SQL Teaser - Missing Dates 1

[ca8msm]

The Aim

To return all dates for a given person in a given month where they don't have a record in a "login" table.

Example Data

[COLOR=blue]DECLARE[/color] @LOGIN [COLOR=blue]TABLE[/color] (id [COLOR=blue]int[/color] [COLOR=blue]IDENTITY[/color](1,1), [COLOR=blue]Name[/color] [COLOR=blue]varchar[/color](20), LoginDate [COLOR=#FF00FF]datetime[/color] )

[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Joe'[/color], [COLOR=red]'20070701'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Joe'[/color], [COLOR=red]'20070710'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Joe'[/color], [COLOR=red]'20070720'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Fred'[/color], [COLOR=red]'20070702'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Fred'[/color], [COLOR=red]'20070707'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Fred'[/color], [COLOR=red]'20070711'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Fred'[/color], [COLOR=red]'20070718'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Fred'[/color], [COLOR=red]'20070723'[/color])
[COLOR=blue]INSERT[/color] @LOGIN [COLOR=blue]VALUES[/color] ([COLOR=red]'Fred'[/color], [COLOR=red]'20070725'[/color])

So, the results for 'Joe' in 'July 2007' would be:

Joe, 20070702
Joe, 20070703
Joe, 20070704
Joe, 20070705
Joe, 20070706
Joe, 20070707
Joe, 20070708
Joe, 20070709
Joe, 20070711
Joe, 20070712
Joe, 20070713
Joe, 20070714
Joe, 20070715
Joe, 20070716
Joe, 20070717
Joe, 20070718
Joe, 20070719
Joe, 20070721
Joe, 20070722
Joe, 20070723
Joe, 20070724
Joe, 20070725
Joe, 20070726
Joe, 20070727
Joe, 20070728
Joe, 20070729
Joe, 20070730
Joe, 20070731

The Rules
1) I must be able to use your query and provide a name and a month/year combination (so if data was entered for another person and/or month, only the parameters would change).
2) You can't use a "dates" tables

The Winner
The shortest code wins...


____________________________________________________________
Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]

Need help finding an answer? Try the Search Facility or read FAQ222-2244.

 

[SQLDenis]

>>And I didn't assume anything either, like you guys did.

what if you don't have permissions to the master database ;-)

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[AlexCuse]

mao

IMO, there should be a numbers table (up to at least 10,000) included in the model database. Then I wouldn't have to assume ;-)

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[ESquared]

Then use the local database instead of master
If not, include your script for your numbers table
Otherwise, do it in a loop (totallly inefficient of course):

[spoiler]--"Stored Procedure" setup
DECLARE
   @Name varchar(20),
   @LoginMonth datetime
SET @Name = 'Joe'
SET @LoginMonth = 'July 2007'

--Actual code
DECLARE @LOGNOTIN TABLE (Name varchar(20), LoginDate datetime )
DECLARE @i int
SET @LoginMonth = DateAdd(mm, DateDiff(mm, 0, @LoginMonth), 0)
SET @i = DateDiff(dd, @LoginMonth, DateAdd(mm, 1, @LoginMonth)) - 1
WHILE @i > -1 BEGIN
   INSERT @LOGNOTIN SELECT @Name, @LoginMonth + @i WHERE NOT EXISTS(SELECT * FROM @LOGIN WHERE Name = @Name AND LoginDate = @LoginMonth + @i)
   SET @i = @i - 1
END
SELECT * FROM @LOGNOTIN[/spoiler]

Actually Alex I like your idea about the model database

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[SQLDenis]

I was just being sarcastic
By the way why do you have an alarm clock?
I don't have one...not really true I have 3......kids
However I am up between 4 and 5AM, before them anyway

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

I have an alarm clock because on rare occasions my wife gets up early and takes the child in the other room and I have an extra 20 or 30 minutes to doze before I have to get up.

[COLOR=black #d0d0d0]My alarm clock causes time travel. When I hit snooze, no apparent time passes before the alarm sounds again, but the universe has in fact moved forward through time by ten minutes![/color]

 

[Otto]

Assuming SQL2005, just for fun .

declare @SDate datetime, @EDate datetime, @Joe varchar(20)
set @SDate='20070701'
set @EDate='20070731'
set @Joe='Joe'

with DATES (SD, ED) as (
  select @SDate, @EDate
  union all
  select SD+1, ED from DATES where SD<ED
)
select @Joe, DT.SD from DATES DT
 where not exists (select ID from @LOGIN LG where LG.NAME=@Joe and LG.LOGINDATE=DT.SD)