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.
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
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.
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.
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.
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):
,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)
group by group