|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Case Within Where ClauseI am written a query that runs each day and pulls all of the work logged by an office, it works fine Mon - Fri but as the managers dont work Sat-Sun they want my report to show all work from the weekend. Here is my current where clause:- Where Sta = 0 and query_source = 1 and User_Name in (Select User_Name from DepartmentList) and convert(char(8),date_logged,112) = Convert(char(8),current_timeStamp-1,112) What I was thinking of doing was Replacing the "=" and "-1" in the last line with a CASE statement:- select CASE LEFT (DateName(DW,CONVERT(CHAR(8), current_timestamp -1, 112)),3) WHEN 'Mon' Then '>' ELSE '=' END And select CASE LEFT (DateName(DW,CONVERT(CHAR(8), current_timestamp -1, 112)),3) WHEN 'Mon' Then '-3' ELSE '-1' END So on a Monday the last line will look like:- and convert(char(8),date_logged,112) > Convert(char(8),current_timeStamp-3,112) I just can't get it to work, im no expert at SQL I have gotten most of my code from thie NG Thanks for any Help Mark Have you thought about doing a UNION ALL instead?
You could have the first part do Mon-Fri and the second the previous weekend. Tony. Show quote "Sh0t2bts" <sh0t2***@hotmail.com> wrote in message news:1137757614.445706.30520@z14g2000cwz.googlegroups.com... > Hi All, > > I am written a query that runs each day and pulls all of the work > logged by an office, it works fine Mon - Fri but as the managers dont > work Sat-Sun they want my report to show all work from the weekend. > > Here is my current where clause:- > Where Sta = 0 > and query_source = 1 > and User_Name in (Select User_Name from DepartmentList) > and convert(char(8),date_logged,112) = > Convert(char(8),current_timeStamp-1,112) > > What I was thinking of doing was Replacing the "=" and "-1" in the last > line with a CASE statement:- > select CASE LEFT (DateName(DW,CONVERT(CHAR(8), current_timestamp -1, > 112)),3) > WHEN 'Mon' Then '>' > ELSE '=' > END > > And > > select CASE LEFT (DateName(DW,CONVERT(CHAR(8), current_timestamp -1, > 112)),3) > WHEN 'Mon' Then '-3' > ELSE '-1' > END > > So on a Monday the last line will look like:- > and convert(char(8),date_logged,112) > > Convert(char(8),current_timeStamp-3,112) > > I just can't get it to work, im no expert at SQL I have gotten most of > my code from thie NG > > > Thanks for any Help > > Mark > Tuesday to Friday I only need to see the previous days work, and on
Monday I need to see Fri - Sun work, I don't know how a UNION would work for that Thanks Mark Can you post the query that needs to run on Monday to see Fri-Sun?
Tony Show quote "Sh0t2bts" <sh0t2***@hotmail.com> wrote in message news:1137763133.296826.256230@o13g2000cwo.googlegroups.com... > Tuesday to Friday I only need to see the previous days work, and on > Monday I need to see Fri - Sun work, I don't know how a UNION would > work for that > > Thanks > Mark > This runs on a Monday:-
Select distinct * from T_query_header Where Sta = 0 and query_source = 1 and User_Name in (Select User_Name from DepartmentList) and convert(char(8),date_logged,112) > Convert(char(8),current_timeStamp-3,112) This one Tue-Fri:- Select distinct * from T_query_header Where Sta = 0 and query_source = 1 and User_Name in (Select User_Name from DepartmentList) and convert(char(8),date_logged,112) = Convert(char(8),current_timeStamp-1,112) The only difference is in the date line where I change the "=" sign to a ">" sign and "-1" to "-3" Thanks Mark Select distinct * from T_query_header
Where Sta = 0 and query_source = 1 and User_Name in (Select User_Name from DepartmentList) and convert(char(8),date_logged,112) > Convert(char(8),current_timeStamp-3,112) union all Select distinct * from T_query_header Where Sta = 0 and query_source = 1 and User_Name in (Select User_Name from DepartmentList) and convert(char(8),date_logged,112) = Convert(char(8),current_timeStamp-1,112) Tony. Show quote "Sh0t2bts" <sh0t2***@hotmail.com> wrote in message news:1137763819.482862.166770@g43g2000cwa.googlegroups.com... > This runs on a Monday:- > Select distinct * from T_query_header > Where Sta = 0 > and query_source = 1 > and User_Name in (Select User_Name from DepartmentList) > and convert(char(8),date_logged,112) > > Convert(char(8),current_timeStamp-3,112) > > This one Tue-Fri:- > Select distinct * from T_query_header > Where Sta = 0 > and query_source = 1 > and User_Name in (Select User_Name from DepartmentList) > and convert(char(8),date_logged,112) = > Convert(char(8),current_timeStamp-1,112) > > The only difference is in the date line where I change the "=" sign to > a ">" sign and "-1" to "-3" > > Thanks > Mark > Tony,
Thanks for your help, Your solution didn't bring back the result set that I required, I have worked it out now and have posted the where clause below with some comments to help others Cheers Mark Original Query:- Where convert(char(8),date_closed,112) between Convert(char(8),current_timeStamp-1,112) and Convert(char(8),current_timeStamp-1,112) I wanted to extend the date range by 3 days on a Monday so that weekend work would be pickedup New Query:- Where convert(char(8),date_closed,112) between Convert(char(8),current_timeStamp- -- I have removed 1 from -1 and replaced it with the case statment CASE LEFT (DateName(DW,CONVERT(CHAR(8), current_timestamp -1, 112)),3) WHEN 'Mon' Then 3 ELSE 1 END --back to the original query ,112) and Convert(char(8),current_timeStamp-1,112)  |
|||||||||||||||||||||||
Other interesting topics