[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 651
  • Last Modified:

Produce summary line from multiple detail lines

Need to produce a summary line from multiple detail lines ...

Sample data

SELECT SupplierName, SupplierNo,  InvoiceDate, InvoiceAmount
FROM suppliers

SupplierName  SupplierNo   InvoiceDate   InvoiceAmount
MCDONALD      746793           2012.01       -70
MCDONALD      746793           2012.01       -35
MCDONALD      746793           2012.01       -70

MCDONALD      746793           2012.02       -60
MCDONALD      746793           2012.02       -345
MCDONALD      746793           2012.02       -402

MCDONALD      746793           2012.03       -128
MCDONALD      746793           2012.03       -93
MCDONALD      746793           2012.03       -93

So the total for 2012.01 is -175, the total for 2012.02 is -807 and the total for 2012.03 is -314.

With the live data, there can be any number of values for InvoiceDate (eg 2011.01 ...2011.12,  2012.01 .... 2012.07 etc), and many different SupplierNames ..

Need to produce a summary line from the detail lines above, showing  

SupplierName  SupplierNo    2012.01   2012.02   2012.03   YTD-Total
MCDONALD      746793          -175        -807          -314         -1296

What I've been trying so far is this :

SELECT SupplierName, SupplierNo,
    SUM(CASE WHEN InvoiceDate = '2012.01' THEN InvoiceAmount ELSE 0 END) AS [2012.01],
    SUM(CASE WHEN InvoiceDate = '2012.02' THEN InvoiceAmount ELSE 0 END) AS [2012.02],
    SUM(CASE WHEN InvoiceDate = '2012.03' THEN InvoiceAmount ELSE 0 END) AS [2012.03],
    SUM(InvoiceAmount) AS [YTD-Total]
FROM suppliers
AND InvoiceDate IN ('2012.01', '2012.02', '2012.03')
GROUP BY SupplierNAme, SupplierNo, InvoiceDate      

But that didn't produce the required summary line but instead produced :


SupplierName  SupplierNo    2012.01   2012.02   2012.03   YTD-Total
MCDONALD      746793          -175         0      0             -175
MCDONALD      746793          0              -807      0      -807
MCDONALD      746793          0              0      -314      -314

With the live data, there can be any number of values for InvoiceDate (eg 2011.01 ...2011.12, 2012.01 .... 2012.07 etc), so I'd need to pick up InvoiceDate up directly rather than my current use of AND InvoiceDate IN ('2012.01', '2012.02', '2012.03') ...

Can anyone help me to come up with the best SQL statement to produce the summary line I need, ie from the sample data shown :

SupplierName  SupplierNo    2012.01   2012.02   2012.03   YTD-Total
MCDONALD      746793          -175        -807          -314         -1296

The target database is MS SQL 2005.


Thanks in advance ....
0
raymurphy
Asked:
raymurphy
  • 2
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
0
 
Simone BCommented:
Have you tried something like this:

select SupplierNAme, SupplierNo, InvoiceDate, Sum(InvoiceAmount) as SumInvoiceAmount
FROM suppliers
GROUP BY SupplierNAme, SupplierNo, InvoiceDate
0
 
Simone BCommented:
Sorry, that won't work. I missed your last spec showing the layout.
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
raymurphyAuthor Commented:
I'll have a look at that link, Patrick, so thanks for that - in the meantime I've realised that my GROUP BY was wrong, and shouldn't have been GROUPING on InvoiceDate  - if I change the SQL to read :

SELECT SupplierName, SupplierNo,
    SUM(CASE WHEN InvoiceDate = '2012.01' THEN InvoiceAmount ELSE 0 END) AS [2012.01],
    SUM(CASE WHEN InvoiceDate = '2012.02' THEN InvoiceAmount ELSE 0 END) AS [2012.02],
    SUM(CASE WHEN InvoiceDate = '2012.03' THEN InvoiceAmount ELSE 0 END) AS [2012.03],
    SUM(InvoiceAmount) AS [YTD-Total]
FROM suppliers
WHERE SupplierName = 'MCDONALD'
AND InvoiceDate IN ('2012.01', '2012.02', '2012.03')
GROUP BY SupplierName, SupplierNo

then I do get the single summary line as expected. Given that, is there a way I can modify this SQL statement so that it can pick up ANY InvoiceDate from my table ?
0
 
Patrick MatthewsCommented:
Yes, but you would have to use dynamic SQL to do it.  Which is why I recommend mark_wills's article, as the procedure he outlines there does all the donkey work for you :)
0
 
raymurphyAuthor Commented:
Just began reading that article and can see that it's going to be very relevant to what I need, so thanks a lot for that pointer .....
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now