Daily Schadule of Student Query

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

Transact-SQL (2008)

Daily Schadule of Student Query

Author

Topic

hamid.y
Starting Member

22 Posts

Posted - 2010-05-24 : 12:27:17

hi experts,
i have a table with these fields:
(Student,Course,Day,Time)

Day is an integer evaluated with 0-5 for each day of week
for example 0 = Monday and 1 is equal to Tuesday and so on.

Time is also the same but
0 is equal to 8-10 A.M, 1 is equal to 10-12 A.M and so on.

now I wanna select each student's Schedule like the following:
==========================================================
| 8-10 | 10-12 | 12-14 | 14-16 | ......
-------|-------|---------|-------|-------|--------
monday | |DataBase | | |
-------|-------|---------|-------|-------|--------
Tuesday| art | | Math | |
-------|-------|---------|-------|-------|--------
. | | | | |
.
.
.
.
.
.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-24 : 14:56:59

SQL Server programming is very robust and powerful. It is possible to use PIVOT to create a recordset similar to what you request, however each "timeslot" would have a distinct value and it is not possible to span columns with a single value. In your example, the Monday schedule could list Database twice (once for 8-10, once for 10-12) but not one value spanning both 8-10 and 10-12.

It's worth noting that this quote from Jurassic Park is apt: "[you] are so preoccupied with whether or not [sql server] could, [you] didn't stop to think if [it] should". Specifically, formating data like this is the job of your presentation layer -- whether a reporting engine like SSRS or Crystal or a high level language (web or otherwise) like ASP.NET, PHP, etc.. This is because databases are expensive to scale and are apt to become the bottleneck of your application, whereas the presentation layer is typically much easier and cheaper to scale and are generally more suited to this type of workload.

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2010-05-24 : 19:02:46

Nice use of the Jurassic Park quote...

=======================================
A couple of months in the laboratory can save a couple of hours in the library. -Frank H. Westheimer, chemistry professor (1912-2007)

hamid.y
Starting Member

22 Posts

Posted - 2010-05-28 : 02:07:34

hi again,
thanks for you advices but my professor do that really easily.
its the way he solve it:

he just create a new table like this:
Day(Dayid int, DayTitle nvarchar(30))

then

create function getCname(@StdID int,@Dayid int,@TimeID int)
returns varchar(20) as
begin
declare @Cname varchar(30)
if(select Count(*) from Selection where StdID = @StdID and Dayid = @Dayid) = 1)
set @Cname = (select Cname from Selection Where StdID = @StdID and Dayid = @Dayid)

else

set @Cname = null

return @Cname
end

and another procedure as:

create proce GetTimeSheet
@StdID int as
select DayTitle,GetCname(@StdID,Dayid,1) '8-10',
GetCname(@StdID,Dayid,2) '10-12',
GetCname(@StdID,Dayid,3) '12-14',
GetCname(@StdID,Dayid,4) '14-16',
GetCname(@StdID,Dayid,5) '16-18',
GetCname(@StdID,Dayid,6) '18-20',
from day
GO

just very very easy.
I believe in him.

end

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2010-05-28 : 10:48:29

Your professor provided a solution, but it is not very efficient. In the real world, following this pattern will only get you in trouble.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources