Path: csiph.com!eternal-september.org!feeder.eternal-september.org!mx02.eternal-september.org!.POSTED!not-for-mail
From: Erland Sommarskog <esquel@sommarskog.se>
Newsgroups: microsoft.public.sqlserver.programming
Subject: Re: Assigning Display Lines to Date Ranges
Date: Fri, 13 Nov 2015 14:12:09 +0100
Organization: Erland Sommarskog
Lines: 86
Message-ID: <XnsA5519079CD9EYazorman@127.0.0.1>
References: <n20m6u$kn7$1@dont-email.me> <XnsA550E759DB2DYazorman@127.0.0.1> <n2325h$ror$1@dont-email.me>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx02.eternal-september.org; posting-host="265adb1c021f4e45f9de4dc4b7d8dc41"; logging-data="1187"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX19beB0ggstP5UIBFnsV0YJg"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:p8ifWu6oFTH1dHZ0WcDSW6vT928=
Xref: csiph.com microsoft.public.sqlserver.programming:31281

Michael Cole (invalid@invalid.com) writes:
> They are.  We are looking at about 70 entities, with a complete date 
> range of about a year.  Individual ranges of about a month.  For the 
> record, they are for bricklayers on new house builds.

OK, so we could add HouseID to your test table like the below?

CREATE TABLE [dbo].[Test](
   HouseID    int NOT NULL,
	[DateFrom] [date] NOT NULL,
	[DateTo] [date] NOT NULL,
	[ExpectedLine] [int] NULL,
   PRIMARY KEY (HouseID, DateFrom)
) ON [PRIMARY]

GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-01-01' AS Date), CAST(N'2015-01-10' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-01-12' AS Date), CAST(N'2015-01-16' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-01-15' AS Date), CAST(N'2015-01-20' AS Date), 2)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-01-18' AS Date), CAST(N'2015-01-24' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-01-25' AS Date), CAST(N'2015-01-30' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-02-01' AS Date), CAST(N'2015-02-07' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-02-03' AS Date), CAST(N'2015-02-08' AS Date), 2)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-02-06' AS Date), CAST(N'2015-02-12' AS Date), 3)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-02-11' AS Date), CAST(N'2015-02-20' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(1, CAST(N'2015-02-16' AS Date), CAST(N'2015-02-24' AS Date), 2)

INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-01-01' AS Date), CAST(N'2015-01-10' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-01-12' AS Date), CAST(N'2015-01-16' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-01-15' AS Date), CAST(N'2015-01-20' AS Date), 2)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-01-18' AS Date), CAST(N'2015-01-24' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-01-25' AS Date), CAST(N'2015-01-30' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-02-01' AS Date), CAST(N'2015-02-07' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-02-03' AS Date), CAST(N'2015-02-08' AS Date), 2)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-02-06' AS Date), CAST(N'2015-02-12' AS Date), 3)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-02-11' AS Date), CAST(N'2015-02-20' AS Date), 1)
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo], [ExpectedLine]) VALUES 
(2, CAST(N'2015-02-16' AS Date), CAST(N'2015-02-24' AS Date), 2)


GO
select * FROM Test
go
drop table Test



-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se