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
 Monthly and Quarterly Sales

Author  Topic 

Marzagao
Starting Member

6 Posts

Posted - 2013-07-16 : 13:17:13
Hi

I'm quite new to SQL so learning as I go.

I have a sales table populated with the following columns:
service_country_code,
month,
month_id,
revenue,
product,
service_channel,
year,
month_num,

I would like to be able to get a table with:
service_country_code,
month,
month_id,
revenue,
product,
service_channel,
year,
month_num,
quarter_id,
quarter_revenue

where month_num is the month from 1-12
where the quarter_id is the quarter from 1-4
where the quarter _revenue is the revenue for that quarter.

I was able to get the quarter revenue working on the following select code:


SELECT
service_country_code,
month,
month_id,
revenue, //monthly revenue
product,
service_channel,
year,
month_num,

CASE
WHEN month_num between 1 and 3 THEN 1
WHEN month_num between 4 and 6 THEN 2
WHEN month_num between 7 and 9 THEN 3
WHEN month_num between 10 and 12 THEN 4
END quarter_id

FROM
(
SELECT
service_country_code,
month,
month_id,
product,
revenue,
service_channel,
year,

CASE
WHEN month='January' THEN 1
WHEN month='February' THEN 2
WHEN month='March' THEN 3
WHEN month='April' THEN 4
WHEN month='May' THEN 5
WHEN month='June' THEN 6
WHEN month='July' THEN 7
WHEN month='August' THEN 8
WHEN month='September' THEN 9
WHEN month='October' THEN 10
WHEN month='November' THEN 11
WHEN month='December' THEN 12
END month_num

FROM pmarzagao_test1
)

Which I understand may not be the sharpest one for sure (am just starting). However I cant seem to create a table with both the monthly and the quarter revenue for each record..

Can you help?

Thanks!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-16 : 13:34:12
add the below to select list and check



SUM(revenue) OVER (PARTITION BY [year],[month]) AS MonthlyRevenue,
SUM(revenue) OVER (PARTITION BY [year],[quarter]) AS QuarterRevenue




------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-07-16 : 18:36:07
Is this what you are looking for?

[CODE]

SELECT
service_country_code,
[month],
month_id,
revenue,
product,
service_channel,
[year],
month_num,
quarter_id,
SUM(revenue) OVER (PARTITION BY [year],[quarter_id]) AS QuarterRevenue
FROM
(
SELECT
service_country_code,
[month],
month_id,
product,
revenue,
service_channel,
[year],
(CASE
WHEN month='January' THEN 1
WHEN month='February' THEN 2
WHEN month='March' THEN 3
WHEN month='April' THEN 4
WHEN month='May' THEN 5
WHEN month='June' THEN 6
WHEN month='July' THEN 7
WHEN month='August' THEN 8
WHEN month='September' THEN 9
WHEN month='October' THEN 10
WHEN month='November' THEN 11
WHEN month='December' THEN 12
END) as month_num,
(CASE
WHEN [month] in ('January', 'February', 'March') THEN 1
WHEN [month] in ('April', 'May', 'June') THEN 2
WHEN [month] in ('July', 'August', 'September') THEN 3
WHEN [month] in ('October', 'November', 'December') THEN 4
END) as quarter_id
FROM pmarzagao_test1
) A



[/CODE]
Go to Top of Page

Marzagao
Starting Member

6 Posts

Posted - 2013-07-17 : 04:21:23
Thanks MuMu88 and visakh16

When I follow MuMu88 suggestion I get the following:

https://www.dropbox.com/s/m69a4v375j7j8e9/Screen%20Shot%202013-07-17%20at%2009.16.14.png

This is shows the quarterly revenue column but all values are the same for every row. Maybe I was not detailed enough but I wanted, for example, the 2nd-3rd-4th row's monthly revenue to be summed up quarterly. So this would be respecting 'service_country_code', 'product' and 'service channel'.

Thanks once more!
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-17 : 04:28:18
add the following in red to your query

over (partition by service_country_code, [year] ...



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 04:43:25
quote:
Originally posted by Marzagao

Thanks MuMu88 and visakh16

When I follow MuMu88 suggestion I get the following:

https://www.dropbox.com/s/m69a4v375j7j8e9/Screen%20Shot%202013-07-17%20at%2009.16.14.png

This is shows the quarterly revenue column but all values are the same for every row. Maybe I was not detailed enough but I wanted, for example, the 2nd-3rd-4th row's monthly revenue to be summed up quarterly. So this would be respecting 'service_country_code', 'product' and 'service channel'.

Thanks once more!


you need to add the required columns in partition by based on which you want to do the grouping

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Marzagao
Starting Member

6 Posts

Posted - 2013-07-17 : 04:49:07
Thanks a lot khtan and visakh16

I learned how to user over and partition now :)

Great help!
Go to Top of Page
   

- Advertisement -