SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Monthly and Quarterly Sales
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marzagao
Starting Member

6 Posts

Posted - 07/16/2013 :  13:17:13  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 07/16/2013 :  13:34:12  Show Profile  Reply with Quote
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

547 Posts

Posted - 07/16/2013 :  18:36:07  Show Profile  Reply with Quote
Is this what you are looking for?



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




Edited by - MuMu88 on 07/16/2013 18:39:27
Go to Top of Page

Marzagao
Starting Member

6 Posts

Posted - 07/17/2013 :  04:21:23  Show Profile  Reply with Quote
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!

Edited by - Marzagao on 07/17/2013 04:22:13
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17645 Posts

Posted - 07/17/2013 :  04:28:18  Show Profile  Reply with Quote
add the following in red to your query

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



KH
Time is always against us


Edited by - khtan on 07/17/2013 04:28:39
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  04:43:25  Show Profile  Reply with Quote
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 - 07/17/2013 :  04:49:07  Show Profile  Reply with Quote
Thanks a lot khtan and visakh16

I learned how to user over and partition now :)

Great help!
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.1 seconds. Powered By: Snitz Forums 2000