how to get this year and last year totals in tow separate columns

• itortu

  SSCommitted

  Points: 1914

  More actions

  June 19, 2014 at 4:44 pm

  #396939

  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.

• Sachin Nandanwar

  SSCertifiable

  Points: 7699

  More actions

  June 19, 2014 at 8:12 pm

  #1723450

  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