Author |
Topic |
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2014-07-18 : 09:18:50
|
HiIn my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.Can anyone suggest does this sound like the way to do this or is there another way?ThanksG |
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-18 : 11:55:18
|
quote: Originally posted by Grifter HiIn my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.Can anyone suggest does this sound like the way to do this or is there another way?ThanksG
I didn't quite follow the problem you are trying to solve - except the title of the posting "Finding last Saturday date then range last 28 days".You can find the prior Saturday for any date using the following:DATEADD(dd,-DATEDIFF(dd,'19000101',DATEADD(dd,2,dt))%7 ,dt) Then if you want to pick the 28 days prior to that use dateadd to subtract 28 days.Some sample data and expected output would help clarify the problem |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-19 : 08:06:07
|
[code]SELECT f.Seq, f.FromDate, f.ToDateFROM ( VALUES (DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')) ) AS m(theDate)CROSS APPLY ( VALUES (1, DATEADD(DAY, -27, m.theDate), DATEADD(DAY, -21, m.theDate)), (2, DATEADD(DAY, -20, m.theDate), DATEADD(DAY, -14, m.theDate)), (3, DATEADD(DAY, -13, m.theDate), DATEADD(DAY, -07, m.theDate)), (4, DATEADD(DAY, -6, m.theDate), m.theDate) ) AS f(Seq, FromDate, ToDate);[/code] Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2014-07-22 : 03:45:59
|
If I run a query on the Wednesday then I want to load range of 28 days dates up to the previous Saturday (like a temp look up table) and number the 4 weeks of the 28 days 1-4, 4 being the most recent week. quote: Originally posted by James K
quote: Originally posted by Grifter HiIn my query I have a date field and using that date field I want to pass the date to a function, or whatever is most appropriate, then compare that date with a range of the last 4 weeks, which will be numbered 1-4, 1 being earliest and 4 being latest, and then return the week number.I've determined I need some sort of look up but will need to be dynamic - thinking maybe a temp table, first using the date the report is run on and counting 28 days back from the most recent last saturday then setting number to 1 for first 7 days then 2 for next 7 days etc.Can anyone suggest does this sound like the way to do this or is there another way?ThanksG
I didn't quite follow the problem you are trying to solve - except the title of the posting "Finding last Saturday date then range last 28 days".You can find the prior Saturday for any date using the following:DATEADD(dd,-DATEDIFF(dd,'19000101',DATEADD(dd,2,dt))%7 ,dt) Then if you want to pick the 28 days prior to that use dateadd to subtract 28 days.Some sample data and expected output would help clarify the problem
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2014-07-22 : 04:17:07
|
This code only works on my local SQL Express installation but on the 2005 server version it gives error:quote: Incorrect syntax near the keyword 'VALUES'
I think it's something to do with compatibilty settings or similar but in any case I don't want to start changing settings in DB. This is a really good solution for later version however so many thanksGquote: Originally posted by SwePeso
SELECT f.Seq, f.FromDate, f.ToDateFROM ( VALUES (DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')) ) AS m(theDate)CROSS APPLY ( VALUES (1, DATEADD(DAY, -27, m.theDate), DATEADD(DAY, -21, m.theDate)), (2, DATEADD(DAY, -20, m.theDate), DATEADD(DAY, -14, m.theDate)), (3, DATEADD(DAY, -13, m.theDate), DATEADD(DAY, -07, m.theDate)), (4, DATEADD(DAY, -6, m.theDate), m.theDate) ) AS f(Seq, FromDate, ToDate); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
|
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2014-07-22 : 06:11:08
|
I ended up just doing this in my function:ALTER FUNCTION [dbo].[daterangeweeknumber]( -- Add the parameters for the function here @refdate datetime )RETURNS intASBEGIN -- Run procedure to populate table -- Declare the return variable here DECLARE @weeknumber int DECLARE @mytemptablevariable table (weeknumber INT,fromdate DATETIME,todate DATETIME) DECLARE @lastsaturdaydate DATETIME set @lastsaturdaydate = DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230') -- Add the T-SQL statements to compute the return value here INSERT INTO @mytemptablevariable values (1, DATEADD(DAY, -27, @lastsaturdaydate), DATEADD(DAY, -21, @lastsaturdaydate)) INSERT INTO @mytemptablevariable values (2, DATEADD(DAY, -20, @lastsaturdaydate), DATEADD(DAY, -14, @lastsaturdaydate)) INSERT INTO @mytemptablevariable values (3, DATEADD(DAY, -13, @lastsaturdaydate), DATEADD(DAY, -07, @lastsaturdaydate)) INSERT INTO @mytemptablevariable values (4, DATEADD(DAY, -6, @lastsaturdaydate), @lastsaturdaydate) select @weeknumber = weeknumber from @mytemptablevariable where @refdate >= fromdate AND @refdate <= todate -- Return the result of the function RETURN @weeknumberEND |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2014-07-22 : 07:40:01
|
VALUES is a 2008 constructor.Here is a SQL Server 2005 compatible solution.SELECT f.Seq, f.FromDate, f.ToDateFROM ( VALUES (DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')) ) AS m(theDate)CROSS APPLY ( SELECT 1, DATEADD(DAY, -27, m.theDate), DATEADD(DAY, -21, m.theDate) UNION ALL SELECT 2, DATEADD(DAY, -20, m.theDate), DATEADD(DAY, -14, m.theDate) UNION ALL SELECT 3, DATEADD(DAY, -13, m.theDate), DATEADD(DAY, -7, m.theDate) UNION ALL SELECT 4, DATEADD(DAY, -6, m.theDate), m.theDate ) AS f(Seq, FromDate, ToDate); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
Grifter
Constraint Violating Yak Guru
274 Posts |
Posted - 2014-07-23 : 06:21:43
|
quote: Originally posted by SwePeso VALUES is a 2008 constructor.Here is a SQL Server 2005 compatible solution.SELECT f.Seq, f.FromDate, f.ToDateFROM ( VALUES (DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')) ) AS m(theDate)CROSS APPLY ( SELECT 1, DATEADD(DAY, -27, m.theDate), DATEADD(DAY, -21, m.theDate) UNION ALL SELECT 2, DATEADD(DAY, -20, m.theDate), DATEADD(DAY, -14, m.theDate) UNION ALL SELECT 3, DATEADD(DAY, -13, m.theDate), DATEADD(DAY, -7, m.theDate) UNION ALL SELECT 4, DATEADD(DAY, -6, m.theDate), m.theDate ) AS f(Seq, FromDate, ToDate); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
This gives these errors on server 2005:Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'VALUES'.Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'AS'. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2014-07-23 : 08:36:47
|
quote: Originally posted by Grifter
quote: Originally posted by SwePeso VALUES is a 2008 constructor.Here is a SQL Server 2005 compatible solution.SELECT f.Seq, f.FromDate, f.ToDateFROM ( SELECT (DATEADD(DAY, DATEDIFF(DAY, '18991230', GETDATE()) / 7 * 7, '18991230')) ) AS m(theDate)CROSS APPLY ( SELECT 1, DATEADD(DAY, -27, m.theDate), DATEADD(DAY, -21, m.theDate) UNION ALL SELECT 2, DATEADD(DAY, -20, m.theDate), DATEADD(DAY, -14, m.theDate) UNION ALL SELECT 3, DATEADD(DAY, -13, m.theDate), DATEADD(DAY, -7, m.theDate) UNION ALL SELECT 4, DATEADD(DAY, -6, m.theDate), m.theDate ) AS f(Seq, FromDate, ToDate); Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
This gives these errors on server 2005:Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'VALUES'.Msg 156, Level 15, State 1, Line 12Incorrect syntax near the keyword 'AS'.
Change VALUES to SELECT |
|
|
|
|
|