Results 1 to 5 of 5

Thread: Group by clause

  1. #1
    Join Date
    Aug 2012
    Posts
    18

    Unanswered: Group by clause

    Hi guys

    I need some help with a group by clause

    Please find attached two screen prints

    the first screen print called query1, is giving me every transactional amount per day,

    The second screen print called query 2,
    Is grouping the hst_merchnum by sales amout

    However i need to group the hst_merchnum together, but group the hst_date_processed by the month,

    has any1 got any ideas ?

    oh i also need the hst_date_processed for the last three months,

    i believe the sql query would be

    hst_date_processed < dateadd(mm, -3, max([hst_date_processed]))
    Attached Thumbnails Attached Thumbnails query1.JPG   query2.JPG  

  2. #2
    Join Date
    Aug 2012
    Posts
    30
    so what should be the output? you want last three months total for each value of hst_merchnum ?

  3. #3
    Join Date
    Aug 2012
    Posts
    18
    Hi, visakh16 this now been resolved, I'll repost answer when back in work !

  4. #4
    Join Date
    Aug 2012
    Posts
    30
    ok...thanks for the update

  5. #5
    Join Date
    Aug 2012
    Posts
    18
    SELECT
    *
    FROM
    (
    SELECT
    TOP 10000 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

Posting Permissions

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