How to arrange data Week & Month Wise

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)

How to arrange data Week & Month Wise

Author

Topic

real_pearl
Posting Yak Master

106 Posts

Posted - 2004-06-21 : 02:29:07

If I have to display data week & month wise what query should I write suppose

I have data

Program_ID, Program_Name, Finish_Date
1 A 01-04-2004
2 B 01-04-2004
3 C 02-04-2004
4 D 03-04-2004
5 E 15-04-2004
6 F 23-04-2004

Now I want to display data as

Week Program_ID Program_Name
1 1 A
1 2 B
1 3 C
1 4 D
2 5 E
3 6 F

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-06-21 : 03:18:36

I disagree with your results - shouldn't the 15th be in the 3rd week and the 23rd in the 4th week?

This should do it.

select (day(Finish_Date) + 6) / 7 as week, ProgramID, ProgramName
from Programs

Duane.

vganesh76
Yak Posting Veteran

64 Posts

Posted - 2004-06-21 : 03:53:35

Try if this works out,

select case when (day(Finish_Date) /7.0 ) < 1.0 then 1
else day(Finish_Date) /7 end week ,
Program_ID,Program_Name
from (
select 1 Program_ID, 'A' Program_Name, '2004-04-01' Finish_Date union
select 2, 'B', '2004-04-01' union
select 3, 'C','2004-04-02' union
select 4, 'D', '2004-04-03' union
select 5, 'E', '2004-04-15' union
select 6, 'F','2004-04-23' ) as t

Ganesh V.

Enjoy working

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources