Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

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

Re: Assigning Display Lines to Date Ranges

Show key headers only | View raw

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

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