MS SQL Server QUERY SUM of each item between start date and end date

0
votes

I have a table 'pay_schedule' which looks like this:

------------------------------------------------------------------------------
Title (char[50])  | FilmId (int)     payout (Dec(18,2))    pay_date (date)    |
------------------------------------------------------------------------------|
Big Secret        | -2147483648     |   900.00          |   4/9/2017          | 
puzzled           | -2147483631     |   512.50          |   4/9/2017          |
puzzled           | -2147483631     |   325.94          |   4/9/2017          |
puzzled           | -2147483631     |   325.94          |   7/9/2017          |
Star men          | -2147483639     |   512.5           |   7/9/2017          |
Deep beneath      | -2147483636     |   900             |   7/9/2017          |
Deep beneath      | -2147483636     |   900             |   10/9/2017         |
Deep beneath      | -2147483636     |   512.5           |   10/9/2017         |
Deep beneath      | -2147483636     |   325.94          |   10/9/2017         |
puzzled           | -2147483631     |   325.94          |   1/9/2018          |
Star ment         | -2147483639     |   512.5           |   1/9/2018          |
puzzled           | -2147483639     |   900             |   1/9/2018          |
Mirzya            | -2147483639     |   900             |   4/9/2018          |
puzzled           | -2147483639     |   512.5           |   4/9/2018          |
.....             | ..........      |   ......          |   .....             |
                  |                 |                   |                     |

I would like to get a result of :

  • SUM(payout) for each of the title for each month
  • Total payout for month per month between a start date and end date. Like illustrated under:
   Start Date: 01/01/2017   End Date: 12/31/2019
    -------------------------------------------------------------------------
    Month Y         | Big Secret    |Deep beneath   | puzzled    | star men |
                    |               |               |            |          |
    ------------------------------------------------------------------------|
    apr 2017        |   900.00      |      0.00     |   838.44   |    0.00  |
    jul 2017        |     0.00      |    900.00     |   325.94   |  512.50  |
    oct 2017        |     0.00      |   1738.44     |     0.00   |    0.00  |
    jan 2018        |     0.00      |      0.00     |  1225.94   |  512.50  |
    apr 2018        |   900.00      |               |   512.50   |          |
    _________________________________________________________________________

** Months not included where payout is not there

I appreciate your time and help very much. Since this is a large table I would appreciate if you point to possible optimizations too. Have a good day!

2
sqlsql-servertsql
can you have more titles other than the 4 shown in the example? – Vamsi Prabhala
@vkp Yes possibly in the 100s only limit is the start date and end date, or possibly break it down by 20 titles or so. – Sathya Narayanan
Thanks @Joel Coehoom and Happy Town for the formatting edit. I Feel silly but still don't how! Thanks anyway. – Sathya Narayanan
For the answer... take a look at the PIVOT keyword. But even with this keyword, you still must be able to know both the number and names of the columns you expect in the results at the time you write the query, or you'll be stuck using dynamic sql. – Joel Coehoorn

2 Answers

2
votes

If you format the result set as file and month, with the payout, then you can just use aggregation:

select title, year(paydate) as yyyy, month(paydate) as mm,
       sum(payout)
from pay_schedule
group by title, year(paydate), month(paydate)
order by title, yyyy, mm;

You probably cannot even restructure the data as you want -- if you have lots of rows. That probably means lots of columns and more than a thousand or so exceeds SQL Server limits.

If you still want to go down that route, Google "dynamic pivot SQL Server".

0
votes

Try this:

    CREATE TABLE #tt(Title CHAR(50),FilmId INT,payout DECIMAL(18,2),pay_date DATE)
    INSERT INTO #tt
    SELECT 'Big Secret',-2147483648,900.00,'4/9/2017' UNION ALL
    SELECT 'puzzled',-2147483631,512.50,'4/9/2017' UNION ALL
    SELECT 'puzzled',-2147483631,325.94,'4/9/2017' UNION ALL
    SELECT 'puzzled',-2147483631,325.94,'7/9/2017' UNION ALL
    SELECT 'Star men',-2147483639,512.5 ,'7/9/2017' UNION ALL
    SELECT 'Deep beneath',-2147483636,900,'7/9/2017' UNION ALL
    SELECT 'Deep beneath',-2147483636,900,'10/9/2017' UNION ALL
    SELECT 'Deep beneath',-2147483636,512.5 ,'10/9/2017' UNION ALL
    SELECT 'Deep beneath',-2147483636,325.94,'10/9/2017' UNION ALL
    SELECT 'puzzled',-2147483631,325.94,'1/9/2018' UNION ALL
    SELECT 'Star ment',-2147483639,512.5 ,'1/9/2018 ' UNION ALL
    SELECT 'puzzled',-2147483639,900,'1/9/2018' UNION ALL
    SELECT 'Mirzya',-2147483639,900,'4/9/2018' UNION ALL
    SELECT 'puzzled',-2147483639,512.5,'4/9/2018 ' 


    SELECT @col1=ISNULL(@col1+',','')+QUOTENAME(RTRIM(Title)),@col2=ISNULL(@col2,'')+',ISNULL('+QUOTENAME(RTRIM(Title))+',0) AS '+QUOTENAME(RTRIM(Title)) FROM #tt GROUP BY RTRIM(Title)
    PRINT @col2
    SET @sql='SELECT LEFT(mon,3)+'' ''+LTRIM(yr) as [Month Y]'+@col2+' FROM ('+CHAR(13)+
    '   SELECT DATENAME(MONTH,pay_date) AS mon ,YEAR(pay_date) AS yr,RTRIM(Title) AS Title,SUM(payout) AS payout FROM #tt'+CHAR(13)+
    '   GROUP BY DATENAME(MONTH,pay_date) ,YEAR(pay_date),RTRIM(Title)'+CHAR(13)+
    ') AS t '+CHAR(13)+
    'PIVOT(MAX(payout) FOR Title IN ('+@col1+')) p'
    EXEC(@sql)

Month Y          Big Secret                              Deep beneath                            Mirzya                                  puzzled                                 Star men                                Star ment
---------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
Apr 2017         900.00                                  0.00                                    0.00                                    838.44                                  0.00                                    0.00
Jul 2017         0.00                                    900.00                                  0.00                                    325.94                                  512.50                                  0.00
Oct 2017         0.00                                    1738.44                                 0.00                                    0.00                                    0.00                                    0.00
Apr 2018         0.00                                    0.00                                    900.00                                  512.50                                  0.00                                    0.00
Jan 2018         0.00                                    0.00                                    0.00                                    1225.94                                 0.00                                    512.50