Temat: pętla / zapytanie SQL
Cześć,
Trochę utrudniłeś dając dane wejściowe w postaci obrazka. :/
Tabelka:
CREATE TABLE [dbo].[temp](
[id] [int] IDENTITY(1,1) NOT NULL,
[od] [datetime] NULL,
[do] [datetime] NULL,
[kod] [varchar](50) NULL,
[lp] [int] NULL,
CONSTRAINT [PK_temp] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2014-02-15 00:00:00.000' AS DateTime), CAST('2014-05-31 00:00:00.000' AS DateTime), N'B15', 1)
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2014-06-01 00:00:00.000' AS DateTime), CAST('2014-07-31 00:00:00.000' AS DateTime), N'B15', 2)
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2014-08-01 00:00:00.000' AS DateTime), CAST('2014-09-30 00:00:00.000' AS DateTime), N'B15', 3)
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2014-10-01 00:00:00.000' AS DateTime), CAST('2014-12-20 00:00:00.000' AS DateTime), N'B15', 4)
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2013-01-20 00:00:00.000' AS DateTime), CAST('2014-07-31 00:00:00.000' AS DateTime), N'A11', 1)
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2014-08-01 00:00:00.000' AS DateTime), CAST('2014-01-31 00:00:00.000' AS DateTime), N'A11', 2)
INSERT [dbo].[temp] ([od], [do], [kod], [lp]) VALUES (CAST('2015-01-01 00:00:00.000' AS DateTime), CAST('2999-12-31 00:00:00.000' AS DateTime), N'A11', 3)
Zapytanie:
SELECT * FROM dbo.temp;
WITH ble AS
(
SELECT
prevRange.od prevRangeOd
,prevRange.do prevRangeDo
,nextRange.od nextRangeOd
,nextRange.do nextRangeDo
,prevRange.kod
,nextRange.lp
,CASE WHEN DATEADD(DAY,1, prevRange.do) = nextRange.od THEN 0 ELSE 1 END AS ContainsGap
FROM dbo.temp prevRange
JOIN dbo.temp nextRange ON prevRange.kod = nextRange.kod AND prevRange.lp = nextRange.lp - 1
)
SELECT
a.kod
,COALESCE(
(SELECT MAX(b.nextRangeOd) FROM ble b WHERE b.kod = a.kod AND ContainsGap = 1),
(SELECT MIN(b.prevRangeOd) FROM ble b WHERE b.kod = a.kod AND ContainsGap = 0)
) AS DateFrom
,nextRangeDo as DateTo
FROM ble a
WHERE lp = (SELECT MAX(lp) FROM ble poi WHERE poi.kod = a.kod)