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
 Sales Evolution throughout the year
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Marzagao
Starting Member

6 Posts

Posted - 07/17/2013 :  10:53:58  Show Profile  Reply with Quote
Hi!

I got the help of MuMu88, visakh16 and kthan before regarding this query for an earlier stage. I'm trying now to get the following:

- YTD Revenue
- %change on revenue of month vs previous month
- %change on revenue of YTD vs previous year YTD
- %change on revenue of month vs same month Y-1
- %change on revenue of quarter vs previous quarter
- %change on revenue of quarter vs same quarter Y-1



CREATE OR REPLACE TABLE pmarzagaotest2
AS
SELECT
 service_country_code,
 [month],
 month_id,
 revenue, 
 product,
 service_channel,
 [year],
 month_num,
 quarter_id,
 SUM(revenue) OVER (PARTITION BY [service_country_code],[product],[service_channel],[year],[quarter_id]) AS QuarterRevenue,
 (CASE
 WHEN service_country_code in ('BG','RO','CY') THEN 'Emerging Markets'
 WHEN service_country_code in ('AE','BH','DZ','EG','IQ','JO','KW','LB','LY','MA','OM','QA','SA','TN','YE') THEN 'MENA Markets'
 WHEN service_country_code in ('CI','CM','GH','KE','MU','NA','NG','RE','SC','SN','TZ','UG') THEN 'SSA Markets'
 WHEN service_country_code in ('GR','ZA','PT','IL') THEN 'Growing Markets'
 WHEN service_country_code in ('HR','CS','SL') THEN 'Adriatics'
 WHEN service_country_code in ('EE','LT','LV') THEN 'Baltics'
 ELSE 'Other'
 END) AS Regions2,
 (CASE 
 WHEN Regions2 in ('Emerging Markets','SSA Markets','MENA Markets','Adriatics','Baltics') THEN 'Emerging Markets'
 WHEN service_country_code in ('GR','ZA','PT','IL') THEN 'Growing Markets'
 ELSE 'Other'
 END) AS Regions1,
 (CASE
 WHEN Regions1 in ('Emerging Markets','Growing Markets') THEN 'Growing & Emerging'
 ELSE 'Other'
 END) AS Region0,
  
 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
 )


which gives the output shown here:
https://www.dropbox.com/s/e05slp35gt1atz6/pmarzagaotest2screenshot.png

this for each 'service_country_code', 'product' and 'service channel'

Can anyone help? Even if with one of the above, I can learn from it and look to apply on the others.

Thanks in advance!

Marzagao
Starting Member

6 Posts

Posted - 07/18/2013 :  05:50:08  Show Profile  Reply with Quote
Anyone can help out with understanding the logic behind any of these?

I was considering using WHILE here but am not sure is the best option for getting these %changes.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  06:16:04  Show Profile  Reply with Quote
I think what you need is to wrap the logic in a CTE/temporary table and then do self join to it to get the comparison details

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

Marzagao
Starting Member

6 Posts

Posted - 07/23/2013 :  04:36:02  Show Profile  Reply with Quote
Thanks visakh16

Can you give me an example of what you had in mind?

Cheers!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/23/2013 :  04:41:12  Show Profile  Reply with Quote
quote:
Originally posted by Marzagao

Thanks visakh16

Can you give me an example of what you had in mind?

Cheers!


Post some sample data for your scenario with the required output and I will post my suggestion based on the details provided

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

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/23/2013 :  04:45:32  Show Profile  Reply with Quote
quote:
CREATE OR REPLACE TABLE pmarzagaotest2
AS
SELECT

You are not using Microsoft SQL Server right ? Or is this a new feature in SQL Server that i do not know about.


KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/23/2013 :  04:46:19  Show Profile  Reply with Quote
quote:
Originally posted by khtan

quote:
CREATE OR REPLACE TABLE pmarzagaotest2
AS
SELECT

You are not using Microsoft SQL Server right ? Or is this a new feature in SQL Server that i do not know about.


KH
Time is always against us




Ah...I missed that
Looks like Oracle then...

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

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/23/2013 :  05:41:54  Show Profile  Reply with Quote
Weird. Oracle can create a "TABLE" from a Query ? It would be call a VIEW in MS SQL world


KH
Time is always against us

Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22761 Posts

Posted - 07/23/2013 :  08:43:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by khtan

Weird. Oracle can create a "TABLE" from a Query ? It would be call a VIEW in MS SQL world


KH
Time is always against us




It is similar to what we have in MS SQL Server

select * into newtable from (query) as t

Madhivanan

Failing to plan is Planning to fail
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.08 seconds. Powered By: Snitz Forums 2000