Solved

t-sql query help

Posted on 2013-05-21
4
230 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 500 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

770 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