;With CTE
AS(
SELECT
TOP 10000 ROW_NUMBER() OVER (PARTITION BY o.Agent_Chain_No ORDER BY DATEADD(QUARTER,DATEDIFF(QUARTER,0,hst_date_processed),0)) AS Seq,
o.Agent_Chain_No,
DATEADD(QUARTER,DATEDIFF(QUARTER,0,hst_date_processed),0) AS FirstDayOfQuarter,
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_Code not like 'NA'

GROUP BY
o.Agent_Chain_No,
DATEADD(QUARTER,DATEDIFF(QUARTER,0,hst_date_processed),0)
) 


SELECT Agent_Chain_No,
MAX(CASE WHEN Seq=1 THEN Total END) AS TotalQ1,
MAX(CASE WHEN Seq=1 THEN [Diff%] END) AS DiffQ1,
MAX(CASE WHEN Seq=2 THEN Total END) AS TotalQ2,
MAX(CASE WHEN Seq=2 THEN [Diff%] END) AS DiffQ2,
MAX(CASE WHEN Seq=3 THEN Total END) AS TotalQ3,
MAX(CASE WHEN Seq=3 THEN [Diff%] END) AS DiffQ3,
MAX(CASE WHEN Seq=4 THEN Total END) AS TotalQ4,
MAX(CASE WHEN Seq=4 THEN [Diff%] END) AS DiffQ4
FROM
(
SELECT c1.*,COALESCE((c1.Total - COALESCE(c2.Total,0))*100.0/NULLIF(c2.Total,0),0) AS [Diff%]
FROM CTE c1
LEFT JOIN CTE c2
ON c2.Agent_Chain_No = c1.Agent_Chain_No 
AND c2.Seq = c1.Seq-1
)t
GROUP BY Agent_Chain_No

SELECT *,(Q2-Q1)*100.0/NULLIF(Q1,0) AS qtr2%,...
FROM
(
SELECT
...,
[20120101] + [20120201] + [20120301] AS Q1,
[20120401] + [20120501] + [20120601] AS Q2
FROM...

PIVOT(...))
)r

quote:

---

Originally posted by masond

Hi visakh16

Where in my coding would i put that ?

---

SELECT Agent_Chain_No
,[20120101],[20120201],[20120301],[20120401],[20120501]
,[20120601],[20120701],[20120801],[20120901],[20121001]
,[20121101],[20121201],
(Q2-Q1)*100.0/Q1 AS [Q1diff%],
(Q3-Q2)*100.0/Q2 AS [Q2diff%],..
SELECT Agent_Chain_No
,[20120101],[20120201],[20120301],[20120401],[20120501]
,[20120601],[20120701],[20120801],[20120901],[20121001]
,[20121101],[20121201],
[20120101]+[20120201]+[20120301] AS [Q1],
[20120401]+[20120501]+[20120601] AS [Q2],
[20120701]+[20120801]+[20120901] AS [Q3],
[20121001]+[20121101]+[20121201] AS [Q4]
FROM
(
SELECT
TOP 10000 o.Agent_Chain_No,
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_Code not like 'NA'

GROUP BY
o.Agent_Chain_No,
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

SELECT 
,[20120101],[20120201],[20120301],[20120401],[20120501]
,[20120601],[20120701],[20120801],[20120901],[20121001]
,[20121101],[20121201],
[20120101]+[20120201]+[20120301] AS [Q1],
[20120401]+[20120501]+[20120601] AS [Q2],
[20120701]+[20120801]+[20120901] AS [Q3],
[20121001]+[20121101]+[20121201] AS [Q4]
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