If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Rollup multiple records into one?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 30
Rollup multiple records into one?

I have a two tables - a machine, and a volume

Machine has id, and serial number fields
Volume has machine_id, date, and volume

I need to roll up each volume reading into quarters for the year, and return each machine and all its quarters.

So the output should look like this (only one year will be run):
Code:
MachineID     Qtr1SUM(Volume)     Qtr2SUM(Volume)   Qtr3SUM(Volume)   Qtr4SUM(Volume)
Any ideas? I was going to join the volume table four times, with each one making sure it had the appropiate dates, but that isn't working well.

Thanks,

Rob
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 30
Re: Rollup multiple records into one?

Ahh - I think I have it. Besides making this a SP and making the dates parameters, this will work. Is there a more efficient way possibly?

Code:
SELECT Serial = m.serial_vc,
       
       [1Qtr] = q1.vol,
       [2Qtr] = q2.vol,
       [3Qtr] = q3.vol,
       [4Qtr] = q4.vol
  FROM machine_t m
  JOIN (SELECT machine_id, Vol = SUM(totalvolume) FROM vw_meter_reads WHERE period_dt BETWEEN '1/1/03' AND '12/31/03' AND DATEPART(qq, period_dt) = 1 GROUP BY machine_id) q1 ON q1.machine_id = m.id
  JOIN (SELECT machine_id, Vol = SUM(totalvolume) FROM vw_meter_reads WHERE period_dt BETWEEN '1/1/03' AND '12/31/03' AND DATEPART(qq, period_dt) = 2 GROUP BY machine_id) q2 ON q2.machine_id = m.id
  JOIN (SELECT machine_id, Vol = SUM(totalvolume) FROM vw_meter_reads WHERE period_dt BETWEEN '1/1/03' AND '12/31/03' AND DATEPART(qq, period_dt) = 3 GROUP BY machine_id) q3 ON q3.machine_id = m.id
  JOIN (SELECT machine_id, Vol = SUM(totalvolume) FROM vw_meter_reads WHERE period_dt BETWEEN '1/1/03' AND '12/31/03' AND DATEPART(qq, period_dt) = 4 GROUP BY machine_id) q4 ON q4.machine_id = m.id
WHERE m.id = 68
Reply With Quote
  #3 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Yes, there is a much better way. If you lookup CROSSTAB query in Books onlin you will see an excellent example of how to do this using CASE statements.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Sep 2003
Posts: 30
Thank you very much!

Code:
SELECT machine_id,
       Q1 = SUM(CASE DATEPART(qq, period_dt) WHEN 1 THEN totalvolume ELSE 0 END),
       Q2 = SUM(CASE DATEPART(qq, period_dt) WHEN 2 THEN totalvolume ELSE 0 END),
       Q3 = SUM(CASE DATEPART(qq, period_dt) WHEN 3 THEN totalvolume ELSE 0 END),
       Q4 = SUM(CASE DATEPART(qq, period_dt) WHEN 4 THEN totalvolume ELSE 0 END)
  FROM vw_meter_reads
 WHERE machine_id = 68
   AND DATEPART(yyyy, period_dt) = 2003
GROUP BY machine_id
Reply With Quote
  #5 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,560
Perfect code!
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On