how to do this select - SQL Server Forums

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

how to do this select

Author

Topic

nice123ej
Starting Member

48 Posts

Posted - 2011-01-02 : 06:48:58

Hi
I have table that has list of students and homeworks per week
This is my table

HomeWorks (
StNo INT,
WeekName Varchar(50),
HomeworksNo INT
)

when i do this select SELECT * FROM HomeWorks

I get this result

2009001 Monday 2
2009001 Tuesay 1
2009001 Thursday 3
2010029 Thursday 2
2010029 Friday 2
2010157 Monday 4
2010157 Wednesday 1

what i want is to get all week days for each student with 0 in thier homework no
something like this

2009001 Monday 2
2009001 Tuesay 1
2009001 Wednesday 0
2009001 Thursday 3
2009001 Friday 0
2010029 Monday 0
2010029 Tuesday 0
2010029 Wednesday 0
2010029 Thursday 2
2010029 Friday 2
2010157 Monday 4
2010157 Tuesay 0
2010157 Wednesday 1
2010157 Thursday 0
2010157 Friday 0

what i tried to do is to create a Temp Table @WeekDays (WeekName varchar(50))
and Inserted all Week Days in it (Monday, Tuesday, Wednesday, Thursday, Friday)

and outer join it with HomeWorks table but did not work with me

I want the optimum solution for this scenario

can someone help me here?!

Thanks

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-01-02 : 08:25:16

;with dw as
(
select dw = 'Monday'
union all select 'Tuesday'
union all select 'Wednesday'
union all select 'Thursday'
union all select 'Friday'
),
StNo as
(
select distinct StNo from HomeWorks)
)
select d.StNo, dw.dw, coalesce(h.HomeworksNo, 0)
from dates dw
cross join days d
left join HomeWorks h
on h.StNo = d.StNo
and h.WeekName = dw.dw

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources