DB2

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesDB2

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old August 7th, 2013, 11:36 AM
zaino22 zaino22 is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Aug 2013
Posts: 1 zaino22 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 12 m 8 sec
Reputation Power: 0
Thumbs down Aggregate SQL

Looking for help in creating SQL for following. My SQL skills are basic so looking for some help. Although after few runs staging table will have few months data but We are just comparing current month and previous month.

A) Table below is a result of two table join (pipe delimited for ease of reading): Lets look at the example for the month of June, 2013
------------------
CUST_ID | LEVEL | LEVEL_START_DT | LEVEL_END_DT
123|Executive|02/01/2014|01/01/2015
123|Executive|17/06/2013|01/01/2014
456|Classic|02/01/2014|01/01/2015
456|Classic|10/06/2013|01/01/2014
456|Basic|17/05/2013|09/06/2013
789|High Value|02/01/2014|01/01/2015
789|High Value|17/05/2013|01/01/2014
1123|Zero|02/01/2014|01/01/2015
1123|Zero|15/06/2013|01/01/2014

Now based on the above join, I want to create a following staging table:

CUST_ID|CURR_LVL|FUTR_LVL|YR_MONTH
123|Executive| Executive |2013-06
456|Classic|Classic|2013-06
789|High Value|High Value|2013-06
1123|Zero|Zero|2013-06
1456|High Value| High Value|2013-06
1789| High Value| High Value |2013-06



B) Same table described in A but now after July run.

CUST_ID | LEVEL | LEVEL_START_DT | LEVEL_END_DT
123|Executive|02/01/2014|01/01/2015
123|Executive|17/06/2013|01/01/2014
456|Gold|02/01/2014|01/01/2015
456|Gold|15/07/2013|01/01/2014
456|Classic|10/06/2013|14/07/2014
456|Basic|17/05/2013|09/06/2013
789|Executive|02/01/2014|01/01/2015
789|Executive|26/07/2013|01/01/2014
789|High Value|17/05/2013|25/07/2014
1123|Basic|02/01/2014|01/01/2015
1123|Basic|25/07/2013|01/01/2014
1123|zero|15/06/2013|24/07/2014


Staging table will now have two months.

CUST_ID|CURR_LVL|FUTR_LVL|YR_MONTH
123|Executive| Executive |2013-06
456|Classic|Classic|2013-06
789|High Value|High Value|2013-06
1123|Zero|Zero|2013-06

123|Executive|Executive|2013-07
456|Gold|Gold|2013-07
789|Executive| Executive |2013-07
1123|Basic|Basic|2013-07

C) Comparing two months June-July final table will aggregate results and will look like below. Btw, If the level was not changed in comparing month then we are still keeping track see first record.

Number of Executive that remained Executive: 1 | 2013-07
Number of High Value that are now Executive:1| 2013-07
Number of Classic that are now Executive: 0| 2013-07
Number of Basic that are now Executive:0| 2013-07
Number of Zero that are now Executive:0| 2013-07

Number of Classic that are now Gold: 1| 2013-07
Number of Zero that are now Basic:1| 2013-07
*
Thank you,

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesDB2 > Aggregate SQL


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.

© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap