Solved

SQL Query help with aggregate sum

Posted on 2015-02-18
13
137 Views
Last Modified: 2015-02-23
Hello all,

I have the following query:

 SELECT IV.InvoiceID, IV.PeriodEndDate, ST.StatusGroup
 FROM [Invoice].[dbo].[invInvoice] IV
 INNER JOIN [Invoice].[dbo].[invInvoiceStatus] ST
 ON IV.StatusID = ST.StatusID

Records should for example:

InvoiceID, PeriodEndDate, StatusGroup
1                1/3/2014             Paid
2                1/6/2014             Errors
3                1/10/2014           In Progress
4                2/5/2014             In Progress
5                2/7/2014             Paid

What I need to do is I need to return a result set that shows a month column for the current year so it would have 12 columns.   Row for each StatusGroup with a sum count of the invoices for that statusgroup and month.


Any idea how I do this?  So resultset here would show
                Jan    Feb     Mar      Apr      May     Jun     Jul     Aug     Sep     Oct     Nov     Dec
Paid        1        1         0           0           0           0         0        0       0          0           0       0
Errors     1        0         0          0           0            0          0       0        0         0           0       0
In Pro..   1         1 .......

Thanks All
0
Comment
Question by:sbornstein2
  • 6
  • 4
  • 3
13 Comments
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 250 total points
ID: 40617764
SELECT ST.StatusGroup,
    SUM(CASE WHEN MONTH(IV.PeriodEndDate) =  1 THEN 1 ELSE 0 END) AS Jan,
    SUM(CASE WHEN MONTH(IV.PeriodEndDate) =  2 THEN 1 ELSE 0 END) AS Feb,
    SUM(CASE WHEN MONTH(IV.PeriodEndDate) =  3 THEN 1 ELSE 0 END) AS Mar,
    --...
    SUM(CASE WHEN MONTH(IV.PeriodEndDate) = 11 THEN 1 ELSE 0 END) AS Nov,
    SUM(CASE WHEN MONTH(IV.PeriodEndDate) = 12 THEN 1 ELSE 0 END) AS Dec
FROM [Invoice].[dbo].[invInvoice] IV
INNER JOIN [Invoice].[dbo].[invInvoiceStatus] ST
ON IV.StatusID = ST.StatusID
WHERE
    IV.PeriodEndDate >= '20140101' AND
    IV.PeriodEndDate < '20150101'
GROUP BY ST.StatusGroup

{+edit}
 '2014101' corrected to '20140101'
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40618089
While this will make no different to visible outcome, a slight alternative to using SUM() as seen above would be to use COUNT(), some prefer to use this to document the intent of the query (if indeed you do want a count).

SELECT ST.StatusGroup,
    COUNT(CASE WHEN MONTH(IV.PeriodEndDate) =  1 THEN InvoiceID  END) AS Jan,
    COUNT(CASE WHEN MONTH(IV.PeriodEndDate) =  2 THEN InvoiceID END) AS Feb,
    COUNT(CASE WHEN MONTH(IV.PeriodEndDate) =  3 THEN InvoiceID END) AS Mar,
    --...
    COUNT(CASE WHEN MONTH(IV.PeriodEndDate) = 11 THEN InvoiceID END) AS Nov,
    COUNT(CASE WHEN MONTH(IV.PeriodEndDate) = 12 THEN InvoiceID END) AS Dec
...

note: for COUNT() do not use "ELSE 0" or the result will be incorrect.
0
 

Author Comment

by:sbornstein2
ID: 40619569
Scott this is perfect but I have one kink.   I need a rolling 12 month period based on the current month and year.   So we are now in February so I need March of 2013 through Feb 2014.   Any help on that?   Thanks again
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40619760
Sure; wasn't sure exactly which dates were needed:

WHERE
     IV.PeriodEndDate >= DATEADD(MONTH, -23, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND
     IV.PeriodEndDate < DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
0
 

Author Comment

by:sbornstein2
ID: 40619866
I guess the only thing I am confused about is how to pass back the month and year so I have that all in a result set I can simply pass to a grid the columns.  So my columns I guess if todays date would be:

March 2014 through Feb 2015
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 40619887
So we are now in February so I need March of 2013 through Feb 2014.  
March 2014 through Feb 2015
If you need the more recent dates, change "MONTH, -23" to "MONTH, -11" and "MONTH, -11" to "MONTH, 0".

Not sure what you want on the dates ... you want to add the year to the column name?  That will be much more complex.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40619897
pass back the month and year

to what?
0
 

Author Comment

by:sbornstein2
ID: 40621079
I need to get 12 months of data into 12 columns even Month1, Month2, Month3 would do in the app I would just determine the current month and make the columns name the actual month name.
0
 

Author Comment

by:sbornstein2
ID: 40621097
So if we are in the month of February now I want all data where Month >= 3 and Year >= 2013 and Month <= 2 and Year <=2015 for example that would get me 12 months of data including this month.   But I need the columns to line up for those 12 months.
0
 

Author Comment

by:sbornstein2
ID: 40621100
sorry I meant Month >= 3 and Year >= 2014
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40622771
what version of sql server is this?

can you use DATEFROMPARTS()
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40622777
Try this, it should give you the wanted rolling 12 month period (
select
  DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
, DATEADD(MONTH,   1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))

in which case just update the where clause as Scott proposed (ID: 40619760)

WHERE
     IV.PeriodEndDate >= DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND
     IV.PeriodEndDate  < DATEADD(MONTH,  1  , DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))


NB: You will also need a rolling set of conditions inside each of the columns using SUM( case expression )

i.e. you can't just put January into column 1 because it won't be the first month of the rolling period for 11 months of the year (etc.) but as long as you can stick with contant column headings you avoid a lot of technical complexity.

>>"12 columns even Month1, Month2, Month3 would do"
so, along these lines:
SELECT
      ST.StatusGroup
    , SUM(CASE WHEN MONTH(IV.PeriodEndDate) = MONTH(DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) THEN 1 ELSE 0 END) AS MNTH1
    , SUM(CASE WHEN MONTH(IV.PeriodEndDate) = MONTH(DATEADD(MONTH, -10, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) THEN 1 ELSE 0 END) AS MNTH2
    , SUM(CASE WHEN MONTH(IV.PeriodEndDate) = MONTH(DATEADD(MONTH,  -9, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) THEN 1 ELSE 0 END) AS MNTH3

      --...

    , SUM(CASE WHEN MONTH(IV.PeriodEndDate) = MONTH(DATEADD(MONTH,   0, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) THEN 1 ELSE 0 END) AS MNTH11
    , SUM(CASE WHEN MONTH(IV.PeriodEndDate) = MONTH(DATEADD(MONTH,   1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) THEN 1 ELSE 0 END) AS MNTH12
FROM [Invoice].[dbo].[invInvoice] IV
INNER JOIN [Invoice].[dbo].[invInvoiceStatus] ST ON IV.StatusID = ST.StatusID
WHERE
     IV.PeriodEndDate >= DATEADD(MONTH, -11, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)) AND
     IV.PeriodEndDate  < DATEADD(MONTH,  1  , DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))
GROUP BY
      ST.StatusGroup

Open in new window

0
 

Author Closing Comment

by:sbornstein2
ID: 40626743
thanks guys
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how the fundamental information of how to create a table.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now