Converting week number to actual date 2

[pandatime]

Hi,

I've been searching all over the internet but not coming up with a satisfactory answer.

Basically, I have this query:

select datepart(week, created_dt) as week_no from myTable

which gives me: 1, 2, 3, etc for "week_no"

but since this is for a report, I also want the ACTUAL date the week_no is referencing, for example Jan 3, 2011.

Thanks much

 

[PWise]

select created_dt,datepart(week, created_dt) as week_no from myTable

 

[pandatime]

I'm sorry that won't work because I don't want to group on created_dt, only week_no and the week day for that week_no

as in

1 Jan 3, 2011
2 Jan 10, 2011
3 Jan 17, 2011

etc

 

[PWise]

So: what day do you want if you have these dates

1/3/11
1/4/11
1/5/11

 

[pandatime]

1/3/2011 since that is the Sunday, and beginning of the week

 

[PWise]

well here in lala land 1/3/11 was on a monday not a sunday
in any case i think you will need a weeks table listing weekstart and weekend whatever day of the week your week starts sunday or monday

and join your table

Select weekstart,datepart(week, created_dt) as week_no 
from myTable
inner join weekstable 
on created_dt between weekstart and weekend
group by weekstart,datepart(week, created_dt)

think what you are going to do when jan 1 is in middle of the week

 

[gmmastros]

Let's think about this a different way.

From reading your question, it appears that you don't really care about week at all. In fact, it appears as though you really just want to get the date of the Sunday immediately prior to the date you have in your table. Is this right?

Today is Monday, Feb. 28 2011. So, if you had:

[tt][blue]
What you have What you want
-------------- --------------
2011-02-25 Fri 2011-02-20
2011-02-26 Sat 2011-02-20
2011-02-27 Sun 2011-02-27
2011-02-28 Mon 2011-02-27
2011-03-01 Tue 2011-02-27
2011-03-02 Wed 2011-02-27
2011-03-03 Thu 2011-02-27
2011-03-04 Fri 2011-02-27
2011-03-05 Sat 2011-02-27
2011-03-06 Sun 2011-03-06
2011-03-04 Mon 2011-03-06
[/blue][/tt]

Do I understand your question correctly?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

Hi George,

No, what I want is to sum up some data by week, so I'm selecting the week number then grouping on it.

It works fine, but I also want to see what the actual DATE is for the given week number.

So in my report it will look like:

week_no date count
1 1/3/11 99
2 1/10/11 102
3 1/17/11 85
etc

So, on the 1/10, 102 represents the count between 1/3 and 1/10. That part already works for me. I just want the date now so that whoever reads the report knows what the time frame was.

Thanks!

 

[markros]

Check this discussion

http://social.msdn.microsoft.com/Fo...l/thread/cf233caf-4ccd-4bca-add2-b5eb467f2dd4

I remember I answered this question there recently also, but this was the first thread that came up and it looks like it should do the trick.

PluralSight Learning Library

 

[markros]

See also

http://social.msdn.microsoft.com/Fo.../thread/6dc2666c-0ed8-42ea-b932-8f8d18101994/

PluralSight Learning Library

 

[markros]

And just in case one more thread

http://social.msdn.microsoft.com/Fo...l/thread/9b280437-e3ad-46ab-b833-7b5d9accfcdf

PluralSight Learning Library

 

[gmmastros]

Well... I have a bit of a problem with your approach. I mean... under the right circumstances, it might be fine, but it still scares me a little. Let me explain...

Each year has it's own set of weeks. The first day of the week may change depending on year. For example, Jan 3 may be for the first day for year 2011, but the first day of the first week is likely going to be different for 2012 and 2010. This is why I don't like using the week parameter for the datepart function.

so I'm selecting the week number then grouping on it.

If your data spans more than a year, then your DatePart(Week,) calculation will group data from multiple years in to one. This may be what you want, but it might not either.

Instead, I would calculate the first day of each week for your createdate column, and then use that to group on (effectively killing 2 birds with one stone). This method will also accommodate multiple years.

I encourage you to take a look at this code:

Declare @Temp Table(CreateDate DateTime)

insert into @temp Values('2011-02-25')
insert into @temp Values('2011-02-26')
insert into @temp Values('2011-02-27')
insert into @temp Values('2011-02-28')
insert into @temp Values('2011-03-01')
insert into @temp Values('2011-03-02')
insert into @temp Values('2011-03-03')
insert into @temp Values('2011-03-04')
insert into @temp Values('2011-03-05')
insert into @temp Values('2011-03-06')
insert into @temp Values('2011-03-07')

Select CreateDate, DateAdd(Day, -DateDiff(Day, [!]-1[/!], CreateDate) %7, CreateDate)
From   @Temp

Note the -1. If I interpret your question correctly, you want the week to start on Sunday. Day # -1 is actually December 31, 1899, which happens to be a Sunday. If you want your first day of the week to be Monday (or any other day), then change the -1 value.

Now... if you use this calculation to do your grouping, you'll already have the first day of the week AND your grouping will respect year boundaries.

what I want is to sum up some data by week

Let's see my method in action:

Declare @Temp Table(CreateDate DateTime, PizzasEaten Int)

insert into @temp Values('2011-02-25', 2)
insert into @temp Values('2011-02-26', 1)
insert into @temp Values('2011-02-27', 0)
insert into @temp Values('2011-02-28', 3)
insert into @temp Values('2011-03-01', 5)
insert into @temp Values('2011-03-02', 8)
insert into @temp Values('2011-03-03', 4)
insert into @temp Values('2011-03-04', 10)
insert into @temp Values('2011-03-05', 1)
insert into @temp Values('2011-03-06', 4)
insert into @temp Values('2011-03-07', 6)

Select DateAdd(Day, -DateDiff(Day, -1, CreateDate) %7, CreateDate) As StartOfWeek,
       Sum(PizzasEaten) As PizzaCount
From   @Temp 
Group BY DateAdd(Day, -DateDiff(Day, -1, CreateDate) %7, CreateDate)

As you can see, the data is broken out by week with the first day of the week shown in the output.

** Note: I really don't eat that many pizzas.

Make sense?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pandatime]

SET DATEFIRST 1
declare @wk int set @wk = 1
declare @yr int set @yr = 2007

select dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4 -
datepart(dw, dateadd (week, @wk, dateadd (year, @yr-1900, 0)) - 4) + 1

-- was enough for me to figure out what I need, thanks!!!

 

[sparkbyte]

Saw this thread and thought tyhis might be of use. I happend to be working on a report somewhat simular and created this function.

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetDateRangeforWeek]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_GetDateRangeforWeek]
Go
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****************************************************************************************

	Created by:		John Fuhrman
	Date:			03/01/2011
	Description:	Function to give the starting and ending dates for any given week.

******************************************************************************************/

CREATE FUNCTION [fn_GetDateRangeforWeek]
	(@datecol datetime)
RETURNS @WeekRange TABLE (StartOfWeek DateTime, EndOfWeek DateTime)
AS

Begin

--	DECLARE @datecol datetime; -- For testing Select statements
	DECLARE @WeekNum INT
		  , @YearNum char(4);

--	Set @datecol = GetDate() -- set date to current Date for testing

	SELECT @WeekNum = DATEPART(WK, @datecol)
		 , @YearNum = CAST(DATEPART(YY, @datecol) AS CHAR(4));

	-- once you have the @WeekNum and @YearNum set, the following calculates the date range.
	Insert Into @WeekRange(StartOfWeek, EndOfWeek)
		Select 
			DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek,
			DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

	RETURN
End

Example Code for use.

Declare @OrderDate DateTime
set @OrderDate = '1/1/2011'

Select 
	(Select StartOfWeek FROM [tempdb].[dbo].[fn_GetDateRangeforWeek] (@OrderDate)) As StartOfWeek,
	(Select EndOfWeek FROM [tempdb].[dbo].[fn_GetDateRangeforWeek] (@OrderDate)) As EndOfWeek;

SELECT * FROM [tempdb].[dbo].[fn_GetDateRangeforWeek] ('5/15/2010');
SELECT * FROM [tempdb].[dbo].[fn_GetDateRangeforWeek] ('9/1/2006');
SELECT * FROM [tempdb].[dbo].[fn_GetDateRangeforWeek] ('10/22/2010');

Results of Example Code

[tt]
StartOfWeek EndOfWeek
----------------------- -----------------------
2010-12-26 00:00:00.000 2011-01-01 00:00:00.000

StartOfWeek EndOfWeek
----------------------- -----------------------
2010-05-09 00:00:00.000 2010-05-15 00:00:00.000

StartOfWeek EndOfWeek
----------------------- -----------------------
2006-08-27 00:00:00.000 2006-09-02 00:00:00.000

StartOfWeek EndOfWeek
----------------------- -----------------------
2010-10-17 00:00:00.000 2010-10-23 00:00:00.000
[/tt]

Hope this may be of use.

Thanks

John Fuhrman

 

[sparkbyte]

And just for fun, to use George's code example with the function.

Declare @Temp Table(CreateDate DateTime, PizzasEaten Int)

insert into @temp Values('2011-02-25', 2)
insert into @temp Values('2011-02-26', 1)
insert into @temp Values('2011-02-27', 0)
insert into @temp Values('2011-02-28', 3)
insert into @temp Values('2011-03-01', 5)
insert into @temp Values('2011-03-02', 8)
insert into @temp Values('2011-03-03', 4)
insert into @temp Values('2011-03-04', 10)
insert into @temp Values('2011-03-05', 1)
insert into @temp Values('2011-03-06', 4)
insert into @temp Values('2011-03-07', 6)

;with cte1 As (
Select	
		(Select StartOfWeek FROM [dbo].[fn_GetDateRangeforWeek] (CreateDate)) As StartOfWeek,
		(Select EndOfWeek FROM [dbo].[fn_GetDateRangeforWeek] (CreateDate)) As EndOfWeek,
		PizzasEaten
From   @Temp )

	Select 
		StartOfWeek, 
		EndOfWeek, 
		Sum(PizzasEaten) As PizzaCount
	From cte1
		Group By StartOfWeek, EndOfWeek

Results.

[tt]
StartOfWeek EndOfWeek PizzaCount
----------------------- ----------------------- -----------
2011-02-20 00:00:00.000 2011-02-26 00:00:00.000 3
2011-02-27 00:00:00.000 2011-03-05 00:00:00.000 31
2011-03-06 00:00:00.000 2011-03-12 00:00:00.000 10
[/tt]


Thanks

John Fuhrman

 

[gmmastros]

John,

If you are using SQL2005 (or newer), you may want to try using Cross Apply for that query, like this:

Declare @Temp Table(CreateDate DateTime, PizzasEaten Int Primary Key (CreateDate, PizzasEaten))

insert into @temp Values('2011-02-25', 2)
insert into @temp Values('2011-02-26', 1)
insert into @temp Values('2011-02-27', 0)
insert into @temp Values('2011-02-28', 3)
insert into @temp Values('2011-03-01', 5)
insert into @temp Values('2011-03-02', 8)
insert into @temp Values('2011-03-03', 4)
insert into @temp Values('2011-03-04', 10)
insert into @temp Values('2011-03-05', 1)
insert into @temp Values('2011-03-06', 4)
insert into @temp Values('2011-03-07', 6)

Select X.StartOfWeek, 
       X.EndOfWeek, 
       Sum(T.PizzasEaten) As PizzaCount
From   @Temp T 
       Cross Apply [dbo].[fn_GetDateRangeforWeek](T.CreateDate) As X
Group By X.StartOfWeek, X.EndOfWeek

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[sparkbyte]

Thanks George!! I will read up on cross apply.

Thanks

John Fuhrman