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
 )

quote:

---

Originally posted by Marzagao

Thanks visakh16

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

Cheers!

---

quote:

---

CREATE OR REPLACE TABLE pmarzagaotest2
AS
SELECT

---

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
[spoiler]Time is always against us[/spoiler]

---

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
[spoiler]Time is always against us[/spoiler]

---