CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Excited_to_learn
Excited_to_learn

Reputation: 359

Deriving start and end date from a date range for a particular record

Using SQL Server 2008

I have a record number and a column with 'in' and 'out' data with some dates associated with it. I want to derive their start date and end date based on a criteria. min date becomes the start date and immediate next date becomes the end date. a single record can have multiple in and outs. Here's the sample query with few options tried but nothing worked.

IF OBJECT_ID('tempdb..#tmp_Info') IS NOT NULL DROP TABLE #tmp_Info
IF OBJECT_ID('tempdb..#RN') IS NOT NULL DROP TABLE #RN

CREATE TABLE #tmp_Info
(
RECORD_NO           INT, 
TYPE_1          VARCHAR(10), 
TYPE_2          VARCHAR(250), 
START_END           DATE
)


INSERT INTO #tmp_Info (RECORD_NO, TYPE_1, TYPE_2, START_END)

VALUES
(261505,'IN','ADM','1/1/2001'),
(261505,'OUT','RELEASE','2/6/2001'),
(261505,'IN','ADM','2/10/2001'),
(261505,'OUT','RELEASE','4/2/2001'),
(261505,'IN','ADM','5/12/2001'),
(261505,'OUT','RELEASE','7/13/2001'),
(261506,'IN','ADM','1/12/2001'),
(261506,'OUT','RELEASE','2/15/2001'),
(261506,'IN','ADM','2/20/2001'),
(261506,'OUT','RELEASE','4/2/2001'),
(261506,'IN','ADM','5/15/2001'),
(261506,'OUT','RELEASE','7/17/2001'),
(261506,'IN','ADM','8/1/2001')

SELECT * FROM #tmp_Info;

-->>>> OPTION 1
WITH STARTEND AS
(
SELECT T.RECORD_NO, T.START_END, RN = ROW_NUMBER() OVER (PARTITION BY RECORD_NO, TYPE_1 ORDER BY START_END) 
FROM #tmp_Info T
)
SELECT A.RECORD_NO,A.START_END AS START_DT, B.START_END AS END_DT
FROM STARTEND A
LEFT JOIN STARTEND B ON A.RECORD_NO = B.RECORD_NO AND A.RN = B.RN+1
ORDER BY A.RECORD_NO, A.START_END;

-->>>>OPTION 2

SELECT T.*, RN = ROW_NUMBER() OVER (PARTITION BY RECORD_NO, TYPE_1  ORDER BY START_END)
into #RN 
FROM #tmp_Info T

SELECT * FROM #RN

I am attaching the result set I am looking for:enter image description here

Upvotes: 1

Views: 53

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You can use the following query:

;WITH CTE_Rn AS (
   SELECT RECORD_NO, TYPE_1, TYPE_2, START_END,
          ROW_NUMBER() OVER (PARTITION BY RECORD_NO, TYPE_1 
                             ORDER BY START_END) AS rn
   FROM #tmp_Info
)
SELECT t1.RECORD_NO, t1.START_END AS START_DT, t2.START_END AS END_DT           
FROM CTE_Rn AS t1
LEFT JOIN CTE_Rn AS t2
ON t1.RECORD_NO = t2.RECORD_NO AND    
   t2.TYPE_1 = 'OUT' AND
   t1.rn = t2.rn
WHERE t1.TYPE_1 = 'IN';

Upvotes: 1

Related Questions