September 17, 2008 at 4:39 am
hi guys
i need to create a script to get week 36,37,38 and 39 of the current year.
September 17, 2008 at 5:29 am
Nomvula (9/17/2008)
hi guysi need to create a script to get week 36,37,38 and 39 of the current year.
No problem:
SELECT DATEPART(YY, GETDATE()) AS CurrentYear, CAST(36 AS SMALLINT) AS [Week] UNION ALL
SELECT DATEPART(YY, GETDATE()), 37 UNION ALL
SELECT DATEPART(YY, GETDATE()), 38 UNION ALL
SELECT DATEPART(YY, GETDATE()), 39
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 17, 2008 at 5:38 am
hi Chris
thanks for the response, actualy i'm trying to get dates within week 36,37,38,39.
apologies for my mistake, i didn't elaborate that thorough
September 17, 2008 at 5:44 am
it depends ..... What's your week 1 ??
Microsoft starts week 1 at 1/1 :crazy:
In my company, week 1 is the week which containns Jan 4th.
So we always need to check and correct this when determining the week number.
CREATE FUNCTION dbo.fn_ALZDBA_convert_date2WeekALZDBA (@RefDate datetime)
RETURNS integer
AS
BEGIN
-- 4 januari = week 1 !
declare @ALZDBARefDayFirstWeek as datetime
declare @ALZDBARefDayLastWeek as datetime
declare @WrkDay as datetime
declare @WrkWeek1 as int
declare @WrkFirstALZDBAWeekDay1 as datetime
declare @WrkFirstALZDBAWeekDay7 as datetime
declare @WrkCurrentALZDBAWeekDay1 as datetime
declare @WrkCurrentALZDBAWeekDay7 as datetime
declare @WrkWeekYear as int
declare @ALZDBAWeekNr as int
declare @ALZDBAWeekOffset as int
-- bepalen dag1 van ALZDBAweek 1 en dag1 van de @RefDate-ALZDBAWeek
select @ALZDBARefDayFirstWeek = convert(datetime, cast(year(@RefDate) as char(4)) + '/01/04',121)
, @ALZDBARefDayLastWeek = convert(datetime, cast(year(@RefDate) as char(4)) + '/12/31',121)
Select @WrkWeek1 = datepart(wk,@ALZDBARefDayFirstWeek)
, @WrkFirstALZDBAWeekDay1 = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@ALZDBARefDayFirstWeek,'F')
, @WrkFirstALZDBAWeekDay7 = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@ALZDBARefDayFirstWeek,'L')
, @WrkCurrentALZDBAWeekDay1 = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@RefDate,'F')
, @WrkCurrentALZDBAWeekDay7 = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@RefDate,'L')
-- Bij MS week1 starts at 01/01
-- Bij ALZDBA week 1 = 04/01
-- indien 1 jan _NIET_ in eerste ALZDBA-week valt, zit de MS één week te ver. Dus corrigeren
Select @WrkWeekYear = datepart(yyyy,@WrkCurrentALZDBAWeekDay1)
, @WrkDay = @WrkCurrentALZDBAWeekDay1
IF datepart(yyyy,@WrkCurrentALZDBAWeekDay1) < datepart(yyyy,@WrkCurrentALZDBAWeekDay7)
begin
if @RefDate between @WrkFirstALZDBAWeekDay1 and @WrkFirstALZDBAWeekDay7
begin
select @ALZDBAWeekOffset = case when datepart(dd,@WrkFirstALZDBAWeekDay7) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZDBAWeekOffset = case datepart(wk,@WrkFirstALZDBAWeekDay1) - @ALZDBAWeekOffset
when 0
then 0
else @ALZDBAWeekOffset end
if @ALZDBAWeekOffset = 0
begin
Select @WrkWeekYear = datepart(yyyy,@WrkFirstALZDBAWeekDay7)
, @WrkDay = convert(datetime, cast(datepart(yyyy,@WrkFirstALZDBAWeekDay7) as char(4)) + '/01/01',121)
end
else
begin
Select @WrkWeekYear = datepart(yyyy,@WrkFirstALZDBAWeekDay1)
, @ALZDBAWeekOffset = 1
end
end
else
begin
declare @WrkLastALZDBAWeekDay1 as datetime
declare @WrkLastALZDBAWeekDay7 as datetime
Select @WrkLastALZDBAWeekDay1 = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@ALZDBARefDayLastWeek,'F')
, @WrkLastALZDBAWeekDay7 = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@ALZDBARefDayLastWeek,'L')
if @RefDate between @WrkLastALZDBAWeekDay1 and @WrkLastALZDBAWeekDay7
begin
if datepart(dd,@WrkLastALZDBAWeekDay7) < 4
begin
select @ALZDBAWeekOffset = case when datepart(dd,@WrkFirstALZDBAWeekDay7) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZDBAWeekOffset = case datepart(wk,@WrkFirstALZDBAWeekDay1) - @ALZDBAWeekOffset
when 0
then 0
else @ALZDBAWeekOffset end
if @ALZDBAWeekOffset = 0
begin
Select @WrkWeekYear = datepart(yyyy,@WrkFirstALZDBAWeekDay7)
end
else
begin
Select @WrkWeekYear = datepart(yyyy,@WrkLastALZDBAWeekDay1)
end
end
else
begin
select @ALZDBAWeekOffset = case when datepart(dd,@WrkLastALZDBAWeekDay7) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZDBAWeekOffset = case datepart(wk,@WrkLastALZDBAWeekDay1) - @ALZDBAWeekOffset
when 0
then 0
else @ALZDBAWeekOffset end
if @ALZDBAWeekOffset = 0
begin
Select @WrkWeekYear = datepart(yyyy,@WrkLastALZDBAWeekDay7)
, @WrkDay = convert(datetime, cast(datepart(yyyy,@RefDate) as char(4)) + '/01/01',121)
end
else
begin
Select @WrkWeekYear = datepart(yyyy,@WrkLastALZDBAWeekDay1)
--, @ALZDBAWeekOffset = 1
end
end
end
else
begin
declare @WrkFirstALZDBAWeekDay1LastYear as datetime
declare @WrkFirstALZDBAWeekDay7LastYear as datetime
select @ALZDBARefDayFirstWeek = convert(datetime, cast((datepart(yyyy,@RefDate) - 1) as char(4)) + '/01/04',121)
select @WrkFirstALZDBAWeekDay1LastYear = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@ALZDBARefDayFirstWeek,'F')
, @WrkFirstALZDBAWeekDay7LastYear = dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA(@ALZDBARefDayFirstWeek,'L')
select @ALZDBAWeekOffset = case when datepart(dd,@WrkFirstALZDBAWeekDay7LastYear) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZDBAWeekOffset = case datepart(wk,@WrkFirstALZDBAWeekDay1LastYear) - @ALZDBAWeekOffset
when 0
then 0
else @ALZDBAWeekOffset end
end
end
end
ELSE
begin
select @ALZDBAWeekOffset = case when datepart(dd,@WrkFirstALZDBAWeekDay7) between 4 and 7
then 0
else 1
end
-- opgelet week 00 mag niet !
select @ALZDBAWeekOffset = case datepart(wk,@WrkFirstALZDBAWeekDay1) - @ALZDBAWeekOffset
when 0
then 0
else @ALZDBAWeekOffset end
end
Select @ALZDBAWeekNr = (@WrkWeekYear * 100 ) + datepart(wk,@WrkDay) - @ALZDBAWeekOffset
RETURN (@ALZDBAWeekNr)
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE FUNCTION dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA (@RefDate datetime, @FirstLast char(1) = 'F')
RETURNS datetime
AS
BEGIN
-- Bij ALZDBA valt 4 januari altijd in week 1 !
-- Bij ALZDBA is de eerste dag van de week Maandag !
DECLARE @return_date as datetime
declare @Firstdate datetime
declare @WrkDate datetime
declare @refDays int
select @WrkDate = cast(convert(char(10), @RefDate, 121) as datetime)
select @refDays = (datepart(dw,@WrkDate) - 1) * (-1)
select @Firstdate = dateadd( dd, @refDays, @WrkDate)
if @@DATEFIRST = 7 -- indien datefirst op zondag staat (default) voor ALZDBA één dagje bijtellen
begin
select @Firstdate = dateadd(dd, 1,@Firstdate)
end
if @RefDate < @Firstdate
begin
select @Firstdate = dateadd(dd, (-7) ,@Firstdate)
end
if @FirstLast = 'F'
begin
set @return_date = @Firstdate
end
else
begin
select @return_date = dateadd(ms,-2,dateadd(dd,7,@Firstdate))
end
RETURN (@return_date)
END
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
September 17, 2008 at 5:45 am
Nomvula (9/17/2008)
hi Christhanks for the response, actualy i'm trying to get dates within week 36,37,38,39.
apologies for my mistake, i didn't elaborate that thorough
Which dates, Nomvula? The start and end dates of each week, all 7 days for each week, all dates or working days only? As variables or as values in a table column?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 17, 2008 at 5:54 am
for instance '01/09/2008' - '07/09/2008' by looking in my calendar those dates fall in week 36 so i need to get all the dates which falls on wk 36,37,38,39
September 17, 2008 at 6:08 am
select dateadd(wk,36,master.dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA ('20080104', 'F'))
, dateadd(wk,37,master.dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA ('20080104', 'F'))
, dateadd(wk,38,master.dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA ('20080104', 'F'))
, dateadd(wk,39,master.dbo.fn_ALZDBA_convert_date2WeekRangeALZDBA ('20080104', 'F'))
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
September 17, 2008 at 7:57 am
Nomvula (9/17/2008)
for instance '01/09/2008' - '07/09/2008' by looking in my calendar those dates fall in week 36 so i need to get all the dates which falls on wk 36,37,38,39
Here's a quick and dirty method using the information you have to hand - the start and end dates of week 36...
[font="Courier New"]SELECT WeekNo+number-36 AS WeekNo,
DATEADD(ww, number-36, StartDate) AS StartDate,
DATEADD(ww, number-36, EndDate) AS EndDate
FROM (SELECT 36 AS WeekNo, CAST('01/09/2008' AS DATETIME) AS StartDate, CAST('07/09/2008' AS DATETIME) AS EndDate) d,
(SELECT CAST(36 AS INT) AS number UNION ALL SELECT 37 UNION ALL SELECT 38 UNION ALL SELECT 39) n
--WHERE number BETWEEN 36 AND 39 -- <<< if using a "numbers" or "tally" table
ORDER BY number[/font]
...which will work for this year. But you should use ALZDBA's elegant code once you've established your local rules for determining the first day of the first week of the year.
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 18, 2008 at 1:29 am
hi chris i looked at that and it fine but you giving me the start and the end date but i want the list of all the dates within those week No's
September 18, 2008 at 2:00 am
Nomvula (9/18/2008)
hi chris i looked at that and it fine but you giving me the start and the end date but i want the list of all the dates within those week No's
A list or a table?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 18, 2008 at 2:33 am
in my table i have a date column, the data starts from 2004 - 2008 i'm required to count all records captured from 2004 to 2008 and divide it with what has been captured in week 36,37,38 and 39.
now what i need to do is to write a query which will retrieve all dates within the mentioned dates.
September 19, 2008 at 3:09 am
Is there any reason why you aren't using a start date (the first day of week 36) and an end date (the last day of week 39) for this?
WHERE YourDateColumn >= [first day of week 36]
AND YourDateColumn < [last day of week 39 plus 1]
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 19, 2008 at 3:26 am
Hi,
this might be crude and ugle but it'll deliver what you're asking for...
[font="Courier New"]DECLARE @STARTWEEK INT
DECLARE @ENDWEEK INT
SELECT @STARTWEEK = 37-- Your Start Week
SELECT @ENDWEEK = 39 -- Your End Week
DECLARE @JAN1 DATETIME
DECLARE @DATE DATETIME
DECLARE @STARTWEEKRANDOMDAY DATETIME
DECLARE @STARTWEEKFIRSTDAY DATETIME
DECLARE @LASTWEEKLASTDAY DATETIME
-- Start by Getting the first date in the Year Jan1
SELECT @JAN1 = CONVERT(DATETIME,CONVERT(VARCHAR(4),DATEPART(YEAR,GETDATE())) + '-01-01')
-- Get A Date in your Start Week by adding weeks to Jan 1st.
SELECT @STARTWEEKRANDOMDAY = DATEADD(WEEK,(@STARTWEEK-1),@JAN1)
-- Get Fist Day of the Startweek ( a Sunday)
SELECT @STARTWEEKFIRSTDAY = @STARTWEEKRANDOMDAY + 1 - DATEPART(WEEKDAY,@STARTWEEKRANDOMDAY)
-- Get the Last Day of the End Week ( a Saturday)
SELECT @LASTWEEKLASTDAY = DATEADD(DAY,-1,DATEADD(WEEK,(@ENDWEEK+1-@STARTWEEK),@STARTWEEKFIRSTDAY))
SELECT @DATE = @STARTWEEKFIRSTDAY
-- Use a table to store the date list.
CREATE TABLE #DATES
(
WEEKDATE DATETIME NOT NULL
)
-- Loop through from StartDateFirstDay to EndDateLastDay adding to the #DATES list
WHILE @DATE <= @LASTWEEKLASTDAY
BEGIN
INSERT INTO #DATES SELECT @DATE
SELECT @DATE = DATEADD(DAY,1,@DATE)
END
-- Voila!
SELECT *,DATEPART(WEEK,WEEKDATE),DATEPART(WEEKDAY,WEEKDATE),DATENAME(WEEKDAY,WEEKDATE) FROM #DATES[/font]
Kyran
September 19, 2008 at 3:40 am
There is this very nice and eye-opening article concering ranges ...
Called "The "Numbers" or "Tally" Table: What it is and how it replaces a loop."
www.sqlservercentral.com/articles/TSQL/62867/
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
September 19, 2008 at 3:52 am
HI Try this procedure
CREATE PROCEDURE PROC_GETWEEKNUMBERS
@strtdate datetime,
@enddate datetime
AS
DECLARE @cnt int
DECLARE @i int
SET @i=0
SELECT @cnt= datediff(ww,@strtdate,@enddate)
DECLARE @WeekNumbers Table(weeknumber int)
DECLARE @wknum int
SELECT @wknum=Datepart(Wk,@strtdate)
SELECT @strtdate=Dateadd(dd,1,@strtdate)
WHILE(@i < @CNT)
BEGIN
Insert into @WeekNumbers(weeknumber) values (Datepart(Wk,@strtdate))
SELECT @strtdate=Dateadd(dd,6,@strtdate)
SET @i=@i+1
END
(SELECT * from @WeekNumbers)
GO
After creating try this query for week numbers b/w 1st aug and 30 th aug
EXEC dbo.PROC_GETWEEKNUMBERS '1-aug-2008','31-aug-2008'
happy programming 😀
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply