Split out a table by date?

Question

Split out a table by date?

aaa121

SSCertifiable

Points: 5902
More actions
January 26, 2010 at 3:56 am

#215914

I have a table where each row has a start and end date.
What is the best way to split this out in a query so for each day between the start and end date I have identical row?

Viewing 8 posts - 1 through 7 (of 7 total)

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

dave.repton SSCrazy Points: 2468 More actions · Answer 1

I would look to join it to a date table - if you don;t already have one the below would create one on the fly for you...

declare @Dates table (Dt smalldatetime)

declare @StartDate datetime

declare @Days int

set @StartDate = '20090101'

set @Days = 365

while @Days > 0

begin

set @Days = @Days -1

insert @Dates values (dateadd(dd, @Days, @StartDate))

end

select * from @Dates order by Dt

aaa121 SSCertifiable Points: 5902 More actions · Answer 2

Thanks Dave,

I have a date table and this is what I was looking at. But my main issue is to break it out by day, do I use PIVOT perhaps?

I think there is an easier way but speed of retrieval is the number 1 priority due to the amount of records.

dave.repton SSCrazy Points: 2468 More actions · Answer 3

would be something along the lines of

select a.StartDate, b.EndDate

from TableSource a

inner join DateTable b

on b.Dt between a.StartDate and a.EndDtae

dave.repton SSCrazy Points: 2468 More actions · Answer 4

A more complete example.....

declare @Dates table (Dt smalldatetime)

declare @StartDate datetime

declare @Days int

set @StartDate = '20090101'

set @Days = 365

while @Days > 0

begin

set @Days = @Days -1

insert @Dates values (dateadd(dd, @Days, @StartDate))

end

declare @test-2 table(sd smalldatetime, ed smalldatetime)

insert @test-2 values('20090919', '20090925')

insert @test-2 values('20090405', '20090425')

select a.sd, a.ed

from @test-2 a

inner join @Dates b

on b.dt between a.sd and a.ed

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

-- Make some sample data

DROP TABLE #TableWithStartAndEndDate

CREATE TABLE #TableWithStartAndEndDate (TiD INT, StartDate DATETIME, EndDate DATETIME)

INSERT INTO #TableWithStartAndEndDate (TiD, StartDate, EndDate)

SELECT 1, '2010-01-18 01:00:00.000', '2010-01-25 23:00:00.000' UNION ALL

SELECT 2, '2010-01-18 02:00:00.000', '2010-01-20 22:00:00.000' UNION ALL

SELECT 3, '2010-01-18 03:00:00.000', '2010-01-19 21:00:00.000'

-- run a query against it

SELECT t.TiD, t.StartDate, t.EndDate, [GeneratedDate] = CAST(StartDate + (n.RowID-1) AS DATE)

FROM #TableWithStartAndEndDate t

CROSS APPLY

(SELECT

TOP (DATEDIFF(dd, t.StartDate, t.EndDate)+1) RowID = ROW_NUMBER() OVER (ORDER BY [Name])

FROM master.dbo.syscolumns) n

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

aaa121 SSCertifiable Points: 5902 More actions · Answer 6

Thanks both,

I have looked at both solutions.

Dave - I have never used the ON statement with a BETWEEN before.

Chris - Very interesting solution thanks

aaa121 SSCertifiable Points: 5902 More actions · Answer 7

I am going to carry out some peformance testing on each one and see which one works the best