Solved

SQL Query help with aggregate sum

Posted on 2015-02-18
13
139 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

912 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

16 Experts available now in Live!

Get 1:1 Help Now