quote:

---

Originally posted by Grifter

Hi

In 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?

Thanks

G

---

DATEADD(dd,-DATEDIFF(dd,'19000101',DATEADD(dd,2,dt))%7 ,dt)

quote:

---

Originally posted by James K

quote:

---

Originally posted by Grifter

Hi

In 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?

Thanks

G

---

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

---

quote:

---

Incorrect syntax near the keyword 'VALUES'

---

quote:

---

Originally posted by SwePeso

SELECT		f.Seq,
		f.FromDate,
		f.ToDate
FROM		(
			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

---

ALTER FUNCTION [dbo].[daterangeweeknumber]
(
	-- Add the parameters for the function here
	
	@refdate datetime
	
)
RETURNS int
AS
BEGIN
    -- 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 @weeknumber

END

SELECT		f.Seq,
		f.FromDate,
		f.ToDate
FROM		(
			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);

quote:

---

Originally posted by SwePeso

VALUES is a 2008 constructor.
Here is a SQL Server 2005 compatible solution.

SELECT		f.Seq,
		f.FromDate,
		f.ToDate
FROM		(
			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

---

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'VALUES'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'AS'.

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.ToDate
FROM		(
			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 5
Incorrect syntax near the keyword 'VALUES'.
Msg 156, Level 15, State 1, Line 12
Incorrect syntax near the keyword 'AS'.

---