How to sum the values based on quater and the month

• nhimabindhu

  Ten Centuries

  Points: 1242

  More actions

  March 20, 2015 at 2:14 am

  #298764

  Hi,

  We had a requirement that need to sum the data based on quater we will be having 12 months data in the system for an year suppose we have 12 records for 2014 year. jan month sales data should be same when we were in feb month it should sum jan+feb sales and should show in sales column whereas we were in march month it should sum jan+feb+mar sales, then same for next quater also apr month it wil be same value in may it should be apr+may in may sales value etc ....

  we will be having date column values as 201401,201402,.....

  how can we implement in sql sever performance should be good.

• roger.price-1150775

  Default port

  Points: 1458

  More actions

  March 20, 2015 at 8:48 am

  #1785373

  Not sure if this is what ou want but something like this will read the table once and add give totals per month.

  SELECT

  SUM(CASE WHEN month(salesdate) = 1 THEN salesvalue ELSE 0 END) AS [Jan],

  SUM(CASE WHEN month(salesdate) <= 2 THEN salesvalue ELSE 0 END) AS [Feb],

  SUM(CASE WHEN month(salesdate) <= 3 THEN salesvalue ELSE 0 END) AS [Mar],

  SUM(CASE WHEN month(salesdate) <= 4 THEN salesvalue ELSE 0 END) AS [Apr],

  SUM(CASE WHEN month(salesdate) <= 5 THEN salesvalue ELSE 0 END) AS [May],

  SUM(CASE WHEN month(salesdate) <= 6 THEN salesvalue ELSE 0 END) AS [Jun],

  SUM(CASE WHEN month(salesdate) <= 7 THEN salesvalue ELSE 0 END) AS [Jul],

  SUM(CASE WHEN month(salesdate) <= 8 THEN salesvalue ELSE 0 END) AS [Aug],

  SUM(CASE WHEN month(salesdate) <= 9 THEN salesvalue ELSE 0 END) AS [Sep],

  SUM(CASE WHEN month(salesdate) <= 10 THEN salesvalue ELSE 0 END) AS [Oct],

  SUM(CASE WHEN month(salesdate) <= 11 THEN salesvalue ELSE 0 END) AS [Nov],

  SUM(CASE WHEN month(salesdate) <= 12 THEN salesvalue ELSE 0 END) AS [Dec]

  FROM SalesData