July 5, 2012 at 5:10 am
Hi
I need to put in my WHERE clause some script that identifies data that is within the last year. but the period needs to be only be for completed weeks.
I am working on the basis that sunday is last day of week.
For example todays date is the 5th July 2012 so the nearest completed week would be Sunday gone (1st july). So I only want the pervious 52 wks prior to the 1st July 2012. Come the start of next week I would only want to see the previous 52 weeks prior to 8th July.
Anyone have any ideas ???
The date format I'm working with is "2011-06-23 00:00:00.000"
Thanks
July 5, 2012 at 5:19 am
More information please!! 😀
To get the previous Sunday, you could do something like this: -
DECLARE @date AS DATETIME = '2012-07-05 00:00:00.000';
SELECT DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-08', @date), '2012-01-08'); -- Previous Sunday, returns the 1st July 2012
But I'm not clear on what date you want to actually get back (52 weeks previous of 1st July 2012 when 5th July 2012 is supplied).
Do you want 1st July 2011? 3rd July 2011?
For 3rd July 2011: -
DECLARE @date AS DATETIME = '2012-07-05 00:00:00.000';
SELECT DATEADD(WEEK, -52, DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-08', @date), '2012-01-08'));
For 1st July 2011: -
DECLARE @date AS DATETIME = '2012-07-05 00:00:00.000';
SELECT DATEADD(YEAR, -1, DATEADD(WEEK, DATEDIFF(WEEK, '2012-01-08', @date), '2012-01-08'));
July 5, 2012 at 5:19 am
--Try something like this.
declare @dt datetime
set @dt = '20120705'
declare @dt datetime
set @dt = '20120705'
select dateadd(wk,-52,case when @@datefirst = 7 then
case
when datepart(dw,@dt) = 1 then @dt --it is alredy sunday so no need to find prev sunday
else dateadd(dd,-datepart(dw,@dt)+ 1,@dt)
end
when @@datefirst = 1 then
case
when datepart(dw,@dt) = 7 then @dt --it is alredy sunday so no need to find prev sunday
else dateadd(dd,-datepart(dw,@dt),@dt)
end
end)
It worked in Sybase and I am sure it should work on SQL Server as well. I will post the sql one once I will get access to sql server machine but something on similar line should work..
GulliMeel
Finding top n Worst Performing queries[/url]
Improve the performance of Merge Join(special case)
How to Post Performance Problem -Gail Shaw[/url]
July 5, 2012 at 5:27 am
Thanks for replies.
i will always want to select previous 52 prior to the previous sunday in the current week.
thanks
July 5, 2012 at 5:30 am
How about:
select @dt [date as is]
, dateadd(yy, -1,@dt) [date-1Y]
, dateadd(wk, datediff(wk, 0, dateadd(yy, -1,@dt)), 0) [date - 1 year then Beginning of week]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 5, 2012 at 1:09 pm
Here's some code I wrote to get the first and last day of the previous 12 months. You can modify it for your uses to get week ending dates.
;WITH
t1 AS (SELECT 1 N UNION ALL SELECT 1 N),
t2 AS (SELECT 1 N FROM t1 x CROSS JOIN t1 y),
t3 AS (SELECT 1 N FROM t2 x CROSS JOIN t2 y),
Tally AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM t3 x
CROSS JOIN t3),
MonthDates AS (SELECT DATEADD(mm, -(N - 1), CAST(CAST(DATEADD(dd,-(DAY(DATEADD(mm, 1, GETDATE()))- 1),DATEADD(mm, 0, GETDATE())) AS DATE) AS DATETIME)) AS PeriodStart,
DATEADD(mm, -(N - 1), DATEADD(ss, -1, CAST(CAST(DATEADD(dd, -DAY(DATEADD(m, 1, GETDATE())) + 1, DATEADD(m, 1, GETDATE())) AS DATE) AS DATETIME))) PeriodEnd
FROM Tally
WHERE N BETWEEN 2 AND 13),
Months AS (SELECT CAST(YEAR(PeriodEnd) AS NVARCHAR(4)) + RIGHT('0' + CAST(MONTH(PeriodEnd) AS NVARCHAR(2)), 2) AS YYYYMM
FROM MonthDates)
select *
from monthdates
July 6, 2012 at 7:19 am
This will get you the date for last sunday:-
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
This will get you last sunday - 52 weeks
SELECT DATEADD(wk, -52, DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6))
so this will be your where clause:-
WHERE MyDateValue BETWEEN DATEADD(wk, -52, DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)) AND DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
July 6, 2012 at 1:09 pm
I suggest avoiding "WEEK" or anything that could or does rely on SQL settings such as @@DATEFIRST (I don't think WEEK actually does, but I think it does always use Sunday, so if your anchor day were to change, the code would no longer work).
The code below will always give you the last Sunday, regardless of any SQL setting (*prior* to the current date, that is, if today is Sunday, you get the prior Sunday's date (if you want to return the current day if it's Sunday, just remove the DATEADD(DAY, -1)).
SELECT
DATEADD(DAY, DATEDIFF(DAY, '19000107', DATEADD(DAY, -1, GETDATE())) / 7 * 7, '19000107')
So, to get 52-weeks worth of data for the query, you can do this [naturally adjust the (52 * 7), if you need to, to get the exact relative starting date you want]:
WHERE
table_date_column >= DATEADD(DAY, DATEDIFF(DAY, '19000107', DATEADD(DAY, -1, GETDATE())) / 7 * 7 - (52 * 7), '19000107')
AND
table_date_column < DATEADD(DAY, DATEDIFF(DAY, '19000107', DATEADD(DAY, -1, GETDATE())) / 7 * 7 + 1, '19000107')
[Note that the code above can be adjusted for any day -- for example, changing '19000107' to '1900103' would always give you the first Wednesday before the current date.]
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
July 9, 2012 at 5:23 am
many thanks for all your helpfull suggestions.
P.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply