Finding Missing Dates for Each EmpIDs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Finding Missing Dates for Each EmpIDs

Author

Topic

rajeshnrh74
Starting Member

5 Posts

Posted - 2008-06-06 : 22:56:17

Friends

I'm using Sql Server 2005, in which I've table like this

USE [Sample]
GO
/****** Object: Table [dbo].[Table1] Script Date: 06/07/2008 03:10:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpID] [int] NULL
) ON [PRIMARY]

INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)

My task is I want to find Missing Dates (Except Saturday, Sunday)
for each Employee.

Note: Missing Dates should be Working Days only (Excluding Saturday & Sunday)

Help me out

Thanks
Rajesh N.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-07 : 03:57:50

[code]SET DATEFIRST 7
DECLARE @MinDate datetime,@MaxDate datetime

SELECT @MinDate=MIN(TimesheetDate),@MaxDate=MAX(TimesheetDate)
FROM Table1

SELECT m.CalendarDate
FROM
(SELECT DATEADD(dd,number,@MinDate) AS CalendarDate
FROM master..spt_values
WHERE type='p'
AND DATEADD(dd,number,@MinDate)<=@MaxDate
AND DATEPART(dw,DATEADD(dd,number,@MinDate))NOT IN (1,7))m
LEFT JOIN Table1 t1
ON t1.TimesheetDate=m.CalendarDate
WHERE t1.TimesheetDate IS NULL[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-07 : 05:18:17

[code];WITH Yak(EmpID, TD, maxTD)
AS (
SELECT EmpID,
MIN(TimesheetDate) AS minTD,
MAX(TimesheetDate) AS maxTD
FROM @Table1
GROUP BY EmpID

UNION ALL

SELECT EmpID,
TD + 1,
maxTD
FROM Yak
WHERE TD < maxTD
)

SELECT EmpID,
TD AS TimesheetDate
FROM Yak
ORDER BY EmpID,
TD[/code]

E 12°55'05.25"
N 56°04'39.16"

ayamas
Aged Yak Warrior

552 Posts

Posted - 2008-06-07 : 07:09:38

Maybe this is what you want

CREATE TABLE [dbo].[Table1](
[TimesheetDate] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[EmpID] [int] NULL
) ON [PRIMARY]

INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',3)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-05 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-17 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-18 00:00:00.000',9)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-01 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',10)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-06 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-07 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-08 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-10 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-11 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-12 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-14 00:00:00.000',11)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',13)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-04 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-24 00:00:00.000',14)
INSERT INTO [Table1] ([TimesheetDate],[EmpID])VALUES('2008-03-03 00:00:00.000',15)

Set Nocount on

;With CTE(AllDates,Empid)As
(
Select Dateadd(dd,number,t.TimesheetDate)As AllDates,t.empid
From [Table1]t
Inner Join master.dbo.spt_values On master.dbo.spt_values.Type='p'
Where master.dbo.spt_values.number<=
Datediff(dd,(Select Min(timesheetdate) From table1),(Select Max(timesheetdate) From table1))
)

Select t.Empid,AllDates From cte inner join
(
Select Min(timesheetdate)As MinDate,Max(timesheetdate)As MaxDate,empid From [Table1] Group by empid

)

t On cte.empid=t.empid
Where AllDates Between MinDate And MaxDate And Datename(weekday,AllDates)
Not in('Saturday','Sunday') Group By t.empid,AllDates
Order By t.empid,AllDates

drop table [Table1]

rajeshnrh74
Starting Member

5 Posts

Posted - 2008-06-08 : 01:23:08

Friends Thanks for your quick response, and admiring your knowledge.
thanks a lot once again.

Only one thing I forgot to post, the date should be from
01 Mar 2008 to 31 Mar 2008 within this range I want to find all the
Missing dates for each employees.

thanks, if you help me out to solve this.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-08 : 02:42:09

[code]DECLARE @Table1 TABLE (TimesheetDate VARCHAR(50), EmpID INT)

INSERT @Table1 VALUES('2008-03-07 00:00:00.000', 3)
INSERT @Table1 VALUES('2008-03-18 00:00:00.000', 3)
INSERT @Table1 VALUES('2008-03-03 00:00:00.000', 9)
INSERT @Table1 VALUES('2008-03-04 00:00:00.000', 9)
INSERT @Table1 VALUES('2008-03-05 00:00:00.000', 9)
INSERT @Table1 VALUES('2008-03-17 00:00:00.000', 9)
INSERT @Table1 VALUES('2008-03-18 00:00:00.000', 9)
INSERT @Table1 VALUES('2008-03-01 00:00:00.000', 10)
INSERT @Table1 VALUES('2008-03-03 00:00:00.000', 10)
INSERT @Table1 VALUES('2008-03-03 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-04 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-06 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-07 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-08 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-10 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-11 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-12 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-14 00:00:00.000', 11)
INSERT @Table1 VALUES('2008-03-03 00:00:00.000', 13)
INSERT @Table1 VALUES('2008-03-04 00:00:00.000', 14)
INSERT @Table1 VALUES('2008-03-24 00:00:00.000', 14)
INSERT @Table1 VALUES('2008-03-03 00:00:00.000', 15)

SELECT e.EmpID,
d.theDate
FROM (
SELECT EmpID
FROM @Table1
GROUP BY EmpID
) AS e
CROSS JOIN (
SELECT DATEADD(DAY, Number, '20080301') AS theDate
FROM master..spt_values
WHERE Type = 'P'
AND Number BETWEEN 0 AND 30
) AS d
LEFT JOIN @Table1 AS t1 ON t1.EmpID = e.EmpID
AND t1.TimesheetDate = d.theDate
WHERE t1.EmpID IS NULL
AND DATENAME(WEEKDAY, d.theDate) NOT IN ('Saturday', 'Sunday')[/code]

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-08 : 05:17:25

quote:
Originally posted by rajeshnrh74

Friends Thanks for your quick response, and admiring your knowledge.
thanks a lot once again.

Only one thing I forgot to post, the date should be from
01 Mar 2008 to 31 Mar 2008 within this range I want to find all the
Missing dates for each employees.

thanks, if you help me out to solve this.

You can just substitute 01 Mar 2008 & 31 Mar 2008 for @MinDate and @MaxDate in my query to achieve this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources