June 19, 2014 at 4:44 pm
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.
June 19, 2014 at 8:12 pm
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')
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply