T-SQL Need assistance with complex join

Question

I am really out of ideas of how to solve this issue and need some assistance - not only solution but idea of how to approach will be welcomed.

I have the following table:

TABLE Data
( 
     RecordID
    ,DateAdd
    ,Status
)

with sample date like this:

11  2012-10-01 OK
11  2012-10-04 NO
11  2012-11-05 NO
22  2012-10-01 OK
33  2012-11-01 NO
33  2012-11-15 OK

And this table with the following example data:

TABLE Periods
(
    PeriodID
   ,PeriodName
   ,DateStart
   ,DateEnd
)

1 Octomer  2012-10-01 2012-10-31
2 November 2012-11-01 2012-11-30

What I need to do, is to populate a new table:

TABLE DataPerPeriods
(
    PeriodID,
    RecordID,
    Status
)

That will store all possible combinations of PeriodID and RecordID and the latest status for period if available. If status is not available for give period, then the status for previous periods. If there are no previous status at all - then NULL for status.

For example with the following data I need something like this:

1 11 NO //We have status "OK" and "NO", but "NO" is latest for the period
1 22 OK 
1 33 NULL//Because there are no records for this and previous periods
2 11 NO //We get the previos status as there are no records in this periods
2 22 OK //There are not records for this period, but record for last periods is available
2 33 NO //We have status "OK" and "NO", but "OK" is latest for the period

EDIT: I have already populate the period ids and the records ids in the last table, I need more help on the status update.

score 1 · Answer 1 · edited May 23 '17 at 11:43

1

Check out my answer on another question to know how to grab the first or last status : Aggregate SQL Function to grab only the first from each group

edited May 23 '17 at 11:43

Community

1
1

answered Nov 02 '12 at 14:23

Dominic Goulet

7,983
7
28
56

coge.soft · Accepted Answer · 2012-11-03T13:55:06.387

There might be a better way to do this. But this is the most straight-forward path I know to get what you're looking for, unconventional as it appears. For larger datasets you may have to change your approach:

SELECT p.PeriodID, td.RecordID, statusData.[Status] FROM Periods p
CROSS JOIN (SELECT DISTINCT RecordID FROM Data) td
OUTER APPLY (SELECT TOP 1 [Status], [DateAdd] 
         FROM Data 
         WHERE [DateAdd] <= p.DateEnd 
         AND [RecordID] = td.RecordID 
         ORDER BY [DateAdd] DESC) statusData
ORDER BY p.PeriodID, td.RecordID

The CROSS JOIN is what gives you every possible combination of RecordIDs and DISTINCT Periods.

The OUTER APPLY selects the latest Status before then end of each Period.

score 1 · Answer 3 · answered Nov 02 '12 at 14:51

OK, here's an idea. Nobody likes cursors, including me, but sometimes for things like this they do come in handy.

The idea is that this cursor loops through each of the Data records, pulling out the ID as an identifier. Inside the loop it finds a single data record and gets the count of a join that meets your criteria.

If the @count = 0, the condition is not met and you should not insert a record for that period.

If the @Count=1, the condition is met so insert a record for the period.

If these conditions need to be updated frequently, you can your query to a job and run it every minute or hour ... what have you.

Hope this helps.

DECLARE @ID int
DECLARE merge_cursor CURSOR FAST_FORWARD FOR 
select recordID
from data 
OPEN merge_cursor
FETCH NEXT FROM merge_cursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN

--get join if record is found in the periods
    declare @Count int
    select @Count= count(*) 
    from data a inner join periods b 
    on a.[dateadd] between b.datestart and b.dateend  
    where a.recordID = @ID 

    if @count>0

        --insert into DataPerPeriods(PeriodID, RecordID, Status)
        select b.periodid, a.recordid, a.status 
        from data a inner join periods b on a.[dateadd] between b.datestart and b.dateend --between beginning of  month and end of  month
        where a.recordid = @ID  
    else
        --insert into DataPerPeriods(PeriodID, RecordID, Status)
        select b.periodid, a.recordid, a.status 
        from data a inner join periods b on a.[dateadd] < b.dateend 
        where a.recordID = @ID --fix this area

FETCH NEXT FROM merge_cursor INTO @ID
END

CLOSE merge_cursor 
DEALLOCATE merge_cursor

T-SQL Need assistance with complex join

3 Answers3