August 26, 2010 at 2:21 pm
Trying to create a consolidated sales report by store and date. The data is scattered across multiple tables, all linked via the sales header table PS_TKT_HIST. I was able to create a nice pivot of the sales categories from one of the tables, and now I'm trying to add the rest of the required data as additional columns.
I thought that I figured out the hard part in pivoting the major sales line table, but now I am completly stumped on where to position the rest of the subqueries to create the rest of the columns.
**************************
USE POS
GO
DECLARE @STARTDATE T_DT, @ENDDATE T_DT
SELECT @STARTDATE=CONVERT(DATETIME,'07/01/2010')
SELECT @ENDDATE=CONVERT(DATETIME,'08/01/2010')
SELECT Date, Store, Name,
isnull([TEXTILES],0) AS 'TEXTILES',
isnull([BIN GOODS],0)AS 'BIN GOODS',
isnull([FURNITURE],0)AS 'FURNITURE',
isnull([E/M],0)AS 'E/M',
isnull([ACCESSORIES],0)AS 'ACCESSORIES',
isnull([WARES],0)AS 'WARES',
isnull([TOYS],0)AS 'TOYS',
isnull([TARGET],0)AS 'TARGET',
[TOTAL SALES]=isnull([TEXTILES],0)+ISNULL([BIN GOODS],0)+ISNULL([FURNITURE],0)+ISNULL([E/M],0)+ISNULL([ACCESSORIES],0)+ISNULL([WARES],0)+ISNULL([TOYS],0)+ISNULL([TARGET],0),
isnull([CASH DONATIONS],0) AS 'CASH DONATIONS'
FROM
(SELECT CONVERT(VARCHAR,PS_TKT_HIST.TKT_DT,1) AS 'Date', PS_TKT_HIST.STR_ID AS 'Store', PS_STR.DESCR AS 'Name', IM_CATEG_COD.DESCR AS 'Category Name', SUM(PS_TKT_HIST_LIN.EXT_PRC) AS 'Amount'
FROM POS.dbo.IM_CATEG_COD IM_CATEG_COD, POS.dbo.PS_STR PS_STR, POS.dbo.PS_TKT_HIST PS_TKT_HIST, POS.dbo.PS_TKT_HIST_LIN PS_TKT_HIST_LIN,
WHERE PS_TKT_HIST.BUS_DAT = PS_TKT_HIST_LIN.BUS_DAT AND PS_TKT_HIST.DOC_ID = PS_TKT_HIST_LIN.DOC_ID AND PS_STR.STR_ID = PS_TKT_HIST.STR_ID AND PS_STR.STR_ID = PS_TKT_HIST_LIN.STR_ID AND IM_CATEG_COD.CATEG_COD = PS_TKT_HIST_LIN.CATEG_COD AND ((PS_TKT_HIST.TKT_DT>=@STARTDATE and PS_TKT_HIST.TKT_DT<@ENDDATE))
GROUP BY CONVERT(VARCHAR,PS_TKT_HIST.TKT_DT,1),PS_TKT_HIST.STR_ID,PS_STR.DESCR,PS_STR.PHONE_2,IM_CATEG_COD.DESCR) s
PIVOT
(
SUM (AMOUNT)
FOR [Category Name] IN
( [TEXTILES],
[BIN GOODS],
[FURNITURE],
[E/M],
[ACCESSORIES],
[WARES],
[TOYS],
[TARGET],
[CASH DONATIONS] )
) p
ORDER BY STORE,DATE
**************************
This creates a very nice pivot table looking somewhat like this:
DateStoreNameTEXTILESBIN GOODSFURNITUREE/MACCESSORIESWARESTOYSTARGETTOTAL SALESCASH DONATIONS
7/1/20101STORE 11204.4801861537.15115.74001936.370
7/2/20101STORE 11711.830177557.33206.85002500.010
7/3/20101STORE 1149801221829.52139.41002499.930
7/1/20102STORE 2445.49023292.81128.2900871.590
7/2/20102STORE 2877.704650305.33163.67001442.70
7/3/20102STORE 2968.1709632688.22107.85001892.240
Now I want to add the results of the tax table to this query as the next column labeled TAX. The tax table can be related to the store and date. By itself, the tax table query would like like this:
**************************
SELECT CONVERT(VARCHAR, PS_TKT_HIST.TKT_DT, 1) AS 'Date',PS_TKT_HIST.STR_ID AS 'Store' ,COALESCE(SUM(PS_TKT_HIST_TAX.TAX_AMT), 0) AS 'Tax'
FROM PS_TKT_HIST LEFT OUTER JOIN PS_TKT_HIST_TAX
ON PS_TKT_HIST.DOC_ID = PS_TKT_HIST_TAX.DOC_ID and
PS_TKT_HIST.BUS_DAT = PS_TKT_HIST_TAX.BUS_DAT
WHERE (PS_TKT_HIST.TKT_DT >= @STARTDATE and PS_TKT_HIST.TKT_DT <= @ENDDATE)
GROUP BY CONVERT(VARCHAR, PS_TKT_HIST.TKT_DT, 1), PS_TKT_HIST.STR_ID
ORDER BY CONVERT(VARCHAR, PS_TKT_HIST.TKT_DT, 1), PS_TKT_HIST.STR_ID
**************************
The results would be simply this:
DateStoreTax
7/1/2010117.34
7/2/2010171.55
7/3/20101160.27
7/1/20102140.7
7/2/20102172.46
7/3/20102128.94
I want the results of this tax query as another column after the Cash Donations column from the pivot query. Here is where I am stuck. Where on earth am I placing the subquery for the tax (or any additional subqueries). Is it in the pivot select, and a subquery on the pivot columns, or as a seperate join, or what? Where does my WHERE statement filter the tax table to the corresponng store and date? Nothing I have tried so far works. I either get ORDER BY clause invalid in subqueries, or multiple records returned error (should be one tax record per date per store). I am just starting to work with the pivot functions, and I am completly thrown off on my placements.
I might consider tossing the result set to a view and then update the view with the additional queries, but I thought it would be more efficient to do it all in one shot. Not easier, obviously,
Thanks for any advice,
Lewis
August 26, 2010 at 3:38 pm
I know you're new to this forum ... please see the link in my signature for information about how to post questions with test data included so that people can more easily help you.
Also, I would highly suggest reading Jeff Moden's excellent article[/url] about cross tabs and pivots. I pretty much never use PIVOT, especially in situations like yours, and you will understand why after reading it.
August 26, 2010 at 3:57 pm
I would use a few CTE's (CommonTableExpressions) or maybe even separate queries to poulate temp tables for intermediate data to get the subresults (especially for the pivot/CrossTab part) and join those on Date and Store.
And I second bteraberry preferring CrossTab over PIVOT as well as Jeff's excellent article as a good resource for CrossTab. I highly recommend to read it followed by the 2nd part, DynamicCrossTab (see the related link in my signature). If you don't want to rewrite your query whenever a new category shows up in your IM_CATEG_COD table, you'll definitely like the DynamicCrossTab method. 😉
August 27, 2010 at 6:07 am
Thanks for the pointers. Will follow up on article.
Lewis
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply