Crossover Dates Problem/Query

GMJ · January 10, 2018, 9:40am

Hi all,
I’ve been battling with this one for a while now and was highly recommended by a colleague to post the query here (after they had a received really good help in the past). So, fingers crossed…

I’m using SQL Server 2012 (T-SQL)

Setup basics and create table scripts

The aim is to find (and total) crossover dates between 2x tables.

The two tables are referenced as WW and CC.

Firstly, here are the 2x CREATE TABLE scripts for both.

Table 1 – WW – Main table which has the empty column to be populated (CC_Days_In_Visit)

CREATE TABLE [dbo].[WW](
[RecordNumber] nvarchar NULL,
[VisitNumber] nvarchar NULL,
[Visit_Start_Date] [date] NULL,
[Visit_End_Date] [date] NULL,
[CC_Days_In_Visit] nvarchar NULL
) ON [PRIMARY]

GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0050854-15', N'1', CAST(N'2015-06-05' AS Date), CAST(N'2015-06-15' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0050854-15', N'2', CAST(N'2015-06-15' AS Date), CAST(N'2015-07-06' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0050854-15', N'3', CAST(N'2015-07-06' AS Date), CAST(N'2015-10-26' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0050854-15', N'4', CAST(N'2015-10-26' AS Date), CAST(N'2015-12-17' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0050854-15', N'5', CAST(N'2015-12-17' AS Date), CAST(N'2015-12-29' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'1', CAST(N'2017-01-06' AS Date), CAST(N'2017-01-06' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'2', CAST(N'2017-01-06' AS Date), CAST(N'2017-01-10' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'3', CAST(N'2017-01-10' AS Date), CAST(N'2017-01-16' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'4', CAST(N'2017-01-16' AS Date), CAST(N'2017-01-17' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'5', CAST(N'2017-01-17' AS Date), CAST(N'2017-01-27' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'6', CAST(N'2017-01-27' AS Date), CAST(N'2017-03-13' AS Date), NULL)
GO
INSERT [dbo].[WW] ([RecordNumber], [VisitNumber], [Visit_Start_Date], [Visit_End_Date], [CC_Days_In_Visit]) VALUES (N'WW0004735-17', N'7', CAST(N'2017-03-13' AS Date), CAST(N'2017-04-30' AS Date), NULL)
GO

Table 2 – CC

CREATE TABLE [dbo].[CC](
[RecordNumber] nvarchar NULL,
[CC_Start_Date] [date] NULL,
[CC_End_Date] [date] NULL,
[DATEDIFF] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[CC] ([RecordNumber], [CC_Start_Date], [CC_End_Date], [DATEDIFF]) VALUES (N'WW0050854-15', CAST(N'2015-07-01' AS Date), CAST(N'2015-07-14' AS Date), 13)
GO
INSERT [dbo].[CC] ([RecordNumber], [CC_Start_Date], [CC_End_Date], [DATEDIFF]) VALUES (N'WW0004735-17', CAST(N'2017-01-10' AS Date), CAST(N'2017-01-16' AS Date), 6)
GO
INSERT [dbo].[CC] ([RecordNumber], [CC_Start_Date], [CC_End_Date], [DATEDIFF]) VALUES (N'WW0004735-17', CAST(N'2017-01-17' AS Date), CAST(N'2017-01-24' AS Date), 7)
GO
INSERT [dbo].[CC] ([RecordNumber], [CC_Start_Date], [CC_End_Date], [DATEDIFF]) VALUES (N'WW0004735-17', CAST(N'2017-03-07' AS Date), CAST(N'2017-03-16' AS Date), 9)
GO
INSERT [dbo].[CC] ([RecordNumber], [CC_Start_Date], [CC_End_Date], [DATEDIFF]) VALUES (N'WW0004735-17', CAST(N'2017-04-16' AS Date), CAST(N'2017-04-18' AS Date), 2)
GO

Desired Results

I’ve highlighted the output table (WW) with how I’d expect to be populated, if successful.

For each different RecordNumber I’d like to add a Count of CC days which crossover the WW days.

So where a CC_Start_Date falls between a Visit_Start_Date and Visit_End_Date (WW), to calculate the total CC days within that WW Visit. If a CC period extends into the next WW Visit, then add this to the next continued Visit.

Ideally I’d implement this as an UPDATE statement within a stored procedure.

Hopefully this makes sense - thanks for reading

gbritton · January 10, 2018, 3:20pm

Would this do it?

SELECT CC.RecordNumber, CC.VisitNumber, CC.CC_Start_Date, CC.CC_End_Date, COUNT(*)
FROM WW
JOIN CC ON WW.RecordNumber = CC.RecordNumber AND WW.Visit_Start_Date = CC.CC_Start_Date and WW.Visit_End_Date = CC.CC_End_Date
GROUP BY CC.RecordNumber, CC.VisitNumber, CC.CC_Start_Date, CC.CC_End_Date

GMJ · January 10, 2018, 3:33pm

Thanks for looking @gbritton - but I don't think that quite gets there unfortunately.

The join wouldn't be as straightforward as that as CC Start Dates can fall between WW Start and Ends.

I think your suggestion would work where all Start and End dates between the 2x tables are the same, but unfortunately that's not the case

gbritton · January 10, 2018, 4:30pm

well then, can you please spell out the exact requirements for a match?

bitsmed · January 10, 2018, 4:39pm

Could this be that you're looking for?

Query

select a.recordnumber
      ,a.visitnumber
      ,a.visit_start_date
      ,a.visit_end_date
      ,sum(datediff(day
                   ,case
                       when a.visit_start_date>b.cc_start_date
                       then a.visit_start_date
                       else b.cc_start_date
                    end
                   ,case
                       when a.visit_end_date<b.cc_end_date
                       then a.visit_end_date
                       else b.cc_end_date
                    end
                   )
          )
       as cc_days_in_visit
  from dbo.ww as a
       left outer join dbo.cc as b
                    on b.cc_start_date<=a.visit_end_date
                   and b.cc_end_date>=a.visit_start_date
 group by a.recordnumber
         ,a.visitnumber
         ,a.visit_start_date
         ,a.visit_end_date
 order by a.recordnumber
         ,a.visitnumber
;

GMJ · January 11, 2018, 12:11pm

This works brilliantly, thank you . I tweaked it slightly for scalability and introduced it into my USP, as below.

Summary

UPDATE dbo.WW
SET [CC_Days_In_Visit] = x.cc_days_in_visit
FROM (

SELECT a.RecordNumber
,a.VisitNumber
,a.Visit_Start_Date
,a.Visit_End_Date
,SUM(DATEDIFF(DAY,CASE WHEN a.Visit_Start_Date > b.CC_Start_Date
THEN a.Visit_Start_Date
ELSE b.CC_Start_Date
END

,CASE WHEN a.Visit_End_Date < b.CC_End_Date
THEN a.Visit_End_Date
ELSE b.CC_End_Date
END
)
)
AS cc_days_in_visit

FROM dbo.WW AS a

LEFT OUTER JOIN dbo.CC AS b
ON b.CC_Start_Date <= a.Visit_End_Date
AND b.CC_End_Date >= a.Visit_Start_Date
AND b.RecordNumber = a.RecordNumber

GROUP BY a.RecordNumber
,a.VisitNumber
,a.Visit_Start_Date
,a.Visit_End_Date

) as x

WHERE WW.RecordNumber = x.RecordNumber
AND WW.VisitNumber = x.VisitNumber