|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Returning specific weekdaysMy brain is fried!!!
I am trying to use a calendar table as per http://www.aspfaq.com/2519 and I am trying to return only dates between Thursday and Wednesday for the current period. Hence if today is Wednesday the select statement would return dates from last Thursday up until and including today. >From tomorrow (i.e. Thursday) it would return dates from tomorrow up until and including next Thursday.I am just learning how to use IF and CASE statements and just can't seem to be able to nail this... Any help would be greatly appreciated. Regards, Ric I am not able to understand this
>From tomorrow (i.e. Thursday) it would return dates from tomorrow up until and including next Thursday.Why should it include next thursday? Can you give the logic behind this? Oops, sorry! It should always return a range from Thursday to the
following Wednesday, where the current day is in the range. On Wednesdays, it should return the range from last Thursday up until and including the Wednesday. I did say that my brain was fried!!! ;-) Ric Omnibuzz wrote: Show quote > I am not able to understand this > >From tomorrow (i.e. Thursday) it would return dates from tomorrow up > until and including next Thursday. > > Why should it include next thursday? > > Can you give the logic behind this? > -- > -Omnibuzz (The SQL GC) > > http://omnibuzz-sql.blogspot.com/ ric
As an alternative you can try CREATE FUNCTION fn_dates(@from AS DATETIME, @to AS DATETIME) RETURNS @Dates TABLE(dt DATETIME NOT NULL PRIMARY KEY) AS BEGIN DECLARE @rc AS INT SET @rc = 1 INSERT INTO @Dates VALUES(@from) WHILE @from + @rc * 2 - 1 <= @to BEGIN INSERT INTO @Dates SELECT dt + @rc FROM @Dates SET @rc = @rc * 2 END INSERT INTO @Dates SELECT dt + @rc FROM @Dates WHERE dt + @rc <= @to RETURN END GO --Usage DECLARE @dtfrom CHAR(8),@dtto CHAR(8) SET @dtfrom=CONVERT(CHAR(8),GETDATE(),112)-6 SET @dtto =CONVERT(CHAR(8),GETDATE(),112) SELECT dt FROM fn_dates(@dtfrom, @dtto) Show quote "ric_deez" <ricardo.da***@gmail.com> wrote in message news:1156336150.565204.219750@i3g2000cwc.googlegroups.com... > My brain is fried!!! > > I am trying to use a calendar table as per http://www.aspfaq.com/2519 > and I am trying to return only dates between Thursday and Wednesday for > the current period. Hence if today is Wednesday the select statement > would return dates from last Thursday up until and including today. >>From tomorrow (i.e. Thursday) it would return dates from tomorrow up > until and including next Thursday. > > I am just learning how to use IF and CASE statements and just can't > seem to be able to nail this... > > Any help would be greatly appreciated. > > Regards, > > Ric > > I am trying to use a calendar table as per http://www.aspfaq.com/2519 There are many options to the calendar table, and one is to specify a custom > and I am trying to return only dates between Thursday and Wednesday for > the current period. Hence if today is Wednesday the select statement > would return dates from last Thursday up until and including today. >>From tomorrow (i.e. Thursday) it would return dates from tomorrow up > until and including next Thursday. week number, another is to specify a custom numeric value for each day of the week (e.g. you could have weekday = 1 for Thursday, 2 for Friday, etc.). Did you do either of those things? Can you show two weeks of data for the calendar table? A Hi Aaron,
I did include a field in the calendar table called TDW (Timesheet Day of Week) which corresponds to 1 for Thursday and 7 for Wednesday. I want to be able to call the udf without passing any parameters and for it to return the current applicable range of dates. Ric Aaron Bertrand [SQL Server MVP] wrote: Show quote > > I am trying to use a calendar table as per http://www.aspfaq.com/2519 > > and I am trying to return only dates between Thursday and Wednesday for > > the current period. Hence if today is Wednesday the select statement > > would return dates from last Thursday up until and including today. > >>From tomorrow (i.e. Thursday) it would return dates from tomorrow up > > until and including next Thursday. > > There are many options to the calendar table, and one is to specify a custom > week number, another is to specify a custom numeric value for each day of > the week (e.g. you could have weekday = 1 for Thursday, 2 for Friday, etc.). > Did you do either of those things? Can you show two weeks of data for the > calendar table? > > A > I did include a field in the calendar table called TDW (Timesheet Day You can't use GETDATE() or CURRENT_TIMESTAMP within the UDF (look up > of Week) which corresponds to 1 for Thursday and 7 for Wednesday. I > want to be able to call the udf without passing any parameters and for > it to return the current applicable range of dates. non-deterministic functions), so you'll need to pass that in. USE tempdb; GO CREATE TABLE dbo.CalendarTable ( [Date] SMALLDATETIME PRIMARY KEY, TDW TINYINT ); GO SET NOCOUNT ON; GO INSERT dbo.CalendarTable SELECT '20060728', 2 UNION SELECT '20060729', 3 UNION SELECT '20060730', 4 UNION SELECT '20060731', 5 UNION SELECT '20060801', 6 UNION SELECT '20060802', 7 UNION SELECT '20060803', 1 UNION SELECT '20060804', 2 UNION SELECT '20060805', 3 UNION SELECT '20060806', 4 UNION SELECT '20060807', 5 UNION SELECT '20060808', 6 UNION SELECT '20060809', 7 UNION SELECT '20060810', 1 UNION SELECT '20060811', 2 UNION SELECT '20060812', 3 UNION SELECT '20060813', 4 UNION SELECT '20060814', 5 UNION SELECT '20060815', 6 UNION SELECT '20060816', 7 UNION SELECT '20060817', 1 UNION SELECT '20060818', 2 UNION SELECT '20060819', 3 UNION SELECT '20060820', 4 UNION SELECT '20060821', 5 UNION SELECT '20060822', 6 UNION SELECT '20060823', 7 UNION SELECT '20060824', 1 UNION SELECT '20060825', 2 UNION SELECT '20060826', 3 UNION SELECT '20060827', 4 UNION SELECT '20060828', 5 UNION SELECT '20060829', 6 UNION SELECT '20060830', 7 UNION SELECT '20060901', 1 UNION SELECT '20060902', 2 UNION SELECT '20060903', 3; GO CREATE FUNCTION dbo.CurrentPeriod ( @today SMALLDATETIME ) RETURNS TABLE AS RETURN ( SELECT [Date] FROM dbo.CalendarTable WHERE [Date] >= ( SELECT MAX([Date]) FROM dbo.CalendarTable WHERE TDW = 1 AND [Date] <= @today ) AND [Date] < ( SELECT MIN([Date]) FROM dbo.CalendarTable WHERE TDW = 1 AND [Date] > @today ) ); GO DECLARE @dt SMALLDATETIME; SET @dt = CURRENT_TIMESTAMP; -- for today (wednesday) SELECT [Date] FROM dbo.CurrentPeriod(@dt) ORDER BY [Date]; -- for tomorrow (thursday) SET @dt = @dt + 1; SELECT [Date] FROM dbo.CurrentPeriod(@dt) ORDER BY [Date]; GO DROP FUNCTION dbo.CurrentPeriod; DROP TABLE dbo.CalendarTable; GO Hi Aaron,
That worked a treat!!! Thank you so much for all your assistance today!!! Ric Aaron Bertrand [SQL Server MVP] wrote: Show quote > > I did include a field in the calendar table called TDW (Timesheet Day > > of Week) which corresponds to 1 for Thursday and 7 for Wednesday. I > > want to be able to call the udf without passing any parameters and for > > it to return the current applicable range of dates. > > You can't use GETDATE() or CURRENT_TIMESTAMP within the UDF (look up > non-deterministic functions), so you'll need to pass that in. > > > USE tempdb; > GO > > CREATE TABLE dbo.CalendarTable > ( > [Date] SMALLDATETIME PRIMARY KEY, > TDW TINYINT > ); > GO > > SET NOCOUNT ON; > GO > > INSERT dbo.CalendarTable > SELECT '20060728', 2 > UNION SELECT '20060729', 3 > UNION SELECT '20060730', 4 > UNION SELECT '20060731', 5 > UNION SELECT '20060801', 6 > UNION SELECT '20060802', 7 > UNION SELECT '20060803', 1 > UNION SELECT '20060804', 2 > UNION SELECT '20060805', 3 > UNION SELECT '20060806', 4 > UNION SELECT '20060807', 5 > UNION SELECT '20060808', 6 > UNION SELECT '20060809', 7 > UNION SELECT '20060810', 1 > UNION SELECT '20060811', 2 > UNION SELECT '20060812', 3 > UNION SELECT '20060813', 4 > UNION SELECT '20060814', 5 > UNION SELECT '20060815', 6 > UNION SELECT '20060816', 7 > UNION SELECT '20060817', 1 > UNION SELECT '20060818', 2 > UNION SELECT '20060819', 3 > UNION SELECT '20060820', 4 > UNION SELECT '20060821', 5 > UNION SELECT '20060822', 6 > UNION SELECT '20060823', 7 > UNION SELECT '20060824', 1 > UNION SELECT '20060825', 2 > UNION SELECT '20060826', 3 > UNION SELECT '20060827', 4 > UNION SELECT '20060828', 5 > UNION SELECT '20060829', 6 > UNION SELECT '20060830', 7 > UNION SELECT '20060901', 1 > UNION SELECT '20060902', 2 > UNION SELECT '20060903', 3; > GO > > > CREATE FUNCTION dbo.CurrentPeriod > ( > @today SMALLDATETIME > ) > RETURNS TABLE > AS > RETURN > ( > SELECT > [Date] > FROM > dbo.CalendarTable > WHERE > [Date] >= ( > SELECT MAX([Date]) > FROM dbo.CalendarTable > WHERE TDW = 1 > AND [Date] <= @today > ) > AND > [Date] < ( > SELECT MIN([Date]) > FROM dbo.CalendarTable > WHERE TDW = 1 > AND [Date] > @today > ) > ); > GO > > DECLARE @dt SMALLDATETIME; > SET @dt = CURRENT_TIMESTAMP; > > -- for today (wednesday) > SELECT [Date] FROM dbo.CurrentPeriod(@dt) ORDER BY [Date]; > > -- for tomorrow (thursday) > SET @dt = @dt + 1; > SELECT [Date] FROM dbo.CurrentPeriod(@dt) ORDER BY [Date]; > GO > > DROP FUNCTION dbo.CurrentPeriod; > DROP TABLE dbo.CalendarTable; > GO ric_deez wrote:
Show quote > My brain is fried!!! So you're effectively time-shifting a week to run from > > I am trying to use a calendar table as per http://www.aspfaq.com/2519 > and I am trying to return only dates between Thursday and Wednesday for > the current period. Hence if today is Wednesday the select statement > would return dates from last Thursday up until and including today. >>From tomorrow (i.e. Thursday) it would return dates from tomorrow up > until and including next Thursday. > > I am just learning how to use IF and CASE statements and just can't > seem to be able to nail this... > > Any help would be greatly appreciated. > > Regards, > > Ric > Thursday-Wednesday instead of Sunday-Saturday. Add an INT column to the calendar table, call it "Period". Populate this column with the WeekNum of the current date plus 3 days. Without the calendar table, you can do it like this:
DECLARE @today SMALLDATETIME, @Thursday SMALLDATETIME, @dp INT; -- get today without any time -- and determine today's day of week: SELECT -- hardcode @today = '20060824' -- e.g. to test tomorrow's result @today = DATEDIFF(DAY, 0, CURRENT_TIMESTAMP), @dp = DATEPART(DW, @today);; -- calculate the appropriate Thursday: -- if it is Thursday, we need today, else -- we need the previous Thursday SELECT @Thursday = @today - CASE WHEN @dp IN (5,6) THEN (@dp-5)*7 ELSE @dp+2 END; -- here is our custom work week range for -- the current Thursday - Wednesday period: SELECT @Thursday, @Thursday + 6; /* -- presumably to use in a real query somewhere SELECT [DateColumn] FROM [SomeTable] WHERE [DateColumn] >= @Thursday AND [DateColumn] <= @Thursday + 6; */ Show quote "ric_deez" <ricardo.da***@gmail.com> wrote in message news:1156336150.565204.219750@i3g2000cwc.googlegroups.com... > My brain is fried!!! > > I am trying to use a calendar table as per http://www.aspfaq.com/2519 > and I am trying to return only dates between Thursday and Wednesday for > the current period. Hence if today is Wednesday the select statement > would return dates from last Thursday up until and including today. >>From tomorrow (i.e. Thursday) it would return dates from tomorrow up > until and including next Thursday. > > I am just learning how to use IF and CASE statements and just can't > seem to be able to nail this... > > Any help would be greatly appreciated. > > Regards, > > Ric >  
Other interesting topics
|
|||||||||||||||||||||||
