August 19, 2015 at 4:43 am
DROP TABLE #abc;
GO
DROP TABLE #final
GO
CREATE TABLE abc (
id INT
,EID INT
,name NVARCHAR(1000) NULL
,STATUS NVARCHAR(1000) NULL
,start_date DATETIME
,end_date DATETIME
)
GO
INSERT INTO abc VALUES (10,22932,'ABC','Active','2008-12-30','2009-06-30')
INSERT INTO abc VALUES(10,26875,'BBC','Active','2008-12-30','2009-06-30')
INSERT INTO abc VALUES(10,513,'XYZ','Active','2008-12-30','2009-06-30')
INSERT INTO abc VALUES(10,22932,'ABC','Active','2009-07-01','2011-06-29')
INSERT INTO abc VALUES(10,26875,'XYZ','Active','2009-07-01','2012-05-30')
INSERT INTO abc VALUES (10,513,'BBC','Active','2009-07-01','2012-05-30')
INSERT INTO abc VALUES(10,22932,'ABC','Terminated','2011-06-30','2013-04-15')
INSERT INTO abc VALUES(10,26875,'BBC','Active','2012-05-31','2013-04-15')
INSERT INTO abc VALUES(10,513,'XYZ','Active','2012-05-31','2013-04-15')
INSERT INTO abc VALUES(20,513,'XYZ','Active','2013-04-16','2013-12-31')
INSERT INTO abc VALUES(20,26875,'BBC','Active','2013-04-16','2013-12-31')
INSERT INTO abc VALUES(20,22932,'ABC','Terminated','2013-04-16','2013-12-31')
INSERT INTO abc VALUES(30,26875,'BBC','Active','2014-01-01','2014-06-30')
INSERT INTO abc VALUES(30,513,'XYZ','Active','2014-01-01','2014-06-30')
INSERT INTO abc VALUES(30,22932,'ABC','Terminated','2014-01-01','2079-06-06')
INSERT INTO abc VALUES(30,26875,'XYZ','Active','2014-07-01','2079-06-06')
INSERT INTO abc VALUES(30,513,'BBC','Active','2014-07-01','2079-06-06')
SELECT * INTO #abc FROM abc
WHERE STATUS <> 'Terminated';
WITH cte
AS (
SELECT a.id
,a.EID
,a.NAME
,a.start_date
,a.end_date
FROM #abc a
LEFT JOIN #abc b ON a.id = b.id
AND a.Eid = b.EID
AND a.start_date - 1 = b.end_date
WHERE b.id IS NULL
UNION ALL
SELECT a.id
,a.Eid
,a.NAME
,a.start_date
,b.end_date
FROM cte a
JOIN #abc b ON a.id = b.id
AND a.Eid = b.Eid
AND b.start_date - 1 = a.end_date
)
SELECT id
,Eid
,NAME
,start_date
,max(end_date) end_date
INTO #final
FROM cte
GROUP BY id
,Eid
,NAME
,start_date
ORDER BY EID
,id;
As name 'ABC' was started on 2008-12-30 and terminated on 2011-06-29 so he wont come in next records
My Output would be as like below its all depands on the status of the records id and the start date will +1 where the end date of the previous records
and ids will fall between those dates upon the status of the records
By using below sql I need to show the out put below,
WITH cte
AS (
SELECT *
FROM #final
)
SELECT t1.id
,CONVERT(VARCHAR(8),t1.start_date,112) AS start_date
,CONVERT(VARCHAR(8),t1.end_date,112) AS end_date
,name = STUFF((
SELECT DISTINCT ', ' + CAST(name AS VARCHAR(MAX))
FROM cte t2
WHERE t2.id = t1.id
FOR XML PATH('')
), 1, 1, '')
FROM cte t1
GROUP BY t1.id
,t1.start_date
,t1.end_date
ORDER BY 1
,2
ID start_date end_date name
102008123020110629ABC, BBC, XYZ
102011063020130415BBC, XYZ
202013041620131231BBC, XYZ
302014010120790606BBC, XYZ
So please suggest me how to do this in my above sql
Regards,
Kiran
August 19, 2015 at 6:23 am
Hi,
Not sure if I'm the only one but really hard to understand what you are trying to accomplish and what the question is.
thanks
JG
August 19, 2015 at 6:36 am
The name ABC,BBC,XYZ are active between dates 20081230 and 20110629
so records should display
102008123020110629ABC, BBC, XYZ
and names BBC,XYZ are active between dates 20110630 and 20130415 and
name=ABC is terminated on 20110630 so records should display
102011063020130415BBC, XYZ
and for dates 20130416 and 20131231 only two records are active so records should display
202013041620131231BBC, XYZ
and for dates 20140101and 20790606only two records are active so records should display
302014010120790606BBC, XYZ
August 19, 2015 at 10:36 am
kiran.rajenimbalkar (8/19/2015)
CREATE TABLE abc (
id INT
,EID INT
,name NVARCHAR(1000) NULL
,STATUS NVARCHAR(1000) NULL
,start_date DATETIME
,end_date DATETIME
)
GO
INSERT INTO abc VALUES (10,22932,'ABC','Active','2008-12-30','2009-06-30')
INSERT INTO abc VALUES(10,26875,'BBC','Active','2008-12-30','2009-06-30')
INSERT INTO abc VALUES(10,513,'XYZ','Active','2008-12-30','2009-06-30')
INSERT INTO abc VALUES(10,22932,'ABC','Active','2009-07-01','2011-06-29')
INSERT INTO abc VALUES(10,26875,'XYZ','Active','2009-07-01','2012-05-30')
INSERT INTO abc VALUES (10,513,'BBC','Active','2009-07-01','2012-05-30')
INSERT INTO abc VALUES(10,22932,'ABC','Terminated','2011-06-30','2013-04-15')
INSERT INTO abc VALUES(10,26875,'BBC','Active','2012-05-31','2013-04-15')
INSERT INTO abc VALUES(10,513,'XYZ','Active','2012-05-31','2013-04-15')
INSERT INTO abc VALUES(20,513,'XYZ','Active','2013-04-16','2013-12-31')
INSERT INTO abc VALUES(20,26875,'BBC','Active','2013-04-16','2013-12-31')
INSERT INTO abc VALUES(20,22932,'ABC','Terminated','2013-04-16','2013-12-31')
INSERT INTO abc VALUES(30,26875,'BBC','Active','2014-01-01','2014-06-30')
INSERT INTO abc VALUES(30,513,'XYZ','Active','2014-01-01','2014-06-30')
INSERT INTO abc VALUES(30,22932,'ABC','Terminated','2014-01-01','2079-06-06')
INSERT INTO abc VALUES(30,26875,'XYZ','Active','2014-07-01','2079-06-06')
INSERT INTO abc VALUES(30,513,'BBC','Active','2014-07-01','2079-06-06')
SELECT * INTO #abc FROM abc
WHERE STATUS <> 'Terminated';
WITH cte
AS (
SELECT a.id
,a.EID
,a.NAME
,a.start_date
,a.end_date
FROM #abc a
LEFT JOIN #abc b ON a.id = b.id
AND a.Eid = b.EID
AND a.start_date - 1 = b.end_date
WHERE b.id IS NULL
UNION ALL
SELECT a.id
,a.Eid
,a.NAME
,a.start_date
,b.end_date
FROM cte a
JOIN #abc b ON a.id = b.id
AND a.Eid = b.Eid
AND b.start_date - 1 = a.end_date
)
SELECT id
,Eid
,NAME
,start_date
,max(end_date) end_date
INTO #final
FROM cte
GROUP BY id
,Eid
,NAME
,start_date
ORDER BY EID
,id;
As name 'ABC' was started on 2008-12-30 and terminated on 2011-06-29 so he wont come in next records
My Output would be as like below its all depands on the status of the records id and the start date will +1 where the end date of the previous records
and ids will fall between those dates upon the status of the records
By using below sql I need to show the out put below,
WITH cteAS (
SELECT *
FROM #final
)
SELECT t1.id
,CONVERT(VARCHAR(8),t1.start_date,112) AS start_date
,CONVERT(VARCHAR(8),t1.end_date,112) AS end_date
,name = STUFF((
SELECT DISTINCT ', ' + CAST(name AS VARCHAR(MAX))
FROM cte t2
WHERE t2.id = t1.id
FOR XML PATH('')
), 1, 1, '')
FROM cte t1
GROUP BY t1.id
,t1.start_date
,t1.end_date
ORDER BY 1
,2
ID start_date end_date name102008123020110629ABC, BBC, XYZ
102011063020130415BBC, XYZ
202013041620131231BBC, XYZ
302014010120790606BBC, XYZ
So please suggest me how to do this in my above sql
Regards,
Kiran
Thank you for posting the code, but I have an issue with it. It is obvious that you are running on a system using a case insensitive collation, but not everyone does. Your code will not run in a case sensitive environment because of inconsistencies in your use of column names. I would suggest that when writing code you be consistent with column names, variable names, etc. If your table has a column declared as EID, then use EID in your code. Don't use EID in some places then Eid in others. Yes, it works in a case insensitive environment but it shows a lack of accuracy and consistency.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply