Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to get this year and last year totals in tow separate columns Expand / Collapse
Author
Message
Posted Thursday, June 19, 2014 4:44 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, July 24, 2014 10:02 AM
Points: 62, Visits: 97
I have two queries that give me the total sales amount for the current year, and the last year.

SELECT SUM([Sales (LCY)]) 
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2013'


SELECT SUM([Sales (LCY)])
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear = '2014'

I would like to learn how to be able to make this a single query and end up with two columns and their summed up totals. Like it shows on the attached image.


This is my query without the columns I need:

SELECT 
c.CustomerNumber
, c.Name
, c.ChainName
, c.PaymentTermsCode
, cle.CreditLimit AS 'CreditLimit'
, SUM(cle.Amount) AS 'Amount'
, (cle.CreditLimit - SUM(cle.Amount)) AS 'Credit Limit/ Total Sales Ratio'
, cle.DBPaydexScore
, cle.DBFinancialStressScore
, cle_b.PostingDate
, cle_b.DocumentNumber
FROM Customer c
LEFT OUTER JOIN [CustomerLedgerEntry] cle
ON c.CustomerNumber = cle.CustomerNumber
LEFT OUTER JOIN
(
SELECT TOP 1 CustomerNumber, DocumentNumber, MAX(PostDate) AS PostingDate
FROM CustomerLedgerEntry
WHERE DocumentType = 2
AND CustomerNumber = '10135'
GROUP BY CustomerNumber, DocumentNumber
ORDER BY MAX(PostDate) DESC, DocumentNumber DESC
) cle_b
ON cle.CustomerNumber = cle_b.CustomerNumber
WHERE c.CustomerNumber = '10135'
GROUP BY
c.CustomerNumber
, c.Name
, c.ChainName
, c.PaymentTermsCode
, cle.CreditLimit
, cle.DBPaydexScore
, cle.DBFinancialStressScore
, cle_b.PostingDate
, cle_b.DocumentNumber

Thank you very much for your help.


  Post Attachments 
Capture.PNG (7 views, 2.37 KB)
Post #1584084
Posted Thursday, June 19, 2014 8:12 PM This worked for the OP Answer marked as solution


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 3:09 AM
Points: 314, Visits: 2,530
SELECT SUM(CASE WHEN fd.CalendarYear = '2013' THEN [Sales (LCY)]) 2013,
SUM(CASE WHEN fd.CalendarYear = '2014' THEN [Sales (LCY)])2014
FROM [$Cust_ Ledger Entry] cle
LEFT OUTER JOIN dw.dim.FiscalDate fd
ON fd.CalendarDate = cle.[Posting Date]
WHERE [Customer No_] = '10135'
AND fd.CalendarYear IN( '2013','2014')

Post #1584134
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse