Groups > microsoft.public.sqlserver.programming > #31281

Re: Assigning Display Lines to Date Ranges

From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	microsoft.public.sqlserver.programming
Subject	Re: Assigning Display Lines to Date Ranges
Date	2015-11-13 14:12 +0100
Organization	Erland Sommarskog
Message-ID	<XnsA5519079CD9EYazorman@127.0.0.1> (permalink)
References	<n20m6u$kn7$1@dont-email.me> <XnsA550E759DB2DYazorman@127.0.0.1> <n2325h$ror$1@dont-email.me>

Show all headers | View raw

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

Back to microsoft.public.sqlserver.programming | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

Assigning Display Lines to Date Ranges Michael Cole <invalid@invalid.com> - 2015-11-12 11:25 +1100
  Re: Assigning Display Lines to Date Ranges Erland Sommarskog <esquel@sommarskog.se> - 2015-11-12 22:44 +0100
    Re: Assigning Display Lines to Date Ranges Michael Cole <invalid@invalid.com> - 2015-11-13 09:01 +1100
      Re: Assigning Display Lines to Date Ranges Erland Sommarskog <esquel@sommarskog.se> - 2015-11-13 14:12 +0100
        Re: Assigning Display Lines to Date Ranges Erland Sommarskog <esquel@sommarskog.se> - 2015-11-13 23:18 +0100
          Re: Assigning Display Lines to Date Ranges Michael Cole <invalid@invalid.com> - 2015-11-16 11:34 +1100
  Re: Assigning Display Lines to Date Ranges --CELKO-- <jcelko212@earthlink.net> - 2015-11-12 18:47 -0800

csiph-web