April 20, 2005 at 3:56 pm
Hello,
I have two tables from which I want to query info.
classinfo
classid...title... location... classlength... startdate...enddate...facilitator
1 classname training rm#1 1.5 4/1/2005 4/5/2005 John Q.
2 clsname2 training rm#2 3 4/6/2005 4/8/2005 Suzy Q.
3.........
And ...
trainrec
classid.... employeeid....
1 424
2 424
3 229
4 229
1 229
2 229
5 121
etc...
Classinfo is just class information table and the trainrec is a bridge (associate) to the employee table and the classid table...
From these tables I need to find the total number of employees with less than 6 hours of training, 6 to 18 hours of training and more than 18 hours of training. I'm able to get the sum of training by groups of employeeids. for this I use:
SELECT SUM(classinfo.classlength) AS classlength, trainrec.employeeid
FROM classinfo
INNER JOIN trainrec
ON classinfo.classid = trainrec.classid
GROUP BY trainrec.employeeid
... This gives me something like this
sum... employeeid(grouped)...
3 211
4 223
1 272
Now I need to count the employees with these totals < 6, >= 6 and <=18, > 18.
The kicker to all this is I need to do this by startdate. So, from these tables I need to find total hours by employeeid by date.
Once I get this done I'm going to put in a sproc and provide it to the webmaster for query in classic asp.
Any suggestions?
Thanks for anything you have...
April 21, 2005 at 1:40 am
Just what does it mean "by date"? In case you need to find out what the totals were at a certain date, then it shouldn't be too complicated. BTW, you didn't mention DDL, so I supposed that startdate is a DATETIME datatype. @date would be a parameter of your SP... here I declared it to test the functionality.
create table classinfo(classid int, classlength float, startdate datetime)
create table trainrec (classid int, employeeid int)
insert into classinfo values (1, 1.5, '20050401')
insert into classinfo values (2, 3, '20050406')
insert into classinfo values (3, 9, '20050402')
insert into trainrec values (1,424)
insert into trainrec values (2,424)
insert into trainrec values (3,229)
insert into trainrec values (4,229)
insert into trainrec values (1,229)
insert into trainrec values (2,229)
insert into trainrec values (5,121)
insert into trainrec values (1,424)
DECLARE @date datetime
SET @date = '20050402'
SELECT Q.trainlevel, count(*)
FROM
(SELECT t.employeeid, SUM(c.classlength) AS length,
CASE WHEN SUM(c.classlength) < 6 THEN '<6'
WHEN SUM(c.classlength) <=18 THEN '6-18'
ELSE '>18' END as trainlevel
FROM classinfo c
JOIN trainrec t ON c.classid = t.classid AND c.startdate <= @date
GROUP BY t.employeeid ) AS Q
GROUP BY Q.trainlevel
If "by date" meant something different, please elaborate on the explanation a bit.
April 21, 2005 at 1:48 am
use northwind
select
sum(case when isnull(freight,0)=0 then 1 else 0 end) as 'ohne Fracht'
, sum(case when isnull(freight,0)>0 and isnull(freight,0)<18 then 1 else 0 end) as 'unter 18'
, sum(case when isnull(freight,0)>=18 and isnull(freight,0)<24 then 1 else 0 end) as 'zwischen 18 und 24'
, sum(case when isnull(freight,0)>=24 and isnull(freight,0)<65 then 1 else 0 end) 'zwischen 24 und 65'
, sum(case when isnull(freight,0)>=65 then 1 else 0 end) 'über 65'
from
orders
ohne Fracht unter 18 zwischen 18 und 24 zwischen 24 und 65 über 65
----------- ----------- ------------------ ------------------ -----------
0 253 48 236 293
(1 row(s) affected)
Just put your query in the FROM clause, modify the ranges you want to be counted. That should be it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply