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 23:18:58 +0100
Organization: Erland Sommarskog
Lines: 131
Message-ID: <XnsA551ED2F46FBAYazorman@127.0.0.1>
References: <n20m6u$kn7$1@dont-email.me> <XnsA550E759DB2DYazorman@127.0.0.1> <n2325h$ror$1@dont-email.me> <XnsA5519079CD9EYazorman@127.0.0.1>
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 8bit
Injection-Info: mx02.eternal-september.org; posting-host="fa94081cc2dc1914a6be3c29c6bd7913"; logging-data="5973"; mail-complaints-to="abuse@eternal-september.org";	posting-account="U2FsdGVkX1/dbvWn5xpX5A/rVOaLq8rq"
User-Agent: Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock: sha1:NPnfw+11G957OURkL4PwRZzFW7s=
Xref: csiph.com microsoft.public.sqlserver.programming:31282

Here is a solution that uses set-based iteration. That is, it first 
computes row numbers house. Then I iterate over the row numbers, so 
that I can handle the first row for all houses in a single go, and
then move on the second row.

To compute the Line Number, we need a table of numbers. That is a table
with all numbers from 1 and up to some max. As it happens, the highest
number I will ever need, is also the highest row numbers, so I 
extract all row numbers to the table #nums which then doubles as a table
of numbers and as a driver for the cursor.

I modified the dates for HouseID = 2, but I have not verified that the
values I compute the desired ones.

CREATE TABLE [dbo].[Test](
   HouseID    int NOT NULL,
	[DateFrom] [date] NOT NULL,
	[DateTo] [date] NOT NULL,
	[ExpectedLine] [int] NULL,
   ComputedValue 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]) VALUES 
(2, CAST(N'2015-04-01' AS Date), CAST(N'2015-04-17' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-04-12' AS Date), CAST(N'2015-04-23' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-04-15' AS Date), CAST(N'2015-04-27' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-04-18' AS Date), CAST(N'2015-04-24' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-04-25' AS Date), CAST(N'2015-04-30' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-05-05' AS Date), CAST(N'2015-05-07' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-05-03' AS Date), CAST(N'2015-05-08' AS Date))
GO
INSERT [dbo].[Test] (HouseID, DateFrom, [DateTo]) VALUES 
(2, CAST(N'2015-05-02' AS Date), CAST(N'2015-05-12' AS Date))
GO
CREATE TABLE #temp (HouseID  int  NOT NULL, 
                    DateFrom date NOT NULL,
                    DateTo   date NOT NULL,
                    rowno    int  NOT NULL,
                    PRIMARY KEY (rowno, HouseID))

INSERT #temp(HouseID, DateFrom, DateTo, rowno)
   SELECT HouseID, DateFrom, DateTo,
          row_number() OVER (PARTITION BY HouseID ORDER BY DateFrom)
   FROM   Test

CREATE TABLE #nums(num int NOT NULL PRIMARY KEY)
INSERT #nums (num) 
   SELECT DISTINCT rowno FROM #temp

DECLARE @rowno int

DECLARE cur CURSOR STATIC LOCAL FOR
    SELECT num FROM #nums ORDER BY num

OPEN cur

WHILE 1 = 1
BEGIN
  FETCH cur INTO @rowno
  IF @@fetch_status <> 0
     BREAK

  UPDATE Test

  SET    ComputedValue = val.val
  FROM   Test a
  JOIN   #temp b ON a.HouseID = b.HouseID
                AND a.DateFrom = b.DateFrom
  CROSS  APPLY (SELECT MIN(n.num) AS val
                FROM   #nums n
                WHERE  NOT EXISTS (SELECT *
                                   FROM   Test a2
                                   WHERE  a.HouseID = a2.HouseID
                                     AND  a.DateFrom < a2.DateTo
                                     AND  a.DateTo   > a2.DateFrom
                                     AND  n.num      = a2.ComputedValue)) AS val(val)
  WHERE  b.rowno = @rowno
END

DEALLOCATE cur
go
SELECT * FROM Test ORDER BY HouseID, DateFrom
go
drop table Test, #temp, #nums

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