Help on Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Help on Query

Author

Topic

sridhar3004
Starting Member

34 Posts

Posted - 2014-08-05 : 14:35:13

I have the following data

Startdate EndDate Available
2014-07-01 2014-07-05 1
2014-07-06 2014-07-10 1
2014-07-11 2014-07-15 0
2014-07-16 2014-07-20 0
2014-07-21 2014-07-26 1
2014-07-27 2014-07-31 1

I want a query that will retrieve the start date and end date of all the available time

so for the above data, the first available slot will be 2014-07-01 and 2014-07-10 (this is the start date of first record and end date of second row)

rows 3 and 4 is not available and will not be a part of the available slots query

the second available slot will 21 Jul 14 to 31 Jul 2014

so only 2 rows will be displayed as an output from the above data

Thanks
Sridhar

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-06 : 02:07:00

[code]
declare @tData TABLE
(
Startdate DATE,
EndDate DATE,
Available BIT)

INSERT INTO @tData (Startdate,EndDate,Available)
VALUES ('2014-07-01','2014-07-05', 1)
,('2014-07-06','2014-07-10',1)
,('2014-07-11', '2014-07-15', 0)
,('2014-07-16', '2014-07-20', 0)
,('2014-07-21', '2014-07-26', 1)
,('2014-07-27', '2014-07-31', 1)

;with cteDATA
AS
(
SELECT MIN(Startdate) AS Startdate
, MIN(OA_E.EndDate) AS EndDate

FROM @tData
OUTER APPLY
(
SELECT
ROW_NUMBER() OVER(ORDER BY B.Startdate) AS RN
,DATEADD(d,-1,B.Startdate) as EndDate
FROM
@tData AS B
WHERE
B.Available = 0
)OA_E
WHERE Available = 1

UNION ALL

SELECT
S.Startdate
,COALESCE(E.EndDate ,E2.EndDate )
FROM
cteDATA AS A
OUTER APPLY
(
SELECT
ROW_NUMBER() OVER(ORDER By B.Startdate) as RN
,B.Startdate AS StartDate
FROM
@tData AS B
WHERE
B.Startdate > A.EndDate
AND B.Available = 1
)S -- for start date

OUTER APPLY
(
SELECT
ROW_NUMBER() OVER(ORDER BY B.Startdate) AS RN
,DATEADD(d,-1,B.Startdate) as EndDate
FROM
@tData AS B
WHERE
B.StartDate > A.EndDate
AND B.Available = 0
AND B.Startdate > S.StartDate
)E -- for End DATE

OUTER APPLY
(
SELECT
ROW_NUMBER() OVER(ORDER BY B.Startdate DESC) AS RN
,B.EndDate AS EndDate
FROM
@tData AS B
WHERE
B.Available = 1
)E2 -- for the last row

WHERE
S.RN = 1
AND ((E.RN = 1) OR (E2.RN = 1 AND E.RN IS NULL))

)

SELECT
DISTINCT
*
FROM
cteDATA as A

[/code]

output:
[code
Startdate EndDate
2014-07-01 2014-07-10
2014-07-21 2014-07-31
[/code]

sabinWeb MCP

sridhar3004
Starting Member

34 Posts

Posted - 2014-08-06 : 02:39:48

Thank you very much

It works

Thanks
Sridhar

stepson
Aged Yak Warrior

545 Posts

Posted - 2014-08-06 : 14:08:44

Your welcome!

sabinWeb MCP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources