Solved

t-sql query help

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

828 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