t-sql query help

I need to come up a query to find the missing recs, the following are the best example I can come up:

CREATE TABLE #TMPTB
(
      EMPID      INT,
      EMPNAME VARCHAR(100),
      WORK_DAY SMALLINT,
      SCHED_DATE DATE,
      WORK_DATE1 DATE,
      WORK_DATE2 DATE,
      WORK_DATE3 DATE,
      WORK_DATE4 DATE
)

DECLARE @today_date  date;

set @today_date = '2013-05-20'

INSERT INTO #TMPTB values(6427,      'John Sminit',       1,      '2012-09-14',      '2012-10-03',      '2012-12-14',      '2013-03-14',      '2013-06-14' )
INSERT INTO #TMPTB values(6427,      'John Sminit',       2,      '2012-09-14',      '2012-12-13',      '2012-12-14',      '2013-03-14',      '2013-06-14' )
INSERT INTO #TMPTB values(6427,      'John Sminit',       3,      '2012-09-14',      '2013-03-13',      '2012-12-14',      '2013-03-14',      '2013-06-14' )

INSERT INTO #TMPTB values(470772,      'John Doe',       1,      '2012-08-09',      '2012-08-23',      '2012-11-09',      '2013-02-09',      '2013-05-09' )
INSERT INTO #TMPTB values(470772,      'John Doe',       4,      '2012-08-09',      '2013-05-06',      '2012-11-09',      '2013-02-09',      '2013-05-09' )

INSERT INTO #TMPTB values(470776,      'Mary Doe',       1,      '2012-09-22',      '2012-09-25',      '2012-12-22',      '2013-03-22',      '2013-06-22' )
INSERT INTO #TMPTB values(470776,      'Mary Doe',       2,      '2012-09-22',      '2012-12-21',      '2012-12-22',      '2013-03-22',      '2013-06-22' )
INSERT INTO #TMPTB values(470776,      'Mary Doe',       3,      '2012-09-22',      '2013-03-21',      '2013-03-22',      '2013-03-22',      '2013-06-22' )

INSERT INTO #TMPTB values(484278,      'Mary Sminit',       1,      '2012-08-24',      '2012-08-28',      '2012-11-24',      '2013-02-24',      '2013-05-24' )
INSERT INTO #TMPTB values(484278,      'Mary Sminit',       3,      '2012-08-24',      '2013-05-14',      '2012-11-24',      '2013-02-24',      '2013-05-24' )

INSERT INTO #TMPTB values(485026,      'Jane Sminit',       1,      '2012-09-19',      '2012-10-09',      '2012-12-19',      '2013-03-19',      '2013-06-19' )
INSERT INTO #TMPTB values(485026,      'Jane Sminit',       3,      '2012-09-19',      '2013-05-14',      '2012-12-19',      '2013-03-19',      '2013-06-19' )

INSERT INTO #TMPTB values(486272,      'Jane Doe',       1,      '2012-08-24',      '2012-08-28',      '2012-11-24',      '2013-02-24',      '2013-05-24' )
INSERT INTO #TMPTB values(486272,      'Jane Doe',       3,      '2012-08-24',      '2013-05-14',      '2012-11-24',      '2013-02-24',      '2013-05-24' )

INSERT INTO #TMPTB values(485366,      'JJ Doe',       1,      '2012-08-09',      '2012-08-23',      '2012-11-09',      '2013-02-09',      '2013-05-09' )
INSERT INTO #TMPTB values(485366,      'JJ Doe',       4,      '2012-08-09',      '2013-05-14',      '2012-11-09',      '2013-02-09',      '2013-05-09' )


SELECT * FROM #TMPTB



/*

LOOKING RESULT LIKE THIS:


empid      empname                  sched_date  work_date1  work_date2  work_date3  work_date4      missing_work_date
470772      John Doe            2012-08-09      2012-08-23      2012-11-09      2013-02-09      2013-05-09  2, 3
484278      Mary Sminit            2012-08-24      2012-08-28      2012-11-24      2013-02-24      2013-05-24  2
485026      Jane Sminit            2012-09-19      2012-10-09      2012-12-19      2013-03-19      2013-06-19  2
486272      Jane Doe            2012-08-24      2012-08-28      2012-11-24      2013-02-24      2013-05-24  2
486272      JJ Doe                  2012-08-09      2013-05-14      2012-11-09      2013-02-09      2013-05-09  2,3 (the last work date should be 2013-05-09, but missed, so I did a make up date at 2013-05-14)

*/


DROP TABLE #TMPTB;


Thank you very much for helping...
jfreeman2010Asked:
Who is Participating?
 
Surendra NathTechnology LeadCommented:
The below one is a little big query, a lot of CTE's are put in place ( inorder to avoid the recursion and while loops), The result set is as you desired.

;with T AS
(
select empid,max(WORK_DAY) max_wd, min(work_day) min_wd FROM #TMPTB group by EMPID
),SequentialNumber AS
(
select number
from master.dbo.spt_values
where type='P'
),EMPJ AS
(
select EMPID,number 
FROM T 
CROSS APPLY (select * from SequentialNumber SN where sn.number between T.min_wd and T.max_wd) B
),EMPJ1 AS
(
select E.EMPID,E.number
from #TMPTB T
RIGHT JOIN EMPJ E
ON E.EMPID = T.EMPID
and e.number = T.WORK_DAY
where T.WORK_DAY is null
),concatenation AS
(
select EMPID, ( select stuff((select ',' + cast(number as varchar) from EMPJ1 E2 where e2.EMPID = e1.EMPID for xml path('')), 1,1,'')) as missingdays
from EMPJ1 E1
),AddingRowNumber AS
(
	select row_number() over(partition by EMPID order by WORK_DAY) as rn,* from #TMPTB
),Top1fromTheList as
(
	select * from AddingRowNumber where rn = 1
),DistinctConcat AS
(
select distinct EMPID,missingdays 
from concatenation C
)
select T.*,D.missingdays 
from DistinctConcat D
join Top1fromTheList T
on T.EMPID = D.EMPID

Open in new window

0
 
Surendra NathTechnology LeadCommented:
which version of sql server are you using?
0
 
jfreeman2010Author Commented:
sql server 2012
0
 
jfreeman2010Author Commented:
thank you very much for help!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.