Solved

SQL Query help with aggregate sum

Posted on 2015-02-18
13
149 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 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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
point in time restore in SQL server 26 46
SQL Job Hung 17 37
Update the value of table B with value of table A tables inner join 6 78
Data encryption options between SQL DBs 3 33
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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