Getting first date from Year + Week Number? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2000 Forums

Transact-SQL (2000)

Getting first date from Year + Week Number?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-21 : 18:50:37

A week or two ago, some kind soul here helped me put together a query to tabulate the total number of posts and replies from a snitz forums database. Here's the query:

SELECT A.Year, A.Week, Count(*) Posts 
FROM 
(SELECT datepart(year,datetime) Year, datepart(wk,datetime) Week 
FROM snitz_topics 
UNION ALL 
SELECT datepart(year,datetime) Year, datepart(wk,datetime) Week 
FROM snitz_reply) AS A 
GROUP BY A.Year, A.Week 
ORDER BY A.Year, A.Week

This yeilds results like:


Year   Week   Posts
----   ----   ----
2001   52     400
2002   1      600
2002   2      700
2002   3      800

However, that's tough to graph. Can anyone think of a clever way to convert the Year/Week columns into a single datetime column that represents "Week of...". IE:


Date          Posts
------------  -----
12/26/2001    400
1/2/2002      600
1/9/2002      700
1/16/2002     800

...Thanks in advance
-b

jbkayne
Posting Yak Master

100 Posts

Posted - 2002-05-21 : 19:11:27

select dateadd("ww",week - 1,convert(varchar(4),year) + '-01-01 00:00:00.000') from
(
select 2001 as year, 52 as week
union
select 2002, 1
union
select 2002, 2
union
select 2002, 3
) as summary

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-21 : 19:25:50

Thank you!

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources