SQL BETWEEN dates Query

Hi all,

I'm guessing this is quite a common query, but trying to summarise it in a Google-able search query is tricky, so thought I'd post the whole issue here.

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

My basic aim

I want to perform a simple inner join against a lookup table. I'm using CCDate to drop BETWEEN HEStartDate and HEEndDate to pull in Visit column.
But my problem is (and quite rightly) if there's an extra visit on the same day, I get an additional record produced.

Scripts to setup 2x tables

CREATE TABLE [dbo].[zzz00MainTable](
	[CCDate] [date] NULL,
	[PersonNumber] [nvarchar](12) NOT NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[zzz01LookupTable](
	[HEStartDate] [date] NULL,
	[HEEndDate] [date] NULL,
	[Visit] [nvarchar](2) NOT NULL,
	[PersonNumber] [nvarchar](12) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180404', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180404', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180405', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180406', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180407', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180408', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180405', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180406', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180409', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180410', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180411', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180412', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180413', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180414', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180415', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180416', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180417', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180418', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180420', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180421', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180422', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180423', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180424', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180425', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180426', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180430', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180407', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180408', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180409', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180410', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180411', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180412', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180413', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180414', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180416', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180417', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180418', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180420', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180421', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180422', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180423', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180424', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180426', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180405', N'xx0030843/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180405', N'xx0030843/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180406', N'xx0030843/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180419', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180427', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180415', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180419', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180427', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180428', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180429', N'xx0030804/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180425', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180428', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180429', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180430', N'xx0030788/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180403', N'xx0030146/10')
GO
INSERT [dbo].[zzz00MainTable] ([CCDate], [PersonNumber]) VALUES (N'20180404', N'xx0030146/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2017-04-01' AS Date), CAST(N'2017-04-05' AS Date), N'1', N'xx0030317/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2017-03-31' AS Date), CAST(N'2017-04-03' AS Date), N'1', N'xx0030296/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2017-04-02' AS Date), CAST(N'2017-04-22' AS Date), N'1', N'xx0030454/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2018-04-05' AS Date), CAST(N'2018-04-05' AS Date), N'1', N'xx0030843/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2018-04-05' AS Date), CAST(N'2018-04-09' AS Date), N'2', N'xx0030843/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2018-04-04' AS Date), CAST(N'2018-04-30' AS Date), N'1', N'xx0030788/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2018-04-03' AS Date), CAST(N'2018-04-06' AS Date), N'1', N'xx0030146/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2017-04-01' AS Date), CAST(N'2017-04-04' AS Date), N'1', N'xx0030406/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2018-04-04' AS Date), CAST(N'2018-04-30' AS Date), N'1', N'xx0030804/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2017-04-04' AS Date), CAST(N'2017-04-12' AS Date), N'1', N'xx0030946/10')
GO
INSERT [dbo].[zzz01LookupTable] ([HEStartDate], [HEEndDate], [Visit], [PersonNumber]) VALUES (CAST(N'2018-04-30' AS Date), CAST(N'2018-04-30' AS Date), N'2', N'xx0030804/10')
GO

These scripts should produce 2x tables. One main and one lookup. It will then insert 59 and 11 rows.

The problem

The issue is around PersonNumber xx0030804/10 and xx0030843/10

Because these have a CCDate which lands between more than one HEStartDate and HEEndDate,- the join doesn't know which to pull through, so pulls both (which is quite fair).

This is my basic query:

SELECT	DISTINCT  m.CCDate
		, m.PersonNumber
		, l.Visit

	FROM [InformationDB].[dbo].[zzz00MainTable] as m

	INNER JOIN [InformationDB].[dbo].[zzz01LookupTable] as l

	ON m.PersonNumber = l.PersonNumber

	AND m.CCDate BETWEEN l.HEStartDate AND l.HEEndDate

	WHERE m.PersonNumber IN ('xx0030843/10','xx0030804/10')

	ORDER BY PersonNumber, Visit

Capture

Desired Results

What I would like to see is; if a record has a CCDate which falls in more than one Visit then for it to take the first. Also, I'm conscious that Visits can increase further than beyond 2.

Thanks for taking the time to read :grinning:

how about using Row_Number

Select CCdate, PersonNumber, Visit from (
SELECT m.CCDate
, m.PersonNumber
,l.Visit
,Row_Number() over (partition by m.CCdate, m.PersonNumber order by m.CCDate, l.HEStartDate, l.HEEndDate) RowNum
FROM [dbo].[zzz00MainTable] as m
INNER JOIN [dbo].[zzz01LookupTable] as l
ON m.PersonNumber = l.PersonNumber
AND m.CCDate BETWEEN l.HEStartDate AND l.HEEndDate
WHERE m.PersonNumber IN ('xx0030843/10','xx0030804/10')) x
where RowNum = 1
ORDER BY PersonNumber, Visit desc

1 Like

Basically the same priciple as the solution by @mike01, but without subquery and with optimized row_number

SELECT TOP(1) WITH TIES
       m.CCDate
      ,m.PersonNumber
      ,l.Visit
  FROM [InformationDB].[dbo].[zzz00MainTable] as m
       INNER JOIN [InformationDB].[dbo].[zzz01LookupTable] as l
               ON m.PersonNumber=l.PersonNumber
              AND m.CCDate BETWEEN l.HEStartDate AND l.HEEndDate
 WHERE m.PersonNumber IN ('xx0030843/10','xx0030804/10')
 ORDER BY ROW_NUMBER() OVER(PARTITION BY m.PersonNumber,m.CCDate ORDER BY l.Visit)
;