how to get a timetable like layout using c#

Hi Experts,
I have few tables in SQL Server 2005
Category(CatID,Desc,Status)
Subject(SubID,Desc,Status,CatID)
Employee(EmpID,Firstname,Surname,etc).
EmployeeSubjectHours(EmpID,SubID,NoOfHours)
I want to create a from with the layout added in a code snippet. A category is selected then display no of hours an employee has been trained on a particular subject.  How can i acheive this kind of layout using asp.net. I am using vs 2005 with SQL Server 2005.
I will appreciate any help either ideas or examples



Select Category	Dropdownlist
 
	         Sub1	Sub2     Sub3     Total
Employee 1	5.00	4.00	3.00	12.00
Employee 2	8.00	9.00	2.00	19.00
Employee 3	4.00	4.50	3.50	12.00

Open in new window

mmalik15Asked:
Who is Participating?
 
tigin44Connect With a Mentor Commented:


First of all you can not give the column name as colum alias as you have written...

Second.. if number of subject changes then you can use a dymanic query to produce the result set e.g.

CURSOR cSubject  FOR AS
   SELECT SubID, Desc
   FROM Subject

DECLARE @SubID   int,  @Desc   varchar(100)
DECLARE @sql   varchar(8000)

SET @sql   = 'SELECT E.EmpID,E.Firsname,E.lastname'

OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS <> 0)
BEGIN
    SET @sql   = @sql  + ', '
   SET @sql   = @sql  + ' SUM(CASE ES.'+ SubID +' WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ' +  @Desc

   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  

SET @sql   = @sql  + ', SUM(ES.NoOfHours) AS Total '+
     'FROM  Category C '+
      'INNER JOIN Subject S ON C.CatID = S.CatID '+
      'INNER JOIN EmployeeSubjectHours ES ON S.SubID = ES.SubID '
      'INNER JOIN Employee E ON ES.EmpID = E.EmpID '+
   'GROUP BY E.EmpID,E.Firsname,E.lastname '

EXEC (@sql)

0
 
tigin44Commented:

You can obtain  alist like you want by using a pivot query like the one below...


SELECT E.EmpID,E.Firsname,E.lastname,
      SUM(CASE ES.SubID WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS Sub1,
      SUM(CASE ES.SubID WHEN 2 THEN ES.NoOfHours ELSE 0 END) AS Sub2,
      SUM(CASE ES.SubID WHEN 3 THEN ES.NoOfHours ELSE 0 END) AS Sub3,
      SUM(CASE ES.SubID WHEN 4 THEN ES.NoOfHours ELSE 0 END) AS Sub4,
      SUM(CASE ES.SubID WHEN 5 THEN ES.NoOfHours ELSE 0 END) AS Sub5,
      SUM(ES.NoOfHours) AS Tolat


FROM  Category C
      INNER JOIN Subject S ON C.CatID = S.CatID
      INNER JOIN EmployeeSubjectHours ES ON S.SubID = ES.SubID
      INNER JOIN Employee E ON ES.EmpID = E.EmpID
GROUP BY E.EmpID,E.Firsname,E.lastname
0
 
mmalik15Author Commented:
Excellent tigin44 you are  a SQL genius. I really appreciate your comment.
There are little things needed to be corrected. First on above query we are iterating on five subjects but subjects can be many and we have to calculate no of subjects at run time, how can set the no of subjects at runtime.
Secondly i tried to get Subject titles rather sub1 or sub2. I tried to add the titles like
SUM(CASE ES.SubID WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ES.SubTitle, but it gives me an error how can we get around this error.
Thanks again






0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
mmalik15Author Commented:
Thanks sir,

For some reasons its not executing the cursor part

I have m ade some changes to table names and field names  and it looks like this.

Also i have printed @sql and it gives me query like this in snippet

SELECT E.EmployeeID,E.Firstname,E.lastname, SUM(ES.NoOfHours) AS Total FROM  tblGradlAcctTrainingCategories C INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID GROUP BY E.EmployeeID,E.Firstname,E.lastname

It does not give me the subject details at all.







DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject
 
DECLARE @SubID   int,  
@Desc   varchar(100)
DECLARE @sql   varchar(8000)
 
SET @sql   = 'SELECT E.EmployeeID,E.Firstname,E.lastname'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS <> 0)
BEGIN
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + ' SUM(CASE ES.'+ @SubID +' WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ' +  @Desc
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', SUM(ES.NoOfHours) AS Total '+
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
      'INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
      'GROUP BY E.EmployeeID,E.Firstname,E.lastname '
--print @sql
 
EXEC (@sql)

Open in new window

0
 
mmalik15Author Commented:
I have solved one problem but the there is a syntax error i am getting now

Syntax error converting the varchar value 'SELECT E.EmployeeID,E.Firstname,E.lastname, SUM(CASE ' to a column of data type int.


DECLARE cSubject CURSOR
   For SELECT SubjectID, Subject
   FROM tblGradAcctTrainingSubject
 
 
DECLARE @SubID   int,  
@Desc   varchar(100)
DECLARE @sql   varchar(8000)
 
SET @sql   = 'SELECT E.EmployeeID,E.Firstname,E.lastname'
 
OPEN cSubject  
FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
WHILE(@@FETCH_STATUS = 0)
BEGIN
   SET @sql   = @sql  + ', '
   SET @sql   = @sql  + ' SUM(CASE ES.'+ @SubID +' WHEN 1 THEN ES.NoOfHours ELSE 0 END) AS ' +  @Desc
 
   FETCH NEXT FROM cSubject   INTO @SubID,  @Desc
END
CLOSE cSubject  
DEALLOCATE cSubject  
 
SET @sql   = @sql  + ', SUM(ES.NoOfHours) AS Total '+
     'FROM  tblGradlAcctTrainingCategories C '+
      'INNER JOIN dbo.tblGradAcctTrainingSubject S ON C.CatID = S.CatID '+
      'INNER JOIN tblGradAcctTrainingHours ES ON S.SubjectID = ES.SubjectID ' +
      'INNER JOIN dbo.tblEmployee E ON ES.EmployeeID = E.EmployeeID '+
      'GROUP BY E.EmployeeID,E.Firstname,E.lastname '
--print @sql
 
EXEC (@sql)

Open in new window

0
 
mmalik15Author Commented:
Great
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.