Results 1 to 5 of 5
  1. #1
    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

  2. #2
    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

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  4. #4
    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

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    Perfect code!
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •