Dates from weeknumber - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Dates from weeknumber

Author

Topic

CoffeeGuru
Starting Member

3 Posts

Posted - 2014-10-22 : 16:27:39

I have tried to get comfort from here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=185440

But am still perplexed. This is my issue
I have the option of year (int), week number (int) or yearweek (int)

What I need to get is
SELECT
ProductID
sum(sales) AS [Half year sales]

WHERE yearweek > {user input year} - 1 * 100 + {user input week}
AND yearweek <= {Here is where i am stuck}

FROM MyTable

also I need to calculate the same sum for the second half of the year

WHERE yearweek > {Here is where i am stuck}
AND yearweek <= {user input year} * 100 + {user input week}

My best guess is to convert the yearweek to a date and always set the calculated date to the first of the month (for my purpose that is fine) then use dateadd to find the end of the first half then convert to a yearweek integer
add 1 to month then convert the result to a yearweek ineger

of course I will also then need to convert the start and end yearweek integers to (the most likely) first week number of the month and last week number of the month.

Has ideas

CoffeeGuru
Starting Member

3 Posts

Posted - 2014-10-23 : 03:32:35

I had a thought last night.

.it happens all too frequently.
How about I add a calculated field to MyTable that calculates the date from YYYY & WW (YYYYWW is already a calculated field) that would then solve all my problems.

But what is the best way to do this.
I'm still as stuck

BBarn
Starting Member

14 Posts

Posted - 2014-10-23 : 08:57:02

CoffeeGuru,

There may be other ways of accomplishing this, but here is one that I have used in the past.

Create the following table and populate it with the necessary data. Make any adjustments you need, obviously. This will contain one date for each date in the range you need, I usually seed mine with 3 years past and 10 years future.

Once that is done, you can link to this table by a date field to extract the elements you are searching for. QTR, WEEK, MONTH, SEMESTER...

Since it is joined, you can also query from its contents to select the parent records. Because of the link, you should always get a 1-to-1 match.

CREATE TABLE [dbo].[DIMDate](
[DATEKEY] [datetime] NOT NULL,
[DayNumberOfWeek] [int] NULL,
[DayNameOfWeek] [varchar](10) NULL,
[DayNumberOfMonth] [int] NULL,
[DayNumberOfYear] [int] NULL,
[WeekNumberOfYear] [int] NULL,
[MonthName] [varchar](10) NULL,
[MonthNumberOfYear] [int] NULL,
[CalendarQuarter] [int] NULL,
[CalendarYear] [int] NULL,
[Semester] [int] NULL,
PRIMARY KEY CLUSTERED
(
[DATEKEY] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CoffeeGuru
Starting Member

3 Posts

Posted - 2014-10-24 : 03:08:56

BBarn

Thanks for your time. I can see that as a quick fix for the date.
For me the QTR, HALFYEAR set up would not work though as I would need a rolling QTR, HALFYEAR as in this scenario.

Half year query
Entered date = 201442 (year 2014 wk 42)
Sum(sales) between 201416 and 201442 AS Second half of period
Sum(Sales) between 201341 and 201415 AS First half of period

Also for QTR/Monthly etc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources