Transposing Member Count with Date Range

Database
Fan

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

2 REPLIES
Senior Apprentice

Re: Transposing Member Count with Date Range

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
Fan

Re: Transposing Member Count with Date Range

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