Solved

SQL Query help with aggregate sum

Posted on 2015-02-18
13
160 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 49

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
[Live Webinar] The Cloud Skills Gap

As Cloud technologies come of age, business leaders grapple with the impact it has on their team's skills and the gap associated with the use of a cloud platform.

Join experts from 451 Research and Concerto Cloud Services on July 27th where we will examine fact and fiction.

 
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 49

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 49

Expert Comment

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

can you use DATEFROMPARTS()
0
 
LVL 49

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

624 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