MSSQL Totals per day for a month

pivot, sql, sql-server, sql-server-2008, t-sql

Solution

Try this

select Date,
  sum(case when [Program label] = 'Salary Day' then count else 0 end) [Salary Day],
  sum(case when [Program label] = 'Monthly' then count else 0 end) [Monthly],
  sum(case when [Program label] = 'Policy' then  count else 0 end) [Policy],
  sum(case when [Program label] = 'Worst Record' then count else 0 end) [Worst Record]
from Totals Group by [Date];

Problem

I am trying to do something very similar to a question I have asked before but I cant seem to get it to work correctly. Here is my previous question: How to get totals per day the table looks as follows: ``` Table Name: Totals Date |Program label |count | | 2013-04-09 |Salary Day |4364 2013-04-09 |Monthly |6231 2013-04-09 |Policy |3523 2013-04-09 |Worst Record |1423 2013-04-10 |Salary Day |9872 2013-04-10 |Monthly |6543 2013-04-10 |Policy |5324 2013-04-10 |Worst Record |5432 2013-04-10 |Salary Day |1245 2013-04-10 |Monthly |6345 2013-04-10 |Policy |5431 2013-04-10 |Worst Record |5232 ``` My question is: Using MSSQL 2008 - Is there a way for me to get the total counts per Program Label per day for the current month. As you can see sometimes it will run twice a day. I need to be able to account for this. The output should look as follows: ``` Date |Salary Day |Monthly |Policy |Worst Record 2013-04-9 |23456 |63241 |23521 |23524 2013-04-10|45321 |72535 |12435 |83612 ```

Original source