Need Help in SQL Server 2000 - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Need Help in SQL Server 2000

Author

Topic

waheed245
Starting Member

1 Post

Posted - 2008-09-21 : 01:12:08

I need help regarding the following.
CREATE Procedure Get_MonthlyReport
@sd varchar(20),
@ed varchar(20),
@TrainID int
As
if @TrainID = 0
Begin
select
distinct Trainings.TrainId,
Trainings.TopiCID
from Trainings with(nolock)
where
Trainings.startdate >= @sd and
Trainings.EndDate <= @ed
order by Trainings.TopiCID

ENd
Else If @TrainID > 1
Begin
select
distinct Trainings.TrainId,
Trainings.TopicId,
Topic = (select dbo.FN_TopicName(Trainings.TopicId)),
Sdate = Convert(varchar(12),Trainings.StartDate ,107),
Edate = Convert(varchar(12),Trainings.EndDate ,107),
cntAtt = (select dbo.FN_GetTrainingCount(Trainings.TrainId)),
Dur = Trainings.Duration,
TrainerDept = (select dbo.FN_GetSubDept(Trainers.EmployeeID,Type)),
Trainer = (select dbo.FN_TrainerName(Trainings.TrainerId)),
mins = (select cast(Trainings.Duration/60 as varchar(5)) + ':' + RIGHT('0' + cast(Trainings.Duration%60 as varchar(2)), 2)) ,
hrs = (
(select cast(
Trainings.Duration * (select dbo.FN_GetTrainingCount(Trainings.TrainId) )
as float)) / 60)

from Trainings with(nolock), Trainers with(nolock)
where
Trainers.TrainerId = Trainings.TRainerId and
Trainings.TrainId = @TrainID
--Trainings.startdate >= '2008-02-02' and
--Trainings.EndDate <= '2008-02-05'
order by Trainings.TopiCID
End
GO
This query generates the following result:

Monthly Report
From: 7/1/2008 To: 7/31/2008
Topic Id Topic Start Date End Date Attendees Duration (min) Trainer Department Trainer Hours Total Hours

116 How to Clean Slicer Machine ? Jul 10, 2008 Jul 10, 2008 13 60 F. & B. - Steward Staff KalakkanDathil Mohammed 13

116 How to Clean Slicer Machine ? Jul 10, 2008 Jul 10, 2008 11 60 F. & B. - Steward Staff AhmadZackaria Mohamed Nazeer 11

164 How to Clean Toaster Machine Jul 11, 2008 Jul 11, 2008 11 60 F. & B. - Steward Staff AhmadZackaria Mohamed Nazeer 11

164 How to Clean Toaster Machine Jul 11, 2008 Jul 11, 2008 10 60 F. & B. - Steward Staff KalakkanDathil Mohammed 10

175 How to Handle Guest Complaints Jul 05, 2008 Jul 05, 2008 2 60 F. & B. - Service Staff KhaledMohamed Mohamed El Nady 2

281 Safelock Maintenance & Troubleshooting Jul 27, 2008 Jul 27, 2008 23 60 R&M -Technician Abdul mananMuhammad Younas 23

543 Safety Work Jul 20, 2008 Jul 20, 2008 13 90 R&M -R. & M. Helpers AL SAYEDABOZAID AL SEBAEI 19.5

544 Key Cutting Jul 26, 2008 Jul 26, 2008 22 60 R&M -Technician Abdul mananMuhammad Younas 22

545 Fire Fighting Jul 28, 2008 Jul 28, 2008 43 120 R&M -Engineers Muhammad AamirRiaz Riaz 86

546 How to Clean Garbage Bin Jul 12, 2008 Jul 12, 2008 21 60 F. & B. - Steward Staff KalakkanDathil Mohammed 21

547 Oasys Attendance Jul 23, 2008 Jul 23, 2008 15 120 I.T. Manager & Assistant KhaledMostafa Al Shafi 30

548 Food Hygiene (HACCP) Jul 07, 2008 Jul 07, 2008 26 180 External Abdul Majeed 78

549 Safety & Security Jul 23, 2008 Jul 23, 2008 13 60 H.K. Housekeeper & Assistants AhmedYahya Mohammed 13

549 Safety & Security Jul 16, 2008 Jul 16, 2008 17 60 H.K. Room Attendants MohammadQasim Nasoor 17

Now I need to generate another report. Which will produce the the following details:
Trainer Name, Duration, Attendees, Hours (Duration * Attendees)
What I need now is to select trainer as distinct. But add all the trainings conducted by one trainer and show it as single. For example
Topic Id Topic Start Date End Date Attendees Duration (min) Trainer Department Trainer Hours Total Hours
281 Safelock Maintenance & Troubleshooting Jul 27, 2008 Jul 27, 2008 23 60 R&M -Technician Abdul mananMuhammad Younas 23
544 Key Cutting Jul 26, 2008 Jul 26, 2008 22 60 R&M -Technician Abdul mananMuhammad Younas 22
These two trainings are conducted by the same trainer for the specified date range. So what I need is to add these two trainings conducted by a trainer and show them as single. But it should add the all the attendees for this trainer and also the duration for this trainer and then it should display the total hours.
Waiting for your support.

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-09-21 : 01:45:01

I think I want to take this class: Safelock Maintenance & Troubleshooting

always wanted to know how to crack safes. How to Clean Garbage Bin? not so much.

elsasoft.org

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-21 : 02:21:30

quote:
Originally posted by waheed245

Now I need to generate another report. Which will produce the the following details:
Trainer Name, Duration, Attendees, Hours (Duration * Attendees)
What I need now is to select trainer as distinct. But add all the trainings conducted by one trainer and show it as single. For example
Topic Id Topic Start Date End Date Attendees Duration (min) Trainer Department Trainer Hours Total Hours
281 Safelock Maintenance & Troubleshooting Jul 27, 2008 Jul 27, 2008 23 60 R&M -Technician Abdul mananMuhammad Younas 23
544 Key Cutting Jul 26, 2008 Jul 26, 2008 22 60 R&M -Technician Abdul mananMuhammad Younas 22
These two trainings are conducted by the same trainer for the specified date range. So what I need is to add these two trainings conducted by a trainer and show them as single. But it should add the all the attendees for this trainer and also the duration for this trainer and then it should display the total hours.
Waiting for your support.

when you show them as single which Topic,startdate,enddate data you want return with it (obviously you can only single value along with summary) or do you want to remove all of them and show only the total values?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources