Simple Query To return 'Week of"

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

Transact-SQL (2005)

Simple Query To return 'Week of"

Author

Topic

JohnBGood
Starting Member

48 Posts

Posted - 2010-02-20 : 21:22:47

I have data that looks like this

TimebyDay, TimeYear, TimeWeekofYear
1/1/2002, 2002, 1
1/2/2002, 2002, 1
1/3/2002, 2002, 1
1/4/2002, 2002, 1
1/5/2002, 2002, 1
1/6/2002, 2002, 1
1/7/2002, 2002, 1
1/8/2002, 2002, 2
1/9/2002, 2002, 2

--and so on

I want to return

1/1/2002, 1
1/8/2002, 2
1/15/2002, 3

--and so on

Help much appreciated

X002548
Not Just a Number

15586 Posts

Posted - 2010-02-20 : 23:09:32

Look up DATEDIFF ??

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 02:52:53

[code]SELECT t.*
FROM Table t
INNER JOIN (SELECT TimeYear,TimeWeekofYear,MIN(TimebyDay) AS First
FROM Table
GROUP BY TimeYear,TimeWeekofYear) t1
ON t1.TimeYear=t.TimeYear
AND t1.TimeWeekofYear = t.TimeWeekofYear
AND t1.First = t.TimebyDay[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-02-21 : 03:21:29

[code]SELECT TimeWeekofYear,
MIN(TimebyDay) AS TimebyDay
FROM Table1
GROUP BY TimeWeekofYear
ORDER BY TimeWeekofYear[/code]

N 56°04'39.26"
E 12°55'05.63"

JohnBGood
Starting Member

48 Posts

Posted - 2010-02-21 : 07:28:32

That did it!! Thanks You!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-21 : 08:49:14

quote:
Originally posted by JohnBGood

That did it!! Thanks You!!

which suggestion worked for you? Mine or Peso's?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources