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]))

so what should be the output? you want last three months total for each value of hst_merchnum ?

Hi, visakh16 this now been resolved, I'll repost answer when back in work !

ok...thanks for the update

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