September 4, 2011 at 12:49 pm
Sample here populates a table for all of August 2011, incremented in 30 minute intervals:
declare @IntervalTable table
(
Interval smalldatetime primary key
)
declare @FirstInterval smalldatetime
declare @LastInterval smalldatetime
declare @ThisInterval smalldatetime
select @FirstInterval = '08/01/2011 00:00'
select @LastInterval = '08/31/2011 23:30'
select @ThisInterval = @FirstInterval
while @ThisInterval <= @LastInterval
begin
insert @IntervalTable(Interval)
values(@ThisInterval)
select @ThisInterval = DATEADD(MINUTE, 30, @ThisInterval)
end
I want to find the previous Saturday:
--find Saturday of previous weekend
declare @Today smalldatetime
select @Today = '08/31/2011' --(Wed 31)
--return '08/27/2011 00:00' (Sat 27)
select @Today = '08/28/2011' --Sun 28
--return '08/27/2011 00:00' (Sat 27)
select @Today = '08/27/2011' --Sat 27
--return '08/20/2011 00:00' (Sat 20)
And Sunday:
--find Sunday of previous weekend
declare @Today smalldatetime
select @Today = '08/31/2011' --(Wed 31)
--return '08/28/2011 00:00' (Sun 28)
select @Today = '08/28/2011' --Sun 28
--return '08/28/2011 00:00' (Sun 28)
select @Today = '08/27/2011' --Sat 27
--return '08/21/2011 00:00' (Sun 21)
If today is a Sunday, need to find the previous Saturday (i.e. the day before) and this Sunday (i.e. today), not the previous one. Basically, I'm looking for the previous whole weekend, or this weekend if it's a Sunday - if this makes sense...
I'm on SQL Server 2005.
September 4, 2011 at 1:23 pm
IMO a calendar table is pretty much essential
September 5, 2011 at 2:36 am
Thanks. I presume it can be done without a calendar table, but on looking at the link provided I can see the advantages of it as I have to do this sort of thing in a number of places.
These 30 minute time slots are important (in some cases, not this one) and need to be identified as, for example, peak and off-peak and depend on the customer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply