Converting Months to quartiles

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting Months to quartiles

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 09:27:39

hey guys

Here is code which i am currently using, which is providing me the data i want.

however i would like 4 additional columns added, each column represents a quarter, i want to find out whether quarter two sales, have decreased or increased by 10% compared too quarter one,

SELECT
*
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

I would appreciate any help available

many thanks
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 09:50:59

sorry but current query is not representing quarter based data but its based on month.

for your requirement you need to change it as


;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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 10:04:11

hi visakh16

is there anyway that it can display months, and create separate columns to look at quarters ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:09:27

hmmm. you mean months,quarter and then difference?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 10:19:14

hi

Well currently the data is displayed as the following
it provides me with the hst_sales_amt per month based on the agent_chain_no

Agent_Chain_No 20120101 20120201 20120301 20120401
878970101886 250870.48 245303.16 450008.36 399853.55

for eg Could we have the data displayed as
then it automatically works out the difference between the quarters and the difference % hst_sales_amt
For e.g.,

if jan , feb and march = £1000
and april, may, june = £2000
quarter 2 has 100% growth based on quarter 1 (hst_sales_amt )

Agent_Chain_No 20120101 20120201 20120301 20120401 qtr1 qtr2
878970101886 250870.48 245303.16 450008.36 399853.55 0% 10% growth

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:23:21

you can.
just use your pivot query itself and for quarter percentages use


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-13 : 10:24:34

Hi visakh16

Where in my coding would i put that ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-13 : 10:26:31

quote:
Originally posted by masond

Hi visakh16

Where in my coding would i put that ?

just put wrapper over your current pivot as shown

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-14 : 03:57:11

Sorry to sound rude, but wrapper over current pivot ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:38:37

yep.like below


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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-14 : 10:52:54

hi visakh16
sorry to bother you again
i would like to Sum up hst_sales_amt per quarter , then working out % difference between per quarter

For eg, if in excel,my sum values were on row2

Column A B C D
q1 q2 q3 q4
Sum 5500 13000 4000 6000

Formula would be =SUM(C2-B2)/B2

My current query is ;

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

how would you apply it to that query ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 10:56:09

my suggestion still does the same thing. the reason i've used months in grouping is because you told you want month values to be displayed along with it
if you want quarter values along with difference %s use my earlier posted suggestion on 08/13/2012 : 09:50:59

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-08-14 : 11:01:44

HI

I PUT THE QUERY AS

SELECT FROM o.FDMSAccountNo
,[20120101],[20120201],[20120301],[20120401],[20120501]
,[20120601],[20120701],[20120801],[20120901],[20121001]
,[20121101],[20121201],
(Q2-Q1)*100.0/Q2 AS [Q2diff%],
(Q3-Q2)*100.0/Q3 AS [Q3diff%],
(Q4-Q3)*100.0/Q4 AS [Q4diff%],

SELECT FROM o.FDMSAccountNo
,[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

BUT I AM GETTING THE ERROR MSG

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'FROM'.
Msg 102, Level 15, State 1, Line 44
Incorrect syntax near 's'.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-14 : 11:41:24

[code]
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

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources