RS 2000 date parameter for this sql

[davecarrera]

Below is some working part of my current sql.

What i would like to do is have a date parameter in RS 2000 which will be the most current saturdays date say 14/10/06 then where i have hardcoded the dates below do some kind of dateadd - 7 thing to dynamically replace the dates in the case things below.

So in the end i have from the week3 case up dated 08/10/06 to 14/10/06, week2 dated 07/10/06 to 01/10/06 and so on dynamically based on the date enterd by the user.

I know how to make the parameter but i dont know how to do the date calulations.

All help is appreciated

Current Working SQL

SUM(CASE WHEN dated >= '09/10/06' AND dated <= '09/16/06' THEN amount ELSE 0 END) AS Week0,
SUM(CASE WHEN dated >= '09/17/06' AND dated <= '09/23/06' THEN amount ELSE 0 END) AS Week1,
SUM(CASE WHEN dated >= '09/24/06' AND dated <= '09/30/06' THEN amount ELSE 0 END) AS Week2,
SUM(CASE WHEN dated >= '10/01/06' AND dated <= '10/07/06' THEN amount ELSE 0 END) AS Week3

END

Many thanks

Dave

 

[gmmastros]

Take a look at this thread to see if it helps. thread183-1246063

If you have any other questions, please feel free to ask them here.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[bborissov]

DECLARE @WantedDate datetime
SET @WantedDate = '20061014'

SUM(CASE WHEN dated >= '09/10/06' AND dated <= '09/16/06' THEN amount ELSE 0 END) AS Week0,
SUM(CASE WHEN dated BETWEEN @WantedDate-27 AND @WantedDate-21
         THEN amount ELSE 0 END) AS Week2,
SUM(CASE WHEN dated BETWEEN @WantedDate-20 AND @WantedDate-14
         THEN amount ELSE 0 END) AS Week1,
SUM(CASE WHEN dated BETWEEN @WantedDate-13 AND @WantedDate-7
         THEN amount ELSE 0 END) AS Week2,
SUM(CASE WHEN dated BEWEEN >= @WantedDate-6 AND @WantedDate
         THEN amount ELSE 0 END) AS Week3

not tested


Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP