Help in Case statement

Hi,

I have to use case statement in where condition of a sql query. I get an error

Incorrect syntax near '<'.

Declare @.iPostingID int

set @.iPostingId = 1

Select PostingID from JobsDB_JobPostings where DateDiff(day,PostingDate,getdate())

case @.iPostingId

when 1 then '<1'

when 2 then '<7'

when 3 then '<30'

when 4 then '<60'

when 5 then '<90'

end

Can anyone help me please

Thanks,

Uma Ramiya

Hi,

Maybe you want this:

Code Snippet

SELECT

CASE PostingID

WHEN 1 THEN '<1'

WHEN 2 THEN '<7'

WHEN 3 THEN '<30'

WHEN 4 THEN '<60'

WHEN 5 THEN '<90'

END

FROM JobsDB_JobPostings WHERE DATEDIFF(DAY,PostingDate,GETDATE())

Regards,

Janos

|||The parameter @.iPostingId is used for comparison and not the column field.|||Well, you need the select before the CASE|||Hi,

We use similar construct in our daily life here. Have a look on following query, this will really work.

DECLARE @.iPostingID int

SET @.iPostingId = 1

SELECT
PostingID
FROM
JobsDB_JobPostings
WHERE
DateDiff(day,PostingDate,getdate()) < (CASE
WHEN @.iPostingId = 1 THEN 1
WHEN @.iPostingId = 2 THEN 7
WHEN @.iPostingId = 3 THEN 30
WHEN @.iPostingId = 4 THEN 60
WHEN @.iPostingId = 5 THEN 90
END)|||

Hi I have the following case.i would like a precise and efficient SQL Statement for it.

I have 7 parameters which are used for comparison. I need to run the query with these parameters. which ever is not null should be compared and the null ones should be ignored. AS a result I end with lots of "if' statements as the parameters form a lot of copmbination. Is there any way to cut short the code

ALTER PROCEDURE [dbo].[JobsDb_Resumes_SelectForMatchingSkills]

@.sSkill varchar(50),

@.iCountryID int=NULL,

@.iStateID int=NULL,

@.iJobTypeID int=NULL,

@.iMinSal int=NULL,

@.iMaxSal int NULL,

@.iPeriodPosted int NULL

AS

IF @.iCountryID!=NULL AND @.iStateID=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID = NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal = NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal = Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted = NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

IF @.iCountryID!=NULL AND @.iStateID!=NULL AND @.iJobTypeID != NULL and @.iMinSal != NULL and @.iMaxSal != Null and iPeriodPosted != NULL

SELECT * FROM [dbo].[JobsDb_Resumes]

where targetcountryid=@.iCountryID AND

targetstateid=@.iStateID and

jobtypeid=@.iJobTypeID and

minsalary >= @.iminsal and

maxsalary >= @.imaxsal and

resumetext like ('%' + @.sSkill + '%')

ORDER BY [postdate] DESC

Help in CASE function

Thanks Rudy for the answer of my previous thread.
Here's another scenario of my problem that i hardly get the correct query.

I have a tables with fields and sample records in my database;

Table: tblPo
FIELDS RECORD1
po_number: PO10001
entered_by: chris
approved_by: albert

Table: tblUser
FIELDS RECORD1 RECORD2
user_id: chris albert
first_name: Christopher Alberto
mi: S W
last_name: Lambert Del Puerto

So this is how my database is structured. entered_by and approved_by fields in tblPo are both existed in the tblUser table as user_id so they would share the same table tblUser for First, Middle,Last Names since they both also must be a valid user to create and approve a PO.

Now, I need to get a result like this:

PO # Prepared by Approved by
PO10001 Christopher S. Lambert Alberto W. Del Puerto

My problem now is how to make a query to generate this report. I already joined the two tables with tblPo.user_id to tblUser.user_id and tblPo.approved_by to tblUser.user_id. Unfortunately, both Preparedby and Approvedby resulted to NULL values.

Any help? thanks in advance

BernieHere you go :

use pubs
go
create table tblPO (po_number varchar(100),entered_by varchar(100),approved_by varchar(100))
go
create table tblUser (user_id varchar(100),first_name varchar(100),mi varchar(100),last_name varchar(100))
go
insert into tblPO values('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')
go
select
a.po_number,
isnull(b.first_name,'') +' '+ isnull(b.mi,'') +'. '+ isnull(b.last_name,'') Prepared_by,
isnull(c.first_name,'') +' '+ isnull(c.mi,'') +'. ' + isnull(c.last_name,'') Approved_by
from
tblPO a,
tbluser b,
tbluser c
where
a.entered_by = b.user_id
and a.approved_by = c.user_id

go
drop table tbluser
go
drop table tblpo
go|||create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)|||Originally posted by cvandemaele
create table tblpo (po_number char(10), entered_by char(10), approved_by char(10))
create table tblUser (user_id char(10), first_name char(30), mi char(30), last_name char(30))
insert into tblpo values ('PO10001','chris','albert')
insert into tbluser values ('chris','Christopher','S','Lambert')
insert into tbluser values ('albert','Alberto','W','Del Puerto')

select
tblpo.po_number as po#,
rtrim(EnteredBy.first_name)+' '+rtrim(EnteredBy.mi)+'. '+rtrim(EnteredBy.last_name) as PreparedBy,
rtrim(ApprovedBy.first_name)+' '+rtrim(ApprovedBy.mi)+'. '+rtrim(ApprovedBy.last_name) as ApprovedBy
from tblpo
left outer join tbluser as EnteredBy
on EnteredBy.user_id = tblpo.entered_by
left outer join tbluser as ApprovedBy
on ApprovedBy.user_id = tblpo.approved_by

po# PreparedBy ApprovedBy
---- -------- -------
PO10001 Christopher S. Lambert Alberto W. Del Puerto

(1 row(s) affected)

Thats better .. that would work even if userid does not exist in tblUser ... but remember to take care of null values .. coz null added to anything yields null|||SALAMAT! Thank you guyz...

I can proceed now with my report...

Berniesql

Help for stored procedure and Null...

Hi,
I have write a stored procedure which makes update in a numeric (int) field.

Depending on data in other table, in some case the result of query get a
Null value instead a zero value...

How can I tell to Update query to NOT update field if the value is Null ?

I hope my word clear...

here the stored procedure:

UPDATE dbo.ANAUTENTI

SET dist1punti = dist1punti +

(SELECT SUM(TEMPIMPORTAZIONEDIST1.qnt * ANAARTICOLI.punti) AS totalepunti

FROM TEMPIMPORTAZIONEDIST1 INNER JOIN

ANAARTICOLI ON TEMPIMPORTAZIONEDIST1.codicearticolo =
ANAARTICOLI.codartdist1

WHERE (TEMPIMPORTAZIONEDIST1.piva = ANAUTENTI.piva))

WHERE (piva IN

(SELECT piva

FROM TEMPIMPORTAZIONEDIST1

GROUP BY piva))

Thanks in advance

Piero

Italypiero (g.pagnoni@.pesaroservice.com) writes:
> Depending on data in other table, in some case the result of query get a
> Null value instead a zero value...
> How can I tell to Update query to NOT update field if the value is Null ?

UPDATE tbl
SET col = col + coalesce((SELECT ...), 0)

The coalesce function takes list of arguments and returns the first non-NULL
value in the list, or NULL if all values are NULL.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <sommar@.algonet.se> ha scritto nel messaggio
news:Xns944153FCAB58Yazorman@.127.0.0.1...
> UPDATE tbl
> SET col = col + coalesce((SELECT ...), 0)
>
> The coalesce function takes list of arguments and returns the first
non-NULL
> value in the list, or NULL if all values are NULL.

It works fine !
Thank You very much !

Piero
Italysql

Help finding the Max Total

Hi,

I have the following code

SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2

It prints out:
Wbs2 BillExt
0141 0
0143 0
1217 20580

I want the code to return the wbs2 code '1217' because it has the highest amount in BillExt '20580'.

Can someone help me with this?

Thanks.
lauraThe quick and dirty version...

select top 1 a.WBS2, a.BillExt
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
order by BillExt desc|||Nothing dirty about it...|||That would be nice if there was only 1 record that needed to be returned.
select a.WBS2, BillExt=max(a.BillExt)
from
(SELECT
PR.WBS2,
SUM(CASE WHEN LedgerAR.Period = '200408' AND LedgerAR.TransType <> 'CR'
THEN Ledgerar.amount * - 1
ELSE '0' END) AS BillExt
FROM PR
LEFT JOIN Ledgerar ON PR.WBS1 = Ledgerar.WBS1 AND
PR.WBS2 = Ledgerar.WBS2 AND PR.WBS3 = Ledgerar.WBS3
WHERE PR.WBS2 <> '98' AND pr.wbs2 <> '9001'
AND pr.wbs2 <> 'zzz' AND pr.wbs3 <> 'zzz' AND
pr.wbs1 = '001-298'
GROUP BY PR.WBS2) a
group by a.WBS2
order by BillExt desc|||Now going a little further once I find the maximum total what if I then have to insert a value in that record. Is there a way to do this without using subqueries and instead using case statements?

For instance :

Wbs2 BillExt MaxValue
0141 0 0
0143 0 0
1217 20580 1

Thanks,
Laura|||That would be nice if there was only 1 record that needed to be returned.

Which is wat she asked for...I left my mind reading hat at home...

Laura....INSERT What to Where?

Insert into that record?

I don't understand.|||I'm sorry it's hard to describe what I am asking.

If I have the following information in a table:

WBS1 Amount MAX
0141 0
1217 2
1222 200

I first want to find the maximum of amount which is 200. Next, I want to put a 1 in the MAX column where the largest sum appears in amount.

Ending result:

WBS1 Amount MAX
0141 0
1217 2
1222 200 1

Is this possible?|||Well, once you know the ID of the record with the highest value you can issue and UPDATE statement to set its MAX field value to 1. But you will also need to issue an UPDATE statement to reset all other MAX field values that might have been the largest value before.

I can't recommend marking a record as the "Maximum value" when that status can change at any time. It is better to have a function or view or stored procedure that finds the maximum record dynamically.

Why do you want to mark this record and what are you going to do with it?|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
Laura|||You can't use subqueries? What kind of lunacy is that?

You could still use a stored proc that first loads the MAX value's primary key into a variable and then uses the variable in subsequent queries.

Can't use subqueries? Kindly direct the person who gave you that directive to this informative website:

http://www.hov-hov.dk/you.htm|||Well, I simplified this example quite a bit to generate ideas for myself. But What I am supposed to do is create a report for accounting.

Accounting bills there clients based on services provided. Each service is designated codes. What is supposed to happen in one of the reports is to print the total reimbursable amount into the service that was billed the most.

There is no insertion are update that can be done it is just for display purposes only.

One of the restrictions that I have been under is that I cannot use subqueries only case statements which makes it even more difficult.

So I may have to go an entirely different path.

Thanks for your help,
LauraCan you "cheat" and JOIN a virtual table? Technically that isn't a sub-query.

-PatP

Side note to Blindman, I'm going to have to bookmark that site!

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?
Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and
I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help explain statement start offset and end offset

SELECT TOP 5
total_worker_time/execution_count AS [Avg CPU Time],
(SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
statement_end_offset end -statement_start_offset)/2) FROM
sys.dm_exec_sql_text(sql_handle)) AS query_text
FROM sys.dm_exec_query_stats
ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I want
to understand this statement within the query above(SELECT
SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset = -1
then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
query_text
Where can i learn more about what that statement is actually trying to do ?Hassan
The view sys.dm_exec_sql_text(sql_handle)) is showing you the currently
executing batch, which may be a block of many commands or could be a stored
procedure. The offsets are showing you which exact statement in the code is
actually running at the moment. This way you are not looking at the whole
batch of commands and trying to figure out which statement is actually
running.
So, picture a stored procedure with 1000 lines. Suppose you run this query
several times and you see the same value being returned, which we might say
are equip to lines 400 to 407, then you may have found the slow point in the
code that could use some extra work in tuning.
RLF
"Hassan" <hassan@.test.com> wrote in message
news:uonSdUVJIHA.5208@.TK2MSFTNGP04.phx.gbl...
> SELECT TOP 5
> total_worker_time/execution_count AS [Avg CPU Time],
> (SELECT SUBSTRING(text,statement_start_offset/2,(CASE WHEN
> statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE
> statement_end_offset end -statement_start_offset)/2) FROM
> sys.dm_exec_sql_text(sql_handle)) AS query_text
> FROM sys.dm_exec_query_stats
> ORDER BY [Avg CPU Time] DESCI saw this query to give top CPU hogs and I
> want to understand this statement within the query above(SELECT
> SUBSTRING(text,statement_start_offset/2,(CASE WHEN statement_end_offset
> = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset
> end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS
> query_text
> Where can i learn more about what that statement is actually trying to do
> ?
>

Help expanding a query .. group By

Im using vb code to generate a SQL query to return a recordset. The follwing
query returns 2 records as follows.
Select a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END AS 'TYPE'
from dbo.BacsHdrYearly as a
LEFT JOIN dbo.BacsTrnYear as b
on a.BHYear_LedgerKey = b.BTYear_LedgerKey
Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
2005-04-21 00:00:00 DD
I need it to only return one record as for each date and type ( may be
multiple dates and types ).
BHYEAR_MOVEDATE TYPE
--- --
2005-04-21 00:00:00 DD
any suggestions on how to rephrase this query ?What ABout DISTINCT ?

> Select DISTINCT a.BHYEAR_MOVEDATE,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
> Im using vb code to generate a SQL query to return a recordset. The
> follwing
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?|||or
GROUP BY clause
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
> What ABout DISTINCT ?
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> schrieb im
> Newsbeitrag news:5AEEE5BB-67B2-4DEC-A918-F6C32E79312C@.microsoft.com...
>|||Of couse ;-)
"Uri Dimant" <urid@.iscar.co.il> schrieb im Newsbeitrag
news:e$TxrUNRFHA.3076@.TK2MSFTNGP14.phx.gbl...
> or
> GROUP BY clause
>
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:OoaGSMNRFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||Peter,
Your statement is correct, except that in the GROUP BY clause you have to us
e:
...
GROUP BY
a.BHYEAR_MOVEDATE,
Case b.BTYear_TransCode
WHEN '17' THEN 'DD'
WHEN '01' THEN 'DD'
WHEN '18' THEN 'DD'
Else 'AUD' END;
AMB
"Peter Newman" wrote:

> Im using vb code to generate a SQL query to return a recordset. The follwi
ng
> query returns 2 records as follows.
> Select a.BHYEAR_MOVEDATE,
> Case b.BTYear_TransCode
> WHEN '17' THEN 'DD'
> WHEN '01' THEN 'DD'
> WHEN '18' THEN 'DD'
> Else 'AUD' END AS 'TYPE'
> from dbo.BacsHdrYearly as a
> LEFT JOIN dbo.BacsTrnYear as b
> on a.BHYear_LedgerKey = b.BTYear_LedgerKey
> Where (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '128')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '134')
> OR (a.BHYEAR_LICENCE = '217000' AND a.BHYEAR_SERIALNUMBER = '135')
> OR (a.BHYEAR_LICENCE = '217001' AND a.BHYEAR_SERIALNUMBER = '136')
> GROUP BY a.BHYEAR_MOVEDATE , b.BTYear_TransCode
>
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> 2005-04-21 00:00:00 DD
>
> I need it to only return one record as for each date and type ( may be
> multiple dates and types ).
> BHYEAR_MOVEDATE TYPE
> --- --
> 2005-04-21 00:00:00 DD
> any suggestions on how to rephrase this query ?sql

Help backing up and restoring database

Hi,

I have been using the backup feature of SQL Express for some time and I thought it would be good to test a restore in case the worst happend. I moved my bak files to a usb memory stick and copied them to a machine and installed SQL Express so its empty no database's etc. So I right click the databae folder and choose restore from device and point to the db's but I get errors restoring them. What am I doing wrong? Is their any guides that give steps to backup and restore database's ? Any help would be great - I can of course provide more information need be.

Thanks,

Adam.

Hi,

Here is a good reference article for backup http://www.microsoft.com/technet/prodtechnol/sql/2000/books/c11ppcsq.mspx

BTW you will have to post the error message to get a good answer, unless we cannot provide you the good suggesion.

Hemantgiri S. Goswami

|||

hi Adam,

2326ac wrote:

Hi,

I have been using the backup feature of SQL Express for some time and I thought it would be good to test a restore in case the worst happend. I moved my bak files to a usb memory stick and copied them to a machine and installed SQL Express so its empty no database's etc. So I right click the databae folder and choose restore from device and point to the db's but I get errors restoring them. What am I doing wrong? Is their any guides that give steps to backup and restore database's ? Any help would be great - I can of course provide more information need be.

Thanks,

Adam.

as Hemantgiri already pointed out, it's difficult to help torubleshooting without the actual exception message... but I can guess the Windows account running the SQLExpress intance has not been granted adeguated NTFS permissions on the backup source, the actual folder (path) containing the backup set..

regards

Help !!!!!using case statements in update

Hi,
I have this update statement that works, it updates the totalamount to calc amount, but I want to update totalamount only when it is not equal to calcamt.I have tried many things but in vain.Can some one please help me.
How do i use case statements to update only when totalamount!=calcamt.

update c
set totalamount= calcamt
from Prepay c
JOIN
( select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid
where c.cust_no='somenum'

Try this:

Code Snippet

update c
set totalamount= calcamt
from Prepay c
JOIN
( select sum(amt) as calcamt, payid
from pay
group by payid
)b ON b.payid= c.payid and b.calcamt <> c.dailytotalamount

where c.cust_no='somenum'