Solved

SQL Query help with aggregate sum

Posted on 2015-02-18
13
146 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:Scott Pletcher
Scott Pletcher 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 69

Expert Comment

by:Scott Pletcher
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:Scott Pletcher
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

790 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