Anilrgowda

Question: Working with MS SQL server 2000. Please be very explicit in your responses.
Output I need is a list of tickets generated during any of the options below.
Current week
Last week
Current Month
Last Month
Week Ends on Friday 23:00:59
Week Starts on Saturday 00:00:01
Month starts on xx/firstday/year - xx/lastday/year
**********************
My current datetime column format [open_time]
mm/dd/yyyy 00:00:00

------------------

Anilrgowda

current week:
where datepart(wk,[open_time] = datepart(getdate()) and year(open_time)=year(getdate())

Last week
where datepart(wk,[open_time] = case when datepart(wk,getdate())>1 then datepart(wk,getdate())-1 else 52 end and year([open_time]) = case when datepart(wk,getdate())>1 then year(getdate()) else year(getdate())-1

current month
where month(open_time) = month(getdate()) and year(open_time)=year(getdate())

last month
where month(open_date)=case when month(getdate())=1 then 12 else month(getdate())-1 and
year(open_date)=case when month(getdate())=1 then year(getdate())-1 else year(getdate())

In order to specify that the week starts on Saturday, put SET DATEFIRST 6 before your SELECT.

------------------