SQL - 2000 Query Analyzer - Weekly Details

[shivalisv]

How Could I get all the transactions which took place from the begining of the week to current date for example if I run query today 14th jan then I should get all the transactions from 12th Jan - Monday to 13th jan excluding current date and the same query should execute even when the user runs it on monday then all the transactions of previous whole week should be displayed ?

 

[rac2]

Oh that is simple.

DECLARE @START_WEEK INT
SET @START_WEEK = 2

SELECT *
FROM tickets
WHERE
   opendate >
CAST(    CONVERT(CHAR(10),
              ( DATEADD(day,
-(

CASE
   WHEN DATEPART( weekday, getdate() ) > @START_WEEK THEN DATEPART( weekday, getdate() ) - @START_WEEK
   ELSE (7 - @START_WEEK) + DATEPART( weekday, getdate() )
END

),
getdate()) ), 101) AS DATETIME    )

  AND opendate <
CAST( CONVERT(CHAR(10), getdate(), 101) AS DATETIME )
ORDER BY opendate

The CASE expression is the heart of my answer. It is used to calculate the cut-off date. We want rows up to 7 days ago, but none for today. The DATEADD() function subtracts the right number of days to get us back to the date of the start of the week. Getting that number is the fun part.

DATEPART(weekday, getdate()) tells the day of the week when we run the report, an integer between 1 and 7. Monday is 2.

We want rows during the past few days. If today is day 4 and the week begins on day 2 then we want rows for the past 2 days, so
DATEPART( weekday, getdate() ) - @START_WEEK

If today is day 1 then we want rows for the past 6 days, so
(7 - @START_WEEK) + DATEPART( weekday, getdate() )

The getdate() function returns the exact time now, but we really need just the date part. In other words we want the date at midnight today.
CAST( CONVERT(CHAR(10), getdate(), 101) AS DATETIME )