Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012
    Posts
    18

    Unanswered: dynamic pivot on months

    Aim

    to get the last three months sales figures, from the date the report has been run, and put the the figures in separate columns associated with months.then provide a flag to give me indiciation if the sales amount has decreased by 10% within those 3months

    The table which i am using is

    SELECT TOP 1000 [hst_merchnum]
    ,[hst_date_processed]
    ,[hst_sales_amt]
    FROM [FDMS].[dbo].[Fact_Financial_History]


    [hst_merchnum] =
    [hst_date_processed] =date of transactions, date is listed as 20120101 (y,m,d)
    hst_sales_amt] = sales amount


    I would need columns created so i can show last 3months hst_sales_amt from point of report

    For eg ran report in march

    layout

    hst_Merchantnum, Jan , Feb, march,
    001 1000 , 2000, 5000

    eg 2) ran report in august
    hst_Merchantnum, June, july, August,
    002 1000 , 2000, 5000

  2. #2
    Join Date
    May 2005
    Posts
    119
    SELECT TOP 1000 [hst_merchnum]
    ,month([hst_date_processed]) as mnth** if this is a datetime field
    ,[hst_sales_amt]
    FROM [FDMS].[dbo].[Fact_Financial_History]

    PIVOT (
    sum([hst_sales_amt]) for mnth in [{enter months to include 1,2,3...12}]) as pvt


    **if you get an error message, use the following command (specific to your sql version):

    For SQL Server 2005:
    EXEC sp_dbcmptlevel 'Fact_Financial_History', 90
    For SQL Server 2008:
    EXEC sp_dbcmptlevel 'Fact_Financial_History', 100
    Last edited by Krista327; 08-15-12 at 12:53.

  3. #3
    Join Date
    Aug 2012
    Posts
    45
    Krista327, I've never used PIVOT (recently coming off SQL Server 2000), but that sure looks a lot easier than what I have done. In any event, if the OP wants to see a rolling 3 month history, the below should do what is required...
    Code:
    IF OBJECT_ID('tempdb..Fact_Financial_History','u') IS NOT NULL
    	DROP TABLE tempdb..Fact_Financial_History
    CREATE TABLE tempdb..Fact_Financial_History
    	(
    	 ID INT IDENTITY(1,1),
    	 hst_merchnum INT NOT NULL,
    	 hst_date_processed DATE NOT NULL,
    	 hst_sales_amt MONEY NOT NULL
    	)
    INSERT INTO tempdb..Fact_Financial_history VALUES 
    (1,'2012-01-01',1000),
    (1,'2012-02-01',980),
    (1,'2012-03-01',800),
    (1,'2012-04-01',1100),
    (1,'2012-05-01',1010),
    (1,'2012-06-01',999),
    (1,'2012-07-01',500),
    (1,'2012-08-01',650),
    (1,'2012-09-01',1200),
    (1,'2012-10-01',1070),
    (1,'2012-11-01',860),
    (1,'2012-12-01',910);
    
    WITH Sales AS
    	(
    	SELECT
    		hst_merchnum,
    		hst_date_processed,
    		MO = MONTH(hst_date_processed),
    		hst_sales_amt,
    		RN = ROW_NUMBER() OVER (PARTITION BY hst_merchnum ORDER BY hst_date_processed)
    	  FROM tempdb..Fact_Financial_History
    	),
    	Months AS
    	(
    	SELECT
    		a.hst_merchnum,
    		a.MO,
    		MONTH1 = MAX(CASE WHEN b.RN = a.RN THEN b.hst_date_processed ELSE NULL END),
    		MONTH1_SALES = SUM(CASE WHEN a.rn = b.RN  THEN b.hst_sales_amt ELSE NULL END),
    		MONTH2 = MAX(CASE WHEN b.RN = a.RN + 1 THEN b.hst_date_processed ELSE NULL END),
    		MONTH2_SALES = SUM(CASE WHEN a.RN + 1 = b.RN  THEN b.hst_sales_amt ELSE NULL END),
    		MONTH3 = MAX(CASE WHEN b.RN = a.RN + 2 THEN b.hst_date_processed ELSE NULL END),
    		MONTH3_SALES = SUM(CASE WHEN a.RN + 2 = b.rn THEN b.hst_sales_amt ELSE NULL END)
    	  FROM Sales b LEFT OUTER JOIN Sales a
    		ON a.hst_merchnum = b.hst_merchnum
    	   AND b.RN <= a.RN + 2
    	   AND b.RN - b.RN <= 3
    	GROUP BY a.hst_merchnum,a.mo
    	)
    SELECT
    	*,
    	[3MOTrend] = MONTH3_SALES - MONTH1_SALES,
    	PCT_CHG = ROUND((MONTH3_SALES - MONTH1_SALES)/(MONTH1_SALES *1.0),2),
    	FLAG = CASE WHEN ROUND((MONTH3_SALES - MONTH1_SALES)/(MONTH1_SALES *1.0),2) <= -.10 THEN 1 ELSE 0 END	
      FROM Months
    I'll have to play around with your PIVOT solution, I can't get it to run without error. I guess it's time to hit BOL.

    Greg

  4. #4
    Join Date
    Aug 2012
    Posts
    45
    Well, PIVOT is pretty nifty...
    Code:
    WITH Sales AS
    	(
    	SELECT
    		hst_merchnum,
    		hst_date_processed,
    		MO = MONTH(hst_date_processed),
    		hst_sales_amt,
    		RN = ROW_NUMBER() OVER (PARTITION BY hst_merchnum ORDER BY hst_date_processed)
    	  FROM tempdb..Fact_Financial_History
    	),
    	Sales2 AS
    	(
    	SELECT
    		a.hst_date_processed, 
    		b.hst_sales_amt,
    		RN2 = ROW_NUMBER() OVER (PARTITION BY a.hst_date_processed ORDER BY b.hst_date_processed)
    	  FROM SALES b LEFT OUTER JOIN Sales a
    		ON a.hst_merchnum = b.hst_merchnum
    	   AND b.RN <= a.RN + 2
    	   AND b.RN - b.RN <= 3
    	 WHERE b.RN >= a.RN
    	 ) 
    SELECT
    	hst_date_processed,
    	[1], [2], [3]
      FROM Sales2
    PIVOT (
    MIN(hst_sales_amt)
    FOR RN2 IN ([1],[2],[3])
    ) AS PVT

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •