[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

how to get a timetable like layout using c#

Posted on 2008-11-06
6
Medium Priority
?
429 Views
Last Modified: 2013-12-17
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

0
Comment
Question by:mmalik15
  • 4
  • 2
6 Comments
 
LVL 26

Expert Comment

by:tigin44
ID: 22893889

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
 

Author Comment

by:mmalik15
ID: 22894324
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
 
LVL 26

Accepted Solution

by:
tigin44 earned 2000 total points
ID: 22895187


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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:mmalik15
ID: 22895663
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
 

Author Comment

by:mmalik15
ID: 22895948
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
 

Author Closing Comment

by:mmalik15
ID: 31513823
Great
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question