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

    Unanswered: Urgent grouping months into quarters

    HI guys

    I need some serious help

    i have the following query, which is producing the results, which i need.

    SELECT
    *
    FROM
    (
    SELECT
    o.FDMSAccountNo,
    DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
    SUM(hst_sales_amt) AS 'Total'
    FROM
    [FDMS].[dbo].[Fact_Financial_History]
    inner join Dim_Outlet o
    on hst_merchnum = FDMSAccountNo_First9
    WHERE
    hst_date_processed >= '20120101' AND hst_date_processed < '20130101'
    and RM_Sales_Band in ( '2M to 4m', '4m +' )
    and HO ='y'
    and LBG_Account <> 'not accepted'
    and ISO_Account ='n'
    AND fdmsaccountno NOT IN (SELECT [ta_mid]
    FROM
    fdms_partnerreporting.tmp.trade_assocations)

    GROUP BY
    o.FDMSAccountNo,
    DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)

    ) s
    PIVOT
    ( max(Total) FOR FirstDayOfMonth IN ([20120101],[20120201],[20120301],[20120401],[20120501]
    ,[20120601],[20120701],[20120801],[20120901],[20121001]
    ,[20121101],[20121201]))p



    However i want to create an additional four columns, which gives me the quarterly sales amount

    for eg
    FDMSAccount no 20120101 20120201 20120301
    878743915885 NULL 10000 35000

    So the quarter sales amount for fdmsaccount no 878743915885 is 45000

    Can anyone provide any assistance ?

  2. #2
    Join Date
    May 2005
    Posts
    119
    I think you may have to do multiple pivot queries and combine them using a join statement. Just do another just like this, but calculate the quarter and use it in your pivot. select * from pivot1 join pivot2 on acct#.

Posting Permissions

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