Getting dates that do not appear in list

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

General SQL Server Forums

New to SQL Server Programming

Getting dates that do not appear in list

Author

Topic

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-22 : 09:20:35

Hi all

I've got a bit of a conundrum here.
I've got a list of dates when people are working and their start and finish times.
What I need to get is the days/dates they are not working (i.e. their days off). Unfortunately, if they have a day off, there is no record in the database to show that - that day just does not exist.

Anyone any ideas how I can get a list of dates missing from a list I actually have?

TIA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 09:26:49

You need a list of dates to check

;with cte
as
(
select dte = convert(datetime,'20100101')
union all
select dte = cte.dte + 1 from cte where dte < getdate()
)
select p.personid, d.dte
from cte d
cross join (select distinct personid from workdates) p
left join workdates w
on d.dte = w.dte
and p.personid = w.personid
where w.personid is null

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-22 : 11:05:39

Can you walk me through that?
Can't quite get the hang of it.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 11:14:38

the cte gives a list of dates - it's 1 jan 2010 up to todys dte - you will probably want to restrict that.
That then cross joins to the distinct perdonids to give a resultset of each personid for each date
that then left joins to the workdates (which is the table that holds the login/logouts) using the persoid and date.
This will give a row for each date, personid with the data from workdtes if it exists otherwise null.
The where clause excludes the rows with data from workdtes leaving only those with no login/logout.

You might want to restrict this to only working days which would be a change to the cte.
To do this sort of thing you really need a calendar table.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-22 : 11:24:23

Thanks for that.
I have a calendar table (DIM_calendar) and my original table with data in it (for those actually working) is called #tmp_working.
#tmp_working already has one row per person for each date containing their shift start/end times.
The calendar is just that, a list of calendar dates with no other info.
Do I still need all of that code?
Apologies for being a bit thick but I'm new to this.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-22 : 11:45:24

This is my from and where clauses:-


from
	DIM_Calendar cal
	cross join (select distinct emp.EmpId from employees_details emp) tmp
	left join #tmp_working t1 on cal.Date=t1.Date and tmp.EmpId=t1.EmpId
where
	cal.[Date] between @start_date and @end_date
	and t1.EmpId is null

cal (DIM_Calendar) is my calendar table, #tmp_working holds my start/end times of the current shifts and tmp is simply a list of employee IDs.

The #tmp_working table holds just over 7000 records but when I run the entire query, I just get a list of the dates I want but all the other fields are null.

Any ideas where I've gone wrong?

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 11:51:01

what is t1.Date? a date or does it contain a time as well?
try
left join #tmp_working t1 on cal.Date=convert(varchar(8),t1.Date,112) and tmp.EmpId=t1.EmpId

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-22 : 12:02:14

t1.date is just a date. It starts out as datetime but is separated out further up the query.
I'll try the other let join and let you know how I get on.

rmg1
Constraint Violating Yak Guru

256 Posts

Posted - 2010-11-22 : 12:04:07

Unfortunately, that's just giving me a list of dates again.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-22 : 12:58:30

Do you get values from tmp.EmpId in the resultset? You must do otherwise you wouldn't get anything.
It's failing to join.
Need to find out whether it's the date or emp id that's causing the issue.

Is cal.date and #tmp_working.date the same datatype and both contain the date with no time.
Are there entries in #tmp_working between the dates specified?

Try retricting it to a single emp id (that you know has data) by putting
and tmp.EmpId = xxxx
in the where clause

Could be that you have some emp id's that have little data and the emp ids with data are further down the resultset.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sreekanth939
Starting Member

12 Posts

Posted - 2010-11-23 : 06:24:33

hai,

Below query will generate date series of approx. 5 year.
By proper "join" you can use this query for getting missing dates.

select NUMS from (
select dateadd(dd,B.i+B1.i+B2.i+B3.i+B4.i+B5.i+B6.i+B7.i+B8.i+B9.i+B10.i,'1/1/2000') as NUMS from
(select 0 as i union all select 1 as i) as B
cross join
(select 0 as i union all select 2 as i) as B1
cross join
(select 0 as i union all select 4 as i) as B2
cross join
(select 0 as i union all select 8 as i) as B3
cross join
(select 0 as i union all select 16 as i) as B4
cross join
(select 0 as i union all select 32 as i) as B5
cross join
(select 0 as i union all select 64 as i) as B6
cross join
(select 0 as i union all select 128 as i) as B7
cross join
(select 0 as i union all select 256 as i) as B8
cross join
(select 0 as i union all select 512 as i) as B9
cross join
(select 0 as i union all select 1024 as i) as B10
) as DA
order by 1 asc

for example :

create table ATTEDANCE
(
id bigint
,date datetime
,status varchar(3)
,intime datetime
,outtime datetime
)

select NUMS,attedance.* from (
...............
............
) as DA
left join ATTEDANCE on date=NUMS
order by 1 asc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources