Sum case when query issue - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum case when query issue

Author

Topic

dest
Starting Member

8 Posts

Posted - 2008-06-12 : 15:48:50

I currently have a sql query setup that sums all our customer sales by month, its in MSSQL 2000 it looks like this:


SELECT SalesAnalysisHistory.CustomerCode, Customer.CustomerName,

  SUM(CASE WHEN SalesAnalysisHistory.InvoiceDate BETWEEN '01/1/08' AND '01/31/08' THEN SalesAnalysisHistory.ExtendedLineItemAmount ELSE NULL END) AS [JAN],

etc Feb
etc March
etc

FROM ABW.dbo.SalesAnalysisHistory SalesAnalysisHistory, ABW.dbo.Customer Customer
WHERE  SalesAnalysisHistory.CustomerCode = Customer.CustomerCode AND SalesAnalysisHistory.InvoiceDate BETWEEN '01/1/08' AND '12/31/08'
GROUP BY SalesAnalysisHistory.CustomerCode, Customer.CustomerName
ORDER BY SalesAnalysisHistory.CustomerCode

This query works great, except that whoever designed our SQL tables, made a completely separate table for "Miscellaneous Charges", which include shipping, freight and tax. They also didn't include the invoice date on the chart, instead choosing to list each entry by invoice number, month number and year. The table/columns for the Misc chargers are as follows:

ABW.dbo.SalesAnalysisMiscChargesHistory
SalesAnalysisMiscChargesHistory.InvoiceNumber
SalesAnalysisMiscChargesHistory.LineItemAmount (includes all charges, including freight and tax)
SalesAnalysisMiscChargesHistory.SalesPeriod (ie. 1-12)
SalesAnalysisMiscChargesHistory.SalesYear (ie. 2008)

So basically, I need to amend my orginal query to:

SalesAnalysisHistory.ExtendedLineItemAmount between '01/1/08' AND '12/31/08'

Plus

SalesAnalysisMiscChargesHistory.LineItemAmount WHEN SalesAnalysisMiscChargesHistory.SalesPeriod = 1 and WHEN
SalesAnalysisMiscChargesHistory.SalesYear = 2008

end as JAN

I really really appreciate any help I may get on this, thank you!

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-12 : 16:27:40

If you provide the table layouts for SalesAnalysisHistory and Customer it will be much easier to assist you.

dest
Starting Member

8 Posts

Posted - 2008-06-12 : 16:28:09

I'm continuing to research and try to figure this out, I found this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=83119 and I tried to implement the advice with:


SELECT SalesAnalysisHistory.CustomerCode, Customer.CustomerName,
SUM(CASE WHEN SalesAnalysisHistory.InvoiceDate BETWEEN '01/1/08' AND '01/31/08' THEN SalesAnalysisHistory.ExtendedLineItemAmount 
         WHEN SalesAnalysisMiscChargesHistory.SalesPeriod = '1' AND SalesAnalysisMiscChargesHistory.SalesYear = '2008' THEN SalesAnalysisMiscChargesHistory.LineItemAmount


ELSE NULL END) AS [JAN]

FROM ABW.dbo.SalesAnalysisHistory SalesAnalysisHistory, ABW.dbo.Customer Customer, ABW.dbo.SalesAnalysisMiscChargesHistory SalesAnalysisMiscChargesHistory
WHERE  SalesAnalysisHistory.CustomerCode = Customer.CustomerCode AND SalesAnalysisHistory.InvoiceDate BETWEEN '01/1/08' AND '12/31/08'
GROUP BY SalesAnalysisHistory.CustomerCode, Customer.CustomerName
ORDER BY SalesAnalysisHistory.CustomerCode

It actually runs, but it takes forever, and the resulting numbers are sky high. It feels like it's pulling a lot more than it should.

Once again, any help will be greatly appreciated.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-12 : 16:39:25

What is the relationship of SalesAnalysisMiscChargesHistory to SalesAnalysisHistory or Customer?
Is there a corresponding InvoiceNumber in SalesAnalysisHistory perhaps?

dest
Starting Member

8 Posts

Posted - 2008-06-12 : 16:46:51

The only reason Customer is even on the list is because SalesAnalysisHistory doesn't contain the Customer Name field, just the customer code, and they want the customer's name listed on the report next to the code.

SalesAnalysisHistory and SalesAnalysisMiscChargesHistory both share an InvoiceNumber field:

SalesAnalysisHistory.InvoiceNumber
SalesAnalysisMiscChargesHistory.InvoiceNumber

and a CustomerCode field.

Are there any other specific fields that would help?

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-06-12 : 17:04:40

Try:

SELECT  sah.CustomerCode, c.CustomerName,
        SUM(CASE WHEN sah.InvoiceMonth = 1
                 THEN ISNULL(sah.ExtendedLineItemAmount, 0)
                      + ISNULL(camch.LineItemAmount, 0)
                 ELSE 0
            END) AS Jan,
        SUM(CASE WHEN sah.InvoiceMonth = 2
                 THEN ISNULL(sah.ExtendedLineItemAmount, 0)
                      + ISNULL(camch.LineItemAmount, 0)
                 ELSE 0
            END) AS Feb
FROM    Customer c
LEFT OUTER JOIN ( SELECT    CustomerCode, InvoiceNumber,
                            SUM(ExtendedLineItemAmount) AS ExtendedLineItemAmount,
                            MONTH(InvoiceDate) AS InvoiceMonth,
                            YEAR(InvoiceDate) AS InvoiceYear
                  FROM      SalesAnalysisHistory
                  GROUP BY  CustomerCode, InvoiceNumber, MONTH(InvoiceDate),
                            YEAR(InvoiceDate)
                ) sah ON sah.CustomerCode = c.CustomerCode
LEFT OUTER JOIN ( SELECT    InvoiceNumber,
                            SUM(LineItemAmount) AS LineItemAmount, SalesPeriod,
                            SalesYear
                  FROM      SalesAnalysisMiscChargesHistory
                  GROUP BY  InvoiceNumber, SalesPeriod, SalesYear
                ) camch ON sah.InvoiceNumber = camch.InvoiceNumber
                           AND sah.InvoiceMonth = camch.SalesPeriod
                           AND sah.InvoiceYear = camch.SalesYear
WHERE   sah.InvoiceYear = 2008
GROUP BY sah.CustomerCode, c.CustomerName

Edit: I rethought the solution and wanted to make room for the possibility of a many to many relationship
between SalesAnalysisHistory and SalesAnalysisMiscChargesHistory.

dest
Starting Member

8 Posts

Posted - 2008-06-12 : 22:16:22

Thank you very much JDA, I really appreciate it. I replaced the shortened table names with the full table names, but I might have messed something up:


SELECT  SalesAnalysisHistory.CustomerCode, Customer.CustomerName,
        SUM(CASE WHEN SalesAnalysisHistory.InvoiceMonth = 1
                 THEN ISNULL(SalesAnalysisHistory.ExtendedLineItemAmount, 0)
                      + ISNULL(SalesAnalysisMiscChargesHistory.LineItemAmount, 0)
                 ELSE 0
            END) AS Jan,
        SUM(CASE WHEN SalesAnalysisHistory.InvoiceMonth = 2
                 THEN ISNULL(SalesAnalysisHistory.ExtendedLineItemAmount, 0)
                      + ISNULL(SalesAnalysisMiscChargesHistory.LineItemAmount, 0)
                 ELSE 0
            END) AS Feb
FROM    Customer Customer
LEFT OUTER JOIN ( SELECT    CustomerCode, InvoiceNumber,
                            SUM(ExtendedLineItemAmount) AS ExtendedLineItemAmount,
                            MONTH(InvoiceDate) AS InvoiceMonth,
                            YEAR(InvoiceDate) AS InvoiceYear
                  FROM      SalesAnalysisHistory
                  GROUP BY  CustomerCode, InvoiceNumber, MONTH(InvoiceDate),
                            YEAR(InvoiceDate)
                ) SalesAnalysisHistory ON SalesAnalysisHistory.CustomerCode = Customer.CustomerCode
LEFT OUTER JOIN ( SELECT    InvoiceNumber,
                            SUM(LineItemAmount) AS LineItemAmount, SalesPeriod,
                            SalesYear
                  FROM      SalesAnalysisMiscChargesHistory
                  GROUP BY  InvoiceNumber, SalesPeriod, SalesYear
                ) SalesAnalysisMiscChargesHistory ON SalesAnalysisHistory.InvoiceNumber = SalesAnalysisMiscChargesHistory.InvoiceNumber
                           AND SalesAnalysisHistory.InvoiceMonth = SalesAnalysisMiscChargesHistory.SalesPeriod
                           AND SalesAnalysisHistory.InvoiceYear = SalesAnalysisMiscChargesHistory.SalesYear
WHERE   SalesAnalysisHistory.InvoiceYear = 2008
GROUP BY SalesAnalysisHistory.CustomerCode, Customer.CustomerName

That's what I'm running, and I'm getting this error message:

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-12 : 23:56:49

quote:

Originally posted by dest

Thank you very much JDA, I really appreciate it. I replaced the shortened table names with the full table names, but I might have messed something up:


SELECT  SalesAnalysisHistory.CustomerCode, Customer.CustomerName,
        SUM(CASE WHEN SalesAnalysisHistory.InvoiceMonth = 1
                 THEN ISNULL(SalesAnalysisHistory.ExtendedLineItemAmount, 0)
                      + ISNULL(SalesAnalysisMiscChargesHistory.LineItemAmount, 0)
                 ELSE 0
            END) AS Jan,
        SUM(CASE WHEN SalesAnalysisHistory.InvoiceMonth = 2
                 THEN ISNULL(SalesAnalysisHistory.ExtendedLineItemAmount, 0)
                      + ISNULL(SalesAnalysisMiscChargesHistory.LineItemAmount, 0)
                 ELSE 0
            END) AS Feb
FROM    Customer Customer
LEFT OUTER JOIN ( SELECT    CustomerCode, InvoiceNumber,
                            SUM(ExtendedLineItemAmount) AS ExtendedLineItemAmount,
                            MONTH(InvoiceDate) AS InvoiceMonth,
                            YEAR(InvoiceDate) AS InvoiceYear
                  FROM      SalesAnalysisHistory
                  GROUP BY  CustomerCode, InvoiceNumber, MONTH(InvoiceDate),
                            YEAR(InvoiceDate)
                ) SalesAnalysisHistory ON SalesAnalysisHistory.CustomerCode = Customer.CustomerCode
LEFT OUTER JOIN ( SELECT    InvoiceNumber,
                            SUM(LineItemAmount) AS LineItemAmount, SalesPeriod,
                            SalesYear
                  FROM      SalesAnalysisMiscChargesHistory
                  GROUP BY  InvoiceNumber, SalesPeriod, SalesYear
                ) SalesAnalysisMiscChargesHistory ON SalesAnalysisHistory.InvoiceNumber = SalesAnalysisMiscChargesHistory.InvoiceNumber
                           AND SalesAnalysisHistory.InvoiceMonth = SalesAnalysisMiscChargesHistory.SalesPeriod
                           AND SalesAnalysisHistory.InvoiceYear = SalesAnalysisMiscChargesHistory.SalesYear
WHERE   SalesAnalysisHistory.InvoiceYear = 2008
GROUP BY SalesAnalysisHistory.CustomerCode, Customer.CustomerName

That's what I'm running, and I'm getting this error message:

Where are you running this?

dest
Starting Member

8 Posts

Posted - 2008-06-13 : 09:05:24

Just in Microsoft Query connected to the MSSQL server.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-13 : 09:33:13

[code]SELECT c.CustomerCode,
c.CustomerName,
SUM(CASE WHEN theMonth = 1 THEN LineItemAmount ELSE 0 END) AS [Jan],
SUM(CASE WHEN theMonth = 2 THEN LineItemAmount ELSE 0 END) AS [Feb],
SUM(CASE WHEN theMonth = 3 THEN LineItemAmount ELSE 0 END) AS [Mar],
SUM(CASE WHEN theMonth = 4 THEN LineItemAmount ELSE 0 END) AS [Apr],
SUM(CASE WHEN theMonth = 5 THEN LineItemAmount ELSE 0 END) AS [May],
SUM(CASE WHEN theMonth = 6 THEN LineItemAmount ELSE 0 END) AS [Jun],
SUM(CASE WHEN theMonth = 7 THEN LineItemAmount ELSE 0 END) AS [Jul],
SUM(CASE WHEN theMonth = 8 THEN LineItemAmount ELSE 0 END) AS [Aug],
SUM(CASE WHEN theMonth = 9 THEN LineItemAmount ELSE 0 END) AS [Sep],
SUM(CASE WHEN theMonth = 10 THEN LineItemAmount ELSE 0 END) AS [Oct],
SUM(CASE WHEN theMonth = 11 THEN LineItemAmount ELSE 0 END) AS [Nov],
SUM(CASE WHEN theMonth = 12 THEN LineItemAmount ELSE 0 END) AS [Dec]
FROM (
SELECT DISTINCT CustomerCode,
InvoiceNumber
FROM ABW.dbo.SalesAnalysisHistory
) AS sah
INNER JOIN ABW.dbo.Customer AS c ON c.CustomerCode = sah.CustomerCode
INNER JOIN (
SELECT InvoiceNumber,
theMonth,
SUM(LineItemAmount) AS LineItemAmount
FROM (
SELECT sah.InvoiceNumber,
DATEPART(MONTH, sah.InvoiceDate) AS theMonth,
sah.ExtendedLineItemAmount AS LineItemAmount
FROM ABW.dbo.SalesAnalysisHistory AS sah
INNER JOIN ABW.dbo.Customer AS c ON c.CustomerCode = sah.CustomerCode
WHERE sah.InvoiceDate >= '20080101'
AND sah.InvoiceDate < '20090101'

UNION ALL

SELECT InvoiceNumber,
SalesPeriod
LineItemAmount
FROM ABW.dbo.SalesAnalysisMiscChargesHistory
WHERE SalesYear = 2008
) AS q
GROUP BY InvoiceNumber,
theMonth
) AS i ON i.InvoiceNumber = sah.InvoiceNumber
GROUP BY c.CustomerCode,
c.CustomerName
ORDER BY c.CustomerCode,
c.CustomerName[/code]

E 12°55'05.25"
N 56°04'39.16"

dest
Starting Member

8 Posts

Posted - 2008-06-13 : 17:38:18

Thank you for looking at this guys! I finally got it!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources