Date record creation based on existing dates of MSL

[prutis66]

Dear Community,

Your help is appreciated for the below question. Thanks in advance!!

AS shown below one MSL has Created Dates in months of june,july,december. Now i have to generate records so that each ME will have created dates > then the existing dates and less than the Max(created date) for this MSL also each ME will have Created Date month in the list of months MSL has got. and Response Score should be of latest month avaiable for that ME.

MSL ID	ME ID	Created Date	Response Score
1	101	july	5
1	102	june	4
1	102	july	5
1	103	december	2
1	104	june	3
1	105	june	6
1	105	december	9

So in this case records generated must be as below:

Explanation: MSL Months= June, July, December. Now ME ID 101: He has record of July, so the next month in MSL list is December so in below table you see december record created.

For ME ID 102: already June, july are existing and nxt mnth in msl list is December, so this record is created in below table.

For ME ID 103: December is the latest mnth for this me and this month is the last month in MSL List. So we need not do anything for this record.

For ME ID 104: THis ME has record in june. Nxt months in the list of MSL are july and december ; so these two records are created as below.

For ME ID 105: This ME has record in june and december and no record in july hence one record of july has to be generated with june month score as shown below.

MSL ID	ME ID	Created Date	Response Score
1	101	december	5
1	102	december	5
1	104	july	3
1	104	december	3
1	105	july	6

And this has to be reated for more than one MSL.

************************ Hi All Updated one more case (ME ID 105) *********************

[EmilyQiao]

It would be better to deal with the data if the [Created Date] is a real date or a number.

Let's set the primary table as [Table1].

First, you could get such a table by script like:

[LastRecord]:

Load [MSL ID],[ME ID],max([Created Date]) as [Created Date] resident [Table1];

inner join Load * resident [Table1];

MSL ID	ME ID	Created Date	Response Score
1	101	7	5
1	102	7	5
1	103	12	2
1	104	6	3

Second, you could get a cross join table for all available ME and Created Date.

[CrossTemp]:

Load distinct [Created Date] resident [Table1];

join load distinct [MSL ID],[ME ID],[Response Score] resident [LastRecord];

For example, for ME ID 101, the table should be :

MSL ID	ME ID	Created Date	Response Score
1	101	7	5
1	101	6	5
1	101	12	5
...

Finally, you should join the [CrossTemp] with [LastRecord] to get the final result.

[RestMonth_temp]:

Load [MSL ID],[ME ID],[Created Date],[Response Score] resident [CrossTemp] ;

Ieft join Load [MSL ID],[ME ID],[Created Date] as [LastCreateDate]  resident [LastRecord];

[RestMonth]:

Load [MSL ID],[ME ID],[Created Date],[Response Score] resident [RestMonth_temp]

where [Created Date]>[LastCreateDate];

After that, drop all these temp tables except for the last [RestMonth].

Just notice that if the table concatenate wrongly, please use NoConcatenate Load.

[prutis66]

Hi  Emilyqiao 

Thanks for your reply. I just updated question with one more test case of ME ID 105. Does your answer satisfies this case as well? Will try your script , i have been trying something in PL/SQL.

Once again thanks in advance. I Love your efforts!!  Cheers!