Multiple correlated colums questions....

[frosterrj]

Trying to write a query to find "lost customers". Fist I want to pull 2005 YTD Sales, then sales by period in columns to the right, looks something like this:
Cust# CustName YTD05Sales Jan06Sales Feb06Sales etc

My problem is that to get the sales numbers, I have to use a where clause since the sales are not held in their own columns in the table.

Getting the 2005YTD is easy, but how do I add columns to the right????

Here is my starting point (with addl columns not described above):
Select
FFDARCD"A/S/D",FFCCUSN"Cust#",FFCCNMB"CustNm",FFDDTEI"LastOrdDt",
sum(HHHEXSN)"SlsNet",FFDCDTE"CreateDt",FFDDTBC"FirstOrd", FFDDTEI”LastOrdr”
From ((FFCCSTAL0 Left Outer Join HHHORDHL0 on FFCCUSN=HHHCUSN)
Left Outer Join FFDCSTBL0 on FFCCUSN=FFDCUSN)
Where (HHHFSYR=2005)
Group By
FFDARCD,FFCCUSN,FFCCNMB,FFDDTEI,FFDCDTE,FFDDTBC, FFDDTEI
Order By FFDDTEI Desc

Basically trying to create a quasi-spread sheet that I want to add some CASE logic to later to compare the FY2005Sales to the 2006 months on an ongoing basis.

Hope someone has an idea.

Thanks,
Robert

 

[rac2]

It looks like sum(HHHEXSN) will give the total sales for 2005 in the query that you posted. Essentially because of the condition in the WHERE clause, HHHFSYR=2005.

Use CASE expressions within the SUM() for all of the columns. In the WHERE clause possibly change the condition to HHHFSYR >= 2005 so that 2006 rows will be included.

Like so -

SELECT customerID,
       SUM( CASE
              WHEN DATEPART(year, order_date) = 2005
                THEN HHHEXSN
              ELSE 0
            END ) AS "YTD05Sales",
       SUM( CASE
              WHEN DATEPART(year, order_date) = 2006
                   AND DATEPART(month, order_date) = 1
                THEN HHHEXSN
              ELSE 0
            END ) AS "Jan06Sales",

etc for each month

FROM MyTables
GROUP BY customerID

I am using MS SQL Server functions, your database may have different functions to obtain the year and month from a DATETIME value, or you may have years, months and dates stored in separate columns.

Hope my example is clear.

 

[frosterrj]

thanks, that got me pointed in the right direction. I am writing this for AS/400 SQL so it looks almost the same:
Select
FFDARCD"A/S/D",FFCCUSN"Cust#",FFCCNMB"CustNm",FFDDTEI"LastOrdDt",
FFDCDTE"CreateDt",FFDDTBC"FirstOrd", FFDDTEI”LastOrdr”,
Sum(CASE when HHHFSYR=2005 then HHHEXSN else 0 END) as "SlsNet2005",
Sum(CASE when HHHFSYR=2006 and HHHFSPD=1 then HHHEXSN else 0 END) as "Jan06Sls"
From ((FFCCSTAL0 Left Outer Join HHHORDHL0 on FFCCUSN=HHHCUSN)
Left Outer Join FFDCSTBL0 on FFCCUSN=FFDCUSN)
Group By
FFDARCD,FFCCUSN,FFCCNMB,FFDDTEI,FFDCDTE,FFDDTBC, FFDDTEI
Order By FFCCUSN

Ideally, I hope to use this as native sql for the MS Excel Data Transfer Wizard, but it's choking on the "as" part after the case statements. Know anything about that? Is there a DB2/400 forum here?

Thanks Again,
Robert

 

[frosterrj]

actually, I found the reason it was choking-- the double quotes here: FFDDTEI”LastOrdr”
are not actually double quotes. Must have been some junk from using MSWord to type the statements.

All works well now.

Another question: is it possible to use the col aliases (Sum(CASE when HHHFSYR=2006 and HHHFSPD=1 then HHHEXSN else 0 END) as "Jan06Sls") like "Jan06Sls" in other Case statements?

Meaning, can I use Case( when SlsNet2005 > 0 and Jan06Sls <=0 then LostJan06) .....

I'm not sure how I can use the case statements for each month to create a column showing some comparison analysis.

Thanks,
Robert