THE SQL Server Blog Spot on the Web
Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | Join | Help
in Search
Home Blogs Forums Roller Downloads   Opml View as RSS news feed in XML

Denis Gobo

Summer SQL Teaser #11 Missing Dates

The teaser for this week is not really a teaser, this time you will have to write some code instead of guessing/knowing.
First create this table of numbers

SET NOCOUNT ON

CREATE TABLE numbers(num int primary key)

DECLARE @l int

SELECT @l =0

WHILE @l <= 1000

BEGIN

INSERT numbers VALUES(@l)

SET @l = @l + 1

END

GO

Below is a table, return all the dates for July 2007 where Joe does not have a LoginDate entry. So in this case the output is 28 rows

DECLARE @LOGIN TABLE (id int IDENTITY(1,1), Name varchar(20), LoginDate datetime )

INSERT @LOGIN VALUES ('Joe', '20070701')

INSERT @LOGIN VALUES ('Joe', '20070710')

INSERT @LOGIN VALUES ('Joe', '20070720')

INSERT @LOGIN VALUES ('Fred', '20070702')

INSERT @LOGIN VALUES ('Fred', '20070707')

INSERT @LOGIN VALUES ('Fred', '20070711')

INSERT @LOGIN VALUES ('Fred', '20070718')

INSERT @LOGIN VALUES ('Fred', '20070723')

INSERT @LOGIN VALUES ('Fred', '20070725')

 Expected output

Joe 2007-07-02 00:00:00.000
Joe 2007-07-03 00:00:00.000
Joe 2007-07-04 00:00:00.000
Joe 2007-07-05 00:00:00.000
Joe 2007-07-06 00:00:00.000
Joe 2007-07-07 00:00:00.000
Joe 2007-07-08 00:00:00.000
Joe 2007-07-09 00:00:00.000
Joe 2007-07-11 00:00:00.000
Joe 2007-07-12 00:00:00.000
Joe 2007-07-13 00:00:00.000
Joe 2007-07-14 00:00:00.000
Joe 2007-07-15 00:00:00.000
Joe 2007-07-16 00:00:00.000
Joe 2007-07-17 00:00:00.000
Joe 2007-07-18 00:00:00.000
Joe 2007-07-19 00:00:00.000
Joe 2007-07-21 00:00:00.000
Joe 2007-07-22 00:00:00.000
Joe 2007-07-23 00:00:00.000
Joe 2007-07-24 00:00:00.000
Joe 2007-07-25 00:00:00.000
Joe 2007-07-26 00:00:00.000
Joe 2007-07-27 00:00:00.000
Joe 2007-07-28 00:00:00.000
Joe 2007-07-29 00:00:00.000
Joe 2007-07-30 00:00:00.000
Joe 2007-07-31 00:00:00.000

Your task is to write code that will return that resultset.
1) you have to use the number table I provided
2) you cannot use a calendar table

Shortest code wins, I got mine down to 207 characters (and no, it is not all in 1 line!)

I will post my code at around 2PM EST

 


Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!
Published Friday, August 10, 2007 10:03 AM by Denis Gobo
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Luciano Evaristo Guerche said:

I've run the SQL Statement below and it returned only 22. As July has 31 days and the login table has 9 entries (31 - 9), I guess 22 is ok. How did you get the 28?

SELECT DATEADD(day, num, '20070701')

FROM numbers

WHERE DATEADD(day, num, '20070701') BETWEEN '20070701' AND '20070731' AND

     NOT EXISTS(

                SELECT LoginDate

                FROM @LOGIN AS Login

                WHERE Login.LoginDate = DATEADD(day, numbers.num, '20070701')

               )

------------------------------------------------------

2007-07-03 00:00:00.000

2007-07-04 00:00:00.000

2007-07-05 00:00:00.000

2007-07-06 00:00:00.000

2007-07-08 00:00:00.000

2007-07-09 00:00:00.000

2007-07-12 00:00:00.000

2007-07-13 00:00:00.000

2007-07-14 00:00:00.000

2007-07-15 00:00:00.000

2007-07-16 00:00:00.000

2007-07-17 00:00:00.000

2007-07-19 00:00:00.000

2007-07-21 00:00:00.000

2007-07-22 00:00:00.000

2007-07-24 00:00:00.000

2007-07-26 00:00:00.000

2007-07-27 00:00:00.000

2007-07-28 00:00:00.000

2007-07-29 00:00:00.000

2007-07-30 00:00:00.000

2007-07-31 00:00:00.000

August 10, 2007 11:25 AM
 

Luciano Evaristo Guerche said:

The SQL Statement below is 209 characters, but still returns only 22 records.

SELECT DATEADD(d, num, '20070701')

FROM numbers

 LEFT JOIN

    @LOGIN

    ON DATEADD(d, num, '20070701') = LoginDate

WHERE DATEADD(d, num, '20070701') BETWEEN '20070701' AND '20070731' AND

     LoginDate IS NULL

August 10, 2007 11:32 AM
 

Valerie said:

Here's my entry.

Logic by Matt, SQL by me :)

SELECT 'Joe', num + CAST('20070701' AS datetime)

FROM numbers

WHERE num + CAST('20070701' AS datetime) NOT IN (SELECT LoginDate FROM @LOGIN WHERE Name = 'Joe')

AND num < 31

Results:

---- -----------------------

Joe  2007-07-02 00:00:00.000

Joe  2007-07-03 00:00:00.000

Joe  2007-07-04 00:00:00.000

Joe  2007-07-05 00:00:00.000

Joe  2007-07-06 00:00:00.000

Joe  2007-07-07 00:00:00.000

Joe  2007-07-08 00:00:00.000

Joe  2007-07-09 00:00:00.000

Joe  2007-07-11 00:00:00.000

Joe  2007-07-12 00:00:00.000

Joe  2007-07-13 00:00:00.000

Joe  2007-07-14 00:00:00.000

Joe  2007-07-15 00:00:00.000

Joe  2007-07-16 00:00:00.000

Joe  2007-07-17 00:00:00.000

Joe  2007-07-18 00:00:00.000

Joe  2007-07-19 00:00:00.000

Joe  2007-07-21 00:00:00.000

Joe  2007-07-22 00:00:00.000

Joe  2007-07-23 00:00:00.000

Joe  2007-07-24 00:00:00.000

Joe  2007-07-25 00:00:00.000

Joe  2007-07-26 00:00:00.000

Joe  2007-07-27 00:00:00.000

Joe  2007-07-28 00:00:00.000

Joe  2007-07-29 00:00:00.000

Joe  2007-07-30 00:00:00.000

Joe  2007-07-31 00:00:00.000

August 10, 2007 11:33 AM
 

Denis Gobo said:

Here is my version

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

August 10, 2007 11:39 AM
 

Denis Gobo said:

Luciano,

we are only looking for Joe not Fred

August 10, 2007 11:40 AM
 

Luciano Evaristo Guerche said:

That's it, haven't figured out all entries weren't for Joe. Add the extra filter and tweaked my SQL Statement so that it became shorter. Now it returns 28 rows and is only 171 characters. To achieve such size, I have to given up 1) prefix fields with table name or alias; 2) identation and extra break lines.

SELECT DATEADD(d, num, '20070701')

FROM numbers LEFT JOIN @LOGIN ON DATEADD(d, num, '20070701') = LoginDate AND 'Joe' = Name

WHERE num BETWEEN 0 AND 30 AND LoginDate IS NULL

--

Luciano Evaristo Guerche

Taboao da Serra, SP, Brazil

August 10, 2007 12:32 PM
 

Denis Gobo said:

Nice,

I made mine flexible enough that it will work for months with 28,29,30 and 31 dates  ;-)

But thene again I did not mention that so it doesn't matter  ;-)

August 10, 2007 12:37 PM
 

Luciano Evaristo Guerche said:

Figured out BETWEEN is very verbose, so tweaked mine statement again and it turned 159 characters

SELECT DATEADD(d, num, '20070701')

FROM numbers LEFT JOIN @LOGIN ON DATEADD(d, num, '20070701') = LoginDate AND 'Joe' = Name

WHERE num < 31 AND LoginDate IS NULL

--

Luciano Evaristo Guerche

Taboao da Serra, SP, Brazil

August 10, 2007 12:42 PM
 

Luciano Evaristo Guerche said:

Took out extra spaces between operators and commas and it turned 149 characters wide.

SELECT DATEADD(d,num,'20070701')

FROM numbers LEFT JOIN @LOGIN ON DATEADD(d,num,'20070701')=LoginDate AND 'Joe'=Name

WHERE num<31 AND LoginDate IS NULL

--

Luciano Evaristo Guerche

Taboao da Serra, SP, Brazil

August 10, 2007 12:47 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit

About Denis Gobo

I was born in Croatia in 1970, when I was one I moved to Amsterdam (and yes Ajax is THE team in Holland) and finally in 1993 I came to the US. I have lived in New York City for a bunch of years and currently live in Princeton, New Jersey with my wife and 3 kids. I work for Dow Jones as a Database architect in the indexes department, one drawback: since our data goes back all the way to May 1896 I cannot use smalldates ;-( I have been working with SQL server since version 6.5 and compared to all the other bloggers here I am a n00b. Some of you might know me from http://sqlservercode.blogspot.com/ or even from some of the newsgroups where I go by the name Denis the SQL Menace If you are a Tek-Tips user then you might know me by the name SQLDenis, I am one of the guys answering SQL Questions in the SQL Programming forum.

This Blog

Syndication
©2006-2008 SQLblog.comTM
Brought to you by Peter DeBetta & Adam Machanic
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement