;With Temp
AS
(
SELECT
RM_Code,
ParentID,

DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(Net_Sales) 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_Account = 'y'

GROUP BY
RM_Code,
o.ParentID,

DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)
)


SELECT t1.*,t1.Total - COALESCE(t2.Total,0)/NULLIF(t2.Total,0) AS [Difference%],
CASE WHEN t1.Total - COALESCE(t2.Total,0)=0 THEN 'No Change'
     WHEN t1.Total - COALESCE(t2.Total,0) > 0 THEN 'Up' 
     ELSE 'Down'
END AS DiffDirection
FROM Temp t1
OUTER APPLY (SELECT Total
             FROM Temp
             WHERE RM_Code = t1.RM_Code
             AND ParentID = t1.Parent_ID
             AND FirstDayOfMonth = DATEADD(mm,-1,t1.FirstDayOfMonth)
            )t2

quote:

---

Originally posted by masond

Bandi

i spotted that mistake and changed it and it still didnt work, after re-booting my sql it worked first time ! grrr

---

quote:

---

Originally posted by masond

Hi Bandi / visakh16

I hope you don’t me asking, i just want to get my head around the logic you used in your query

the query produces 6 columns

Rm_Code
ParentId
firstdayofmonth
total
difference%
DiffDirection

I understand the first 4 columns
and i understand diffdirection column

its just the difference % i cant seem to grasp
what is the difference% calculating on ?
I only ask, as i would have thought that the difference % should be worked out on latest month total – subtracted against previous month total, and that be the difference

---

;With Temp
AS
(
SELECT
RM_Code,
ParentID,

DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS FirstDayOfMonth,
SUM(Net_Sales) 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_Account = 'y'

GROUP BY
RM_Code,
o.ParentID,

DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)
)


SELECT t1.*,(t1.Total - COALESCE(t2.Total,0))/NULLIF(t2.Total,0) AS [Difference%],
CASE WHEN t1.Total - COALESCE(t2.Total,0)=0 THEN 'No Change'
     WHEN t1.Total - COALESCE(t2.Total,0) > 0 THEN 'Up' 
     ELSE 'Down'
END AS DiffDirection
FROM Temp t1
OUTER APPLY (SELECT Total
             FROM Temp
             WHERE RM_Code = t1.RM_Code
             AND ParentID = t1.Parent_ID
             AND FirstDayOfMonth = DATEADD(mm,-1,t1.FirstDayOfMonth)
            )t2

quote:

---

Originally posted by masond

visakh16
You are a star :)
the amount of times you and others have bailed me out :)

---