All Forums Database
Bob18 3 posts Joined 03/15
25 Mar 2015
Transposing Member Count with Date Range

Hi,
I have a transposing question involving date range, member counts, and over 10 million records, and I was hoping I can find some starting points on this forum.
For example, if group “John” is effective from 201310 (Oct-2013 in YYYYMM date format) to 201401 with 1 member, then John’s record should have 1 member counted for columns/date 201310, 201311, 201312, and 201401. Below is an example of input table and how the resulting output table should look like.
 
Table1: Inputs

Group  Eff_YYYYMM   Trm_YYYYMM   Count
John       201310        201401      1
John       201312        201403      1
Kim        201311        201311      2
Kim        201311        201403      2

 
Table2: Total member count by groups for Year-Month Period (after transposing and summing)

Group    201310    201311    201312    201401    201402    201403
John          1         1         2         2         1         1
Kim           0         4         2         2         2         2

Method 1:
I am not familiar with Teradata codes (i.e. loops) and syntax, but if I was a TeraData guru, I would write a statement where if count>0 and 201310 is between 201310 and 201403, then output count 1 for John for the month table 201310. Then do this for count>0 and 201311 is between 201301 and 201412, and then output to month table 201311, and loop until 201403. Then union and sum the 6 month tables to produce a table that looks like Table2 above.
Method2:  
Create the structure for table 2, and then read data elements from table 1, and if they fall in one of the year-month columns, then insert the member count for the year-month.
Method3:
I am not an advance coder, so please let me know if there is a better and more efficient method.
Thanks in advance, and let me know if you have any questions or concerns.
 
Best Regards,
Bob

dnoeth 4628 posts Joined 11/04
25 Mar 2015

Hi Bob,
your method 1 is the common solution for this kind of problem, you just have to translate it to SQL :-)
It's mostly cut&paste&modify (and 100% Standard SQL, no TD syntax needed):

select 
   group
  ,sum(case when 201310 between Eff_YYYYMM and Trm_YYYYMM then count else 0 end)
  ,sum(case when 201311 between Eff_YYYYMM and Trm_YYYYMM then count else 0 end)
  ,sum(case when 201312 between Eff_YYYYMM and Trm_YYYYMM then count else 0 end)
  ,sum(case when 201401 between Eff_YYYYMM and Trm_YYYYMM then count else 0 end)
  ,sum(case when 201402 between Eff_YYYYMM and Trm_YYYYMM then count else 0 end)
  ,sum(case when 201403 between Eff_YYYYMM and Trm_YYYYMM then count else 0 end)
from vt
group by group

 
 

Dieter

Bob18 3 posts Joined 03/15
26 Mar 2015

Thanks Dieter for the solution. Is there a way to use a for loop or if...then statement to go through 201301 to 201312, then output the result? 

As in, I am trying to increment the date_range by 1 month (please correct my syntax if it's wrong) in code below

 

Let date_range = 201310;

 

For date_range = 201310 to date_range = 201403;

 

select

   group 

  ,sum(case when date_range between Eff_YYYYMM and Trm_YYYYMM then (count and date_range =+1) else 0 end)   as date_range=-1

 

from vt

group by group

 

Also, is there a way to transpose the data again so that the table looks like below? 

 

Group

YearMonth

Count

John

201310

1

John

201311

1

John

201312

2

John

201401

2

John

201402

1

John

201403

1

Kim

201310

0

Kim

201311

4

Kim

201312

2

Kim

201401

2

Kim

201402

2

Kim

201403

2

 

 

 

Thanks Again.

Bob

You must sign in to leave a comment.