case statement

Question

case statement

UnionAll

SSCrazy

Points: 2102
More actions
November 4, 2009 at 11:39 am

#216442

Hi All,
I have a table in a SQL 2000 database. The structure is shown below:
ProdName
Sale_Date
I want a query that would return the result set in the following format.
1WeekAgo 2WeekAgo 1MonthAgo
ProdName
I am using case to do this.
But the problem is if there is record with yesterday's date, it is only being counted in the first case condition. And i want this to be counted in the 3rd condition as well.
Any help would be appreciated.
Thanks,

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

details, details, details.

your making it hard on us! you've posted over a hundred times, so I can pretty much assume you've seen requests to provide CREATE TABLE and INSERT INTO scripts for any questions you pose.

you get instant working answers if you provide that information! do your part so we can help you better!

here's a WAG with some assumptions on the data. you should be able to adapt this with your real table names.

to get it to count in multiple sections, change the = signs in the case statements to all < =(less than equal to)signs, so you get kind of like a "running total"

typical results::

PRODNAME THISWEEK ONEWEEKAGO TWOWEEKSAGO LONGERTHANTHAT

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

apples 1 1 1 2

oranges 1 1 1 2

Create Table UnknownName(

ProdName varchar(30),

Sale_Date datetime )

insert into UnknownName

SELECT 'apples',getdate() -1 UNION ALL

SELECT 'apples',getdate() -4 UNION ALL

SELECT 'apples',getdate() -7 UNION ALL

SELECT 'apples',getdate() -18 UNION ALL

SELECT 'oranges',getdate() -1 UNION ALL

SELECT 'oranges',getdate() -4 UNION ALL

SELECT 'oranges',getdate() -7 UNION ALL

SELECT 'oranges',getdate() -18

with MyCTE As (SELECT ProdName, datepart(week,Sale_Date) As Sale_Date from UnknownName group by ProdName, datepart(week,Sale_Date))

select

PRODNAME,

SUM(CASE WHEN Sale_Date = datepart(week,getdate()) THEN 1 ELSE 0 END) AS THISWEEK,

SUM(CASE WHEN Sale_Date = datepart(week,getdate()-1) THEN 1 ELSE 0 END) AS ONEWEEKAGO,

SUM(CASE WHEN Sale_Date = datepart(week,getdate()-2) THEN 1 ELSE 0 END) AS TWOWEEKSAGO,

SUM(CASE WHEN Sale_Date < datepart(week,getdate()-3) THEN 1 ELSE 0 END) AS LONGERTHANTHAT

from MyCTE

group by PRODNAME

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

UnionAll SSCrazy Points: 2102 More actions · Answer 2

please accept my apologies for making it hard.

I didn't try this yet... will let u soon.