Missing years as comma seperated list

Question

Post reply

Missing years as comma seperated list

Atif

Hall of Fame

Points: 3578
More actions
March 2, 2011 at 11:16 pm

#388957

I have like following table with annual reports of employees.
CREATE TABLE demo
(EmpNO tinyint, RYear smallint )
GO
INSERT INTO demo
SELECT 1,2002 UNION ALL
SELECT 1,2001 UNION ALL
SELECT 1,2005 UNION ALL
SELECT 2,2002 UNION ALL
SELECT 2,2006
GO
My task is to get comma separated list of missing years out of years 2001, 2002 and 2003.
The result according to above data would be as
EmpNo MissingYears
1 2003
2 2001,2003
Please suggest optimal method for this task.
Thanks & Regards
Atif
DBDigger Microsoft Data Platform Consultancy.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Try this:

; WITH DistinctEmpNos (EmpNo) AS

(

SELECT DISTINCT EmpNo

FROM demo

),

YearsAndEmps AS

(

SELECT E.EmpNo , Y.Years

FROM DistinctEmpNos E

CROSS JOIN

(

SELECT 2001 UNION ALL

SELECT 2002 UNION ALL

SELECT 2003

) Y (Years)

),

MissingYearsForEachEMp AS

(

SELECT Yrs.EmpNo ,Yrs.Years

FROM demo dem

RIGHT JOIN YearsAndEmps Yrs

ON dem.EmpNo = Yrs.EmpNo

AND dem.RYear = Yrs.Years

WHERE dem.RYear IS NULL

)

SELECT T_O.EmpNo ,STUFF( CAST ( ( SELECT ','+ cast( Years as varchar) FROM MissingYearsForEachEMp T_I

WHERE T_I.EmpNo = T_O.EmpNo

FOR XML PATH(''),TYPE ) AS VARCHAR(MAX)

) , 1,1,'') MissingYears

FROM MissingYearsForEachEMp T_O

GROUP BY T_O.EmpNo

Atif Hall of Fame Points: 3578 More actions · Answer 2

Thanks a lot. It worked great.

DBDigger Microsoft Data Platform Consultancy.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Else:

SELECT distinct E.EmpNO,Y.Years As 'Missing Years' --E.EmpNo , Y.Years , d.EmpNO as 'DEmp', d.RYear

FROM demo E

CROSS JOIN

(

SELECT 2001 UNION ALL

SELECT 2002 UNION ALL

SELECT 2003 UNION ALL

SELECT 2004 UNION ALL

SELECT 2005 UNION ALL

SELECT 2006

) Y (Years)

LEFT JOIN demo d

ON E.EmpNo = d.EmpNO

AND Y.Years = d.RYear

where d.RYear is NULL

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

This doesn't add anything to ColdCoffee's answer. I just saw the other empty thread before I spotted this one so wrote out my solution (pretty much identical to ColdCoffee)

--Create Dummy table

DECLARE @demo AS TABLE

(EmpNO tinyint, RYear smallint )

INSERT INTO @demo

SELECT 1,2002 UNION ALL

SELECT 1,2001 UNION ALL

SELECT 1,2005 UNION ALL

SELECT 2,2002 UNION ALL

SELECT 2,2006

--Actual solution here

;WITH Years (missYear, maxYear)

AS

(

--Create list of all numbers

SELECT 2001 AS missYear, (SELECT MAX(RYear) FROM @demo )

UNION ALL

SELECT missYear + 1, maxYear

FROM Years

--Only needs to go up to the "Max" value

WHERE missYear < maxYear

),

MissingYears AS

(

SELECT DISTINCT EmpNO, Years.missYear

FROM @demo tbl1, Years

--Grabs the missing values

WHERE missYear NOT IN (SELECT RYear FROM @demo tbl2 WHERE tbl1.EmpNO = tbl2.EmpNO)

)

SELECT DISTINCT fin.EmpNo,

--Finally, put the missing years into a comma deliminated list

STUFF(CAST((SELECT ','+ CAST(missYear AS VARCHAR)

FROM MissingYears start WHERE start.EmpNo = fin.EmpNo

FOR XML PATH(''),TYPE ) AS VARCHAR(MAX)),1,1,'') missYear

FROM MissingYears fin

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

WayneS SSC Guru Points: 95460 More actions · Answer 5

Well, I posted a link to a good article in the other thread. But since everyone is posting their code here, here's mine (very similar to you guys...)

DECLARE @demo TABLE (EmpNo tinyint, RYear smallint );

INSERT INTO @demo

SELECT 1,2002 UNION ALL

SELECT 1,2001 UNION ALL

SELECT 1,2005 UNION ALL

SELECT 2,2002 UNION ALL

SELECT 2,2006;

WITH YearsNeeded ([Year]) AS

(

SELECT 2001 UNION ALL

SELECT 2002 UNION ALL

SELECT 2003

), Emps AS

(

SELECT DISTINCT EmpNO

FROM @demo

)

SELECT t.EmpNo,

MissingYears = STUFF((SELECT ',' + CONVERT(CHAR(4),[Year])

FROM YearsNeeded y

WHERE y.Year NOT IN (SELECT RYear

FROM @demo t2

WHERE t2.EmpNo = t.EmpNo)

ORDER BY y.Year

FOR XML PATH(''),TYPE).value('.','varchar(max)'),1,1,'')

FROM Emps t

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 6

Another one to the box:

select d.EmpNO ,

stuff( cast( (

select ','+ cast( Y.Years as varchar)

from demo demo_inner

RIGHT JOIN

(

SELECT 2001 UNION ALL

SELECT 2002 UNION ALL

SELECT 2003

) Y (Years)

on demo_inner.EmpNO = d.EmpNO

and demo_inner.RYear = Y.Years

where demo_inner.RYear is null

order by Y.Years

for XML path (''),TYpe ) as varchar(max)),1,1,''

) missingyrs

from demo d

group by d.EmpNO

Atif Hall of Fame Points: 3578 More actions · Answer 7

Thanks a lot for providing further options.

DBDigger Microsoft Data Platform Consultancy.

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 8

This probably is the fastest i could think of 🙂

select d.EmpNO ,

stuff( (replace ( ',2001,2002,2003',cast( (

select distinct ','+ cast( demo_inner.RYear as varchar)

from demo demo_inner

where d.EmpNO = demo_inner.EmpNO and demo_inner.RYear IN (2001,2002,2003)

for XML path (''),TYpe ) as varchar(max)), '')),1,1,'') missingyrs

from demo d

group by d.EmpNO