Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

t-sql query help

Posted on 2013-05-21
4
Medium Priority
?
240 Views
Last Modified: 2013-05-21
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...
0
Comment
Question by:jfreeman2010
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Surendra Nath
ID: 39184435
which version of sql server are you using?
0
 

Author Comment

by:jfreeman2010
ID: 39184499
sql server 2012
0
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 2000 total points
ID: 39184868
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
 

Author Closing Comment

by:jfreeman2010
ID: 39184923
thank you very much for help!!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

783 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question