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.

 

[AlexCuse]

I don't know that this will win any shortest code competitions, but twas a fun way to start my morning

[spoiler coloring doesnt work w/this]declare @s datetime
declare @n varchar(1000)

set @s = '20070701'
set @n = 'Fred'

select @n Name, x.*from
(
select dateadd(d,N,@s)nD
from Num where N<=31
)x
left join @LOGIN a
on x.nD=a.LoginDate
and a.Name=@n
where a.LoginDate is null
and month(x.nD)=month(@s)[/spoiler]

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

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

Nice one Alex


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

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

 

[SQLDenis]

assuming you have a numbers table from 0 to 8000
where num is the column name and numbers is the table name then I would do this

declare @s datetime
declare @n varchar(50)
select @s = '20070701',@n ='Joe'

select @n + ', ' + convert(varchar,d,112) from @LOGIN l
right join(
select dateadd(dd,num,@s) as d
from numbers
where dateadd(dd,num,@s) >= @s and  dateadd(dd,num,@s) < dateadd(m,1,@s) ) x 
on l.LoginDate  = x.d
and l.name =@n
where l.name is null

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

 

[SQLDenis]

oops forgot the spoiler tag

Also Alex what happens when you run that code in February or a month that has less than 31 days?

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

 

[SQLDenis]

Never mind I missed this part

and month(x.nD)=month(@s)

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

 

[AlexCuse]

Denis - I think you can get rid of the first where clause on Num, it is kind of a given (unless you also have negative nums in your table ;-) )

Spoiler

declare @s datetime
declare @n varchar(1000)

set @s = '20070701'
set @n = 'Fred'

select @n Name, x.*from
(
select dateadd(d,N,@s)nD
from Num where dateadd(d,N,@s)<dateadd(m,1,@s)
)x
left join @LOGIN a
on x.nD=a.LoginDate
and a.Name=@n
where a.LoginDate is null

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

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

I wasn't so much bothered by the formatting of the results (which I should have said ) so I would have been happy with this:

select @n, d from @LOGIN l

rather than:

select @n + ', ' + convert(varchar,d,112) from @LOGIN l

if you wanted to shorten yours Denis. Another good result though




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

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

 

[SQLDenis]

down to 247

Spoiler

declare @s datetime,@n varchar(5)
select @s='20070701',@n ='Joe'
select @n,x.d
from @LOGIN l
right join(
select dateadd(dd,num,@s)d
from numbers
where dateadd(d,num,@s)<dateadd(m,1,@s))x
on l.LoginDate=x.d
and l.name=@n
where l.name is null



select len('declare @s datetime,@n varchar(5)
select @s=''20070701'',@n =''Joe''
select @n,x.d
from @LOGIN l
right join(
select dateadd(dd,num,@s)d
from numbers
where dateadd(d,num,@s)<dateadd(m,1,@s))x
on l.LoginDate=x.d
and l.name=@n
where l.name is null') -4--for the double quotes

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

 

[SQLDenis]

and now 242 with very fancy naming tricks

Spoiler

declare @ datetime,@n varchar(5)
select @='20070701',@n ='Joe'
select @n,x.d
from @LOGIN l
right join(
select dateadd(dd,num,@)d
from numbers
where dateadd(d,num,@)<dateadd(m,1,@))x
on l.LoginDate=x.d
and l.name=@n
where l.name is null



select len('declare @ datetime,@n varchar(5)
select @=''20070701'',@n =''Joe''
select @n,x.d
from @LOGIN l
right join(
select dateadd(dd,num,@)d
from numbers
where dateadd(d,num,@)<dateadd(m,1,@))x
on l.LoginDate=x.d
and l.name=@n
where l.name is null') -4--for the double quotes

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

 

[AlexCuse]

Hey denis you missed a simple trick to shorten the length. Shame on you!

[spoiler I hate the empty variable, but 233]declare @ datetime
declare @n varchar(1000)
set @ = '20070701'
set @n = 'Fred'
select @n,x.*from
(select @+n n
from Num
where @+n<dateadd(m,1,@)
)x left join @LOGIN a
on x.n=a.LoginDate
and a.Name=@n
where a.LoginDate is null
[/spoiler]

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

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

[spoiler whoops 216]declare @ datetime,@n varchar(99)
select @='20070701',@n='Fred'
select @n,x.*from
(select @+n n
from Num
where @+n<dateadd(m,1,@)
)x left join @LOGIN a
on x.n=a.LoginDate
and a.Name=@n
where a.LoginDate is null[/spoiler]

I'm done now. For now ;-)

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

Ignorance of certain subjects is a great part of wisdom

 

[ca8msm]

And since you have unique column names Alex, you could take off your aliases...

Spoiler

declare @ datetime,@n varchar(9)
select @='20070701',@n='Fred'
select @n,x.*from
(select @+n n
from @Num
where @+n<dateadd(m,1,@)
)x left join @LOGIN
on n=LoginDate
and Name=@n
where LoginDate is null

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

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

 

[SQLDenis]

okay 213 now, I also made the right join into a left join (hey saves 1 character)

[spoiler 213.....no really]declare @ datetime,@n varchar(5)
select @='20070701',@n ='Joe'
select @n,d
from (select @+num d
from numbers
where @+num <dateadd(m,1,@))x
left join @LOGIN l
on l.LoginDate=x.d
and name=@n
where name is null



select len('declare @ datetime,@n varchar(5)
select @=''20070701'',@n =''Joe''
select @n,d
from (select @+num d
from numbers
where @+num <dateadd(m,1,@))x
left join @LOGIN l
on l.LoginDate=x.d
and name=@n
where name is null') -4--for the double quotes
[/spoiler]



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

 

[SQLDenis]

Okay stealing borrowing some more ideas

[spoiler Look ma 207]

declare @ datetime,@n varchar(5)
select @='20070701',@n ='Joe'
select @n,d
from (select @+num d
from numbers
where @+num <dateadd(m,1,@))x
left join @LOGIN
on LoginDate=d
and name=@n
where name is null



select len('declare @ datetime,@n varchar(5)
select @=''20070701'',@n =''Joe''
select @n,d
from (select @+num d
from numbers
where @+num <dateadd(m,1,@))x
left join @LOGIN
on LoginDate=d
and name=@n
where name is null') -4--for the double quotes
[/spoiler]

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

 

[sunila7]

With CTE

[Spolier]
declare @s datetime
declare @n varchar(50)
SELECT @s = '20070701',@n ='Joe';

WITH DateTable(Dates) AS
(
SELECT DATEADD(dd, Numbers, @s) Dates
FROM
(SELECT (col * 10 + col1 ) Numbers
FROM
(SELECT (0) col
UNION ALL
SELECT (1)
UNION ALL
SELECT (2)
UNION ALL
SELECT (3) ) TBL
CROSS JOIN
(SELECT (0) col1
UNION ALL
SELECT (1)
UNION ALL
SELECT (2) col1
UNION ALL
SELECT (3)
UNION ALL
SELECT (4)
UNION ALL
SELECT (5)
UNION ALL
SELECT (6)
UNION ALL
SELECT (7)
UNION ALL
SELECT (8)
UNION ALL
SELECT (9) ) TBL2 ) TBL
)


SELECT @n,dates FROM DateTable
LEFT JOIN @login l ON l.logindate = DateTable.Dates and l.name =@n
WHERE MONTH(@S) = MONTH(DateTable.Dates) and logindate is null


[/Spoiler]

Sunil

 

[AlexCuse]

[spoiler 195... Just to mess with Denis]declare @ datetime,@n varchar(9);select @='20070701',@n='Fred';select @n,x.*from(select @+n n from Num where @+n<dateadd(m,1,@))x left join @LOGIN a on n=LoginDate and Name=@n where LoginDate is null[/spoiler]

hopefully it doesn't render the thread unreadable!

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

Ignorance of certain subjects is a great part of wisdom

 

[sunila7]

Tried hard but cannot spell Spoiler right ..

Sunil

 

[ESquared]

Spoiler

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

--Actual code
SET @LoginMonth = DateAdd(mm, DateDiff(mm, 0, @LoginMonth), 0)
SELECT
Name = @Name,
NotLoggedInDate = @LoginMonth + C.colorder - 1
FROM
master.dbo.syscolumns C
LEFT JOIN @LOGIN L ON @LoginMonth + C.colorder - 1 = L.LoginDate AND L.Name = @Name
WHERE
C.id = object_id('syscolumns') and colorder <= DateDiff(dd, @LoginMonth, DateAdd(mm, 1, @LoginMonth))
AND L.Name IS NULL

--hey, I didn't use a dates table. This code works on SQL 2000 and SQL 2005.

[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]

 

[ESquared]

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

[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]