asked on

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

tigin44

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

mmalik15

ASKER

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

ASKER CERTIFIED SOLUTION

tigin44

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mmalik15

ASKER

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

mmalik15

ASKER

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

mmalik15

ASKER

Great