CODEWITHSUNDEEP
sqlsql-server
joe
joe

Reputation: 67

Using case to create multiple columns of data

I am trying to create a query in MS SQL 2005 that will return data for 4 date ranges as separate columns in my results set.

Right now my query looks like the query below. It works fine, however I want to add the additional columns for each date range since it currently supports one date range when.

This would then return a total1,total2, total3 and total 4 column instead of a single total column like the current query below. Each total would represent the 4 date ranges:

I am fairly sure this can be accomplished using case statements, but am not 100%.

Any help would be certainly appreciated.

 SELECT  
       vendor,location,
      sum(ExtPrice) as total 

  FROM [database].[dbo].[saledata]
where processdate between '2010-11-03' and '2010-12-14'

and location <>''
and vendor <> ''

group by vendor,location with rollup

Upvotes: 4

Views: 8341

Answers (2)

BeemerGuy
BeemerGuy

Reputation: 8269

I usually do it like this: 

 SELECT  
       vendor,location,
      sum(CASE WHEN processdate BETWEEN @date1start AND @date1end THEN xtPrice ELSE 0 END) as total,
      sum(CASE WHEN processdate BETWEEN @date2start AND @date2end THEN xtPrice ELSE 0 END) as total2, 
      sum(CASE WHEN processdate BETWEEN @date3start AND @date3end THEN xtPrice ELSE 0 END) as total3, 
      sum(CASE WHEN processdate BETWEEN @date4start AND @date4end THEN xtPrice ELSE 0 END) as total4     
  FROM [database].[dbo].[saledata]

and location <>''
and vendor <> ''

group by vendor,location with rollup

And you can change the WHEN portion to make your desired date ranges.

Upvotes: 5

SteveCav
SteveCav

Reputation: 6729

Use Subqueries, ie 

select sd.vendor, sd.location, sd1.total, sd2.total, sd3.total, sd4.total
from (select distinct vendor, location from saledata) AS sd
LEFT JOIN (
 SELECT vendor,location, sum(ExtPrice) as total 
  FROM [database].[dbo].[saledata]
where processdate between 'startdate1' and 'enddate1'
and location <>''
and vendor <> ''
group by vendor,location with rollup) sd1 on sd1.vendor=sd.vendor and sd1.location=sd.location

LEFT JOIN (
 SELECT vendor,location, sum(ExtPrice) as total 
  FROM [database].[dbo].[saledata]
where processdate between 'startdate2' and 'enddate2'
and location <>''
and vendor <> ''
group by vendor,location with rollup) sd2 on sd2.vendor=sd.vendor and sd2.location=sd.location

LEFT JOIN (
 SELECT vendor,location, sum(ExtPrice) as total 
  FROM [database].[dbo].[saledata]
where processdate between 'startdate3' and 'enddate3'
and location <>''
and vendor <> ''
group by vendor,location with rollup) sd3 on sd3.vendor=sd.vendor and sd3.location=sd.location

LEFT JOIN (
 SELECT vendor,location, sum(ExtPrice) as total 
  FROM [database].[dbo].[saledata]
where processdate between 'startdate4' and 'enddate4'
and location <>''
and vendor <> ''
group by vendor,location with rollup) sd4 on sd4.vendor=sd.vendor and sd4.location=sd.location

Upvotes: 0

Related Questions