| Author |
 Topic  |
|
|
pavlos
Yak Posting Veteran
Greece
58 Posts |
 Posted - 07/19/2012 : 03:40:08
|
Hey guys,
I have been given the year and week.
Select T0.Week,T0.Year From Weeks T0
how can I determine the start date and end date for each week? I want to be able to return them all on one line.
eg.
T0.Week,T0.Year,(StartDate for the week), (EndDate for the week)
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/19/2012 : 06:46:33
|
I would create a calendar table
Then it becomes
select top 1 FirstDayOfWeek, LastDayOfWeek
from Inp.dim_Calendar
where Rep_Year = @year
and rep_week = @week
You can just use the cte in a query if yoou wish - or just the week calculation.
The problem is that if yoou do the calculation when it's used someone will come along and get it wrong. Also if the business decide to change it you have to change all the places it is used.
CREATE TABLE dim_Calendar
(
[dim_Calendar_id] [int] NOT NULL,
[Rep_Date] [datetime] NOT NULL,
[FirstDayOfMonth] [datetime] NOT NULL,
[LastDayOfMonth] [datetime] NOT NULL,
[Rep_Year] [int] NOT NULL,
[Rep_Month] [int] NOT NULL,
[Rep_WeekOfYear] [int] NOT NULL,
FirstDayOfWeek datetime not null ,
LastDayOfWeek datetime not null ,
[Rep_DayOfWeek] [int] NOT NULL,
PRIMARY KEY CLUSTERED ([dim_Calendar_id])
)
GO
truncate table dim_Calendar -- delete
;with cte as
(
select dte = convert(datetime,'20080101')
union all
select dte = DATEADD(dd,1,dte) from cte where dte < '20201231'
)
insert dim_Calendar
(
dim_Calendar_id,
Rep_Date,
FirstDayOfMonth,
LastDayOfMonth,
Rep_Year,
Rep_Month,
Rep_WeekOfYear,
FirstDayOfWeek ,
LastDayOfWeek ,
Rep_DayOfWeek
)
select dim_Calendar_id = convert(int,convert(varchar(8),dte,112))
, Rep_Date = dte
, FirstDayOfMonth = DATEADD(mm,datediff(mm,0,dte),0)
, LastDayOfMonth = DATEADD(mm,datediff(mm,0,dte)+1,0)-1
, Rep_Year = YEAR(dte)
, Rep_Month = MONTH(dte)
, Rep_WeekOfYear = DATEPART(ww,dte)
, FirstDayOfWeek = DATEADD(wk,datediff(wk,0,dte),0)-1
, LastDayOfWeek = DATEADD(wk,datediff(wk,0,dte)+1,0)-2
, Rep_DayOfWeek = DATEPART(dw,dte)
from cte
option (maxrecursion 0)
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/19/2012 : 07:20:32
|
SELECT
T0.WEEK,
T0.YEAR,
GETDATE() AS ACTUAL_DATE,
DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()) AS HOMOL_ACTUAL_DATE,
DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) AS HOMOL_WEEK_ACTUAL_DATE,
DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE())) AS DAY_IN_THE_WEEK,
DATEPART(WEEKDAY,DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) AS DAY_OF_THE_WEEK_OF_DAY_IN_THE_WEEK,
DATEADD(DD, -DATEPART(WEEKDAY,DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) + 1, DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) as FIRST_DAY_OF_THE_WEEK
FROM WEEKS T0
------------------------
PS - Sorry my bad english |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/19/2012 : 09:51:03
|
quote:
Originally posted by pavlos
Hey guys,
I have been given the year and week.
Select T0.Week,T0.Year From Weeks T0
how can I determine the start date and end date for each week? I want to be able to return them all on one line.
eg.
T0.Week,T0.Year,(StartDate for the week), (EndDate for the week)
you need to use a logic like below given a year value and week value as integer parameters
declare @yr int= 2012,@wk int=5
select dateadd(wk,@wk-1,dateadd(yy,@yr-1900,0)) as startdate,dateadd(wk,@wk,dateadd(yy,@yr-1900,0)) -1 as enddate
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
pavlos
Yak Posting Veteran
Greece
58 Posts |
Posted - 07/20/2012 : 03:44:03
|
DATEADD(DD, -DATEPART(WEEKDAY,DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) + 1, DATEADD(WW, T0.WEEK - DATEPART(WW,(DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))),DATEADD(YY, T0.YEAR - YEAR(GETDATE()), GETDATE()))) as FIRST_DAY_OF_THE_WEEK
brilliant!!
thanks jleitao
I just added 6 to get the end date, good piece of code! |
|
|
|
jleitao
Yak Posting Veteran
Portugal
52 Posts |
Posted - 07/20/2012 : 05:11:09
|
you can use visakh16 query to get first day and last day
------------------------
PS - Sorry my bad english |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/20/2012 : 06:13:59
|
Ok as long as you have the week start day on the server set consistently.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/20/2012 : 10:11:17
|
quote:
Originally posted by nigelrivett
Ok as long as you have the week start day on the server set consistently.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Yep...thats true
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
find out first and last date if given year & week
