Friday, August 10, 2007

Summer SQL Teaser #12 Missing Dates

Stole this tease from my friend Mark.
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')


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 tomorrow
at

1 comment:

  1. Anonymous11:46 AM

    I found a solution that works for me in 1 SQL select statement ( not counting the @login table). I am curious to see your solution. This is not written for legibility or ease of use. ;)

    SET NOCOUNT ON
    if object_id('numbers') is null
    begin
    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
    end
    GO
    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')

    --173 characters below:
    select i.n,i.c from(select 'Joe'n,dateadd(d,num,'20070701')c from numbers)i
    left join @login l on i.c=l.LoginDate and l.name=i.n
    where i.c <'20070801' and l.LoginDate is null

    ReplyDelete