Post reply

Help with select statement & date columns

  • April 3, 2006 at 10:55 am

    #116856

    Hi,

    I have this events table.

    create table events

    (startdate datetime, enddate datetime, name varchar(100), location varchar(50))

    insert events select '04/10/2006', '04/12/2006', 'ABC', 'New York'

    insert events select '04/22/2006', '04/22/2006', 'XYZ', 'Los Angeles'

    insert events select '04/26/2006', '04/27/2006', 'MNOP', 'Chicago'

    I want to select from this table, but with one row for each date between startdate and enddate. Like this,

    04/10/2006, ABC, New York

    04/11/2006, ABC, New York

    04/12/2006, ABC, New York

    04/22/2006, XYZ, Los Angeles

    04/26/2006, MNOP, Chicago

    04/27/2006, MNOP, Chicago

    Is there a way to do this in a single select statement? Or do I have to use cursors?

    Thanks.

  • April 3, 2006 at 11:02 am

    #630480

    One possible way to perform this would be to have another table with all possible dates in it, and join to this table.

    ie....

    Select B.Date, A.name, A.location from

    events A inner join Dates B on b.date between A.startdate and A.enddate

     

  • April 3, 2006 at 11:23 am

    #630486

    Instead of a table with all possible dates, can I create a UDF that takes startdate and enddate, and return all the in-between dates and insert this into a table variable inside the sproc. And use this table in the above example query instead? Will this be less efficient?

    Thanks.

  • April 3, 2006 at 12:42 pm

    #630508

    Senthil - if Ryan was online you'd get your solution in a jiffy but since he's not around I'll post his (genius) t-sql...be sure to thank him...

    --numbers table
declare @Numbers table (i int identity(0, 1), j bit)
insert @Numbers select top 10 null from master.dbo.syscolumns a, master.dbo.syscolumns b


--calculation
select convert(varchar, dateadd(d, i, startdate), 101) as Date, Name, location
from events, @Numbers
where datediff(d, startdate, enddate) >= i
order by Name, Date

    Here's the post where the exact same solution was sought - you both don't work for the same company do you ?!







    **ASCII stupid question, get a stupid ANSI !!!**

  • April 3, 2006 at 12:48 pm

    #630510

    That's slick....create a table variable of identities and create a date from them........wouldn't need updating later either......kudos to Ryan

  • April 3, 2006 at 2:37 pm

    #630526

    Thanks much.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply