display yearly data into no of weeks

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

General SQL Server Forums

New to SQL Server Programming

display yearly data into no of weeks

Author

Topic

Mahesh_1986
Starting Member

2 Posts

Posted - 2012-03-02 : 01:59:38

Hi team,

display yearly data into no of weeks. for Ex
Table

Date Name Emp_code Number Team Leader
1/1/2012 A 1 2 A
2/1/2012 B 2 3 B
10/1/2012 C 3 45 A
15/1/2012 D 4 6 B
16/1/2012 E 5 7 C
31/1/2012 F 6 8 C
2/2/2012 G 7 9 D
12/2/2012 H 8 10 E
13/2/2012 I 9 11 E
14/2/2012 J 10 12 J

RESULT SHOULD BE LIKE
Week start from Friday to Thursday.

Week Name Emp_code Number Team1 Jan 2012 to 5 Jan 2012 A 1 2 A
1 Jan 2012 to 5 Jan 2012 B 2 3 B
6 Jan 2012 to 12 Jan 2012 C 3 90 A
13 Jan 2012 to 19 Jan 2012 D 4 6 B
13 Jan 2012 to 19 Jan 2012 E 5 7 C
20 Jan 2012 to 26 Jan 2012
27 Jan 2012 to 31 Jan 2012 F 6 8 C
1 feb 2012 to 1 feb 2012
2 feb 2012 to 8 feb 2012 G 7 9 D
9 feb 2012 to 15 feb 2012 H 8 10 E
9 feb 2012 to 15 feb 2012 I 9 11 E
9 feb 2012 to 15 feb 2012 J 10 12 J

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2012-03-02 : 04:49:29

try grouping your data like below, will give you the weekly data
dateadd(week,datediff(week,0, YourDateField), 0)

@Sathiesh

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-03-02 : 05:00:52

quote:
Originally posted by sathiesh2005

try grouping your data like below, will give you the weekly data
dateadd(week,datediff(week,0, YourDateField), 0)

RESULT SHOULD BE LIKE
Week start from Friday to Thursday.

DATEADD(DAY, DATEDIFF(DAY, '19000105', YourDateField) / 7 * 7, '19000105')

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

sathiesh2005
Yak Posting Veteran

85 Posts

Posted - 2012-03-02 : 05:07:12

I did not noticed that Week start from Friday to Thursday.
can do like this also:
dateadd(week, datediff(week,0, YourDateField), 0)-3

quote:
Originally posted by SwePeso

quote:
Originally posted by sathiesh2005

try grouping your data like below, will give you the weekly data
dateadd(week,datediff(week,0, YourDateField), 0)

RESULT SHOULD BE LIKE
Week start from Friday to Thursday.

DATEADD(DAY, DATEDIFF(DAY, '19000105', YourDateField) / 7 * 7, '19000105')

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

@Sathiesh

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources