Using Aggregate function in data environments in VB6 with SQL 1

[TheOneRing]

I have a database that has a number of tables in it.

tblServiceRequest - requests for service (custid etc)
tblServiceRequestType - service request names(eg: WORKSHOP)
tblServiceTask - the tasks for each request (ie. PCREPAIR)
tblServiceTaskType - info for each task (i. labour hrs etc)

What I want to be able to do is a report for a give date range that will give me the labour dollars (from tblServiceTask) for each ofthe requests type and the average over the month. I use the following SQL Statement :

SELECT DISTINCT srt.strServicecode,sr.strBillTo,st.strServiceTaskNumber,st.dblbilledparts,st.dblbilledlabour,st.dblbilledtravel, st.dblpartscost,dtmservicetaskenddate,stt.dblbilledlabourhours,stt.dblbilledtravelhours FROM ((tblServiceRequestType as srt INNER JOIN tblServiceRequest as sr ON srt.lngServiceRequestTypeID = sr.lngServiceRequestTypeID) INNER JOIN tblServiceTask as st ON sr.lngServiceRequestID = st.lngServiceRequestID) INNER JOIN tblServiceTaskTech as stt ON st.lngServiceTaskID = stt.lngServiceTaskID WHERE (((dtmServiceTaskEndDate) >= CDate('01-02-02') And (dtmServiceTaskEndDate) <= CDate('28-02-02')))

The problem is that when I go into this dataenvironment and set grouping on strServiceCode and then do aggregates on dblbilledlabour, travel etc, it doesn't give me all the totals. Executing this SQL statement on it's own produces about 230 records. But when I check the recordcount in the data environment I get 17 records. What is the best way to do this kind of report (sample below)

REQUEST TYPE # CALLS LBRHR TRVHR LBR$ TRV$
COMPAQC 10 4.5 10.3 $100 $300
ASI 1 1.24 0 $ 90
IPEX 3 2.3 22.2 $890 $400

Your help would be very much appreciated as I am having much trouble with this report and others like it.

thanks,
Darren

 

[rac2]

SELECT srt.strServicecode AS &quot;REQUEST TYPE&quot;,
COUNT(*) AS &quot;# Calls&quot;,
SUM(stt.dblbilledlabourhours) AS &quot;LBRHR&quot;,
AVG(stt.dblbilledlabourhours) AS &quot;AvgLBHR&quot;

FROM (SELECT * FROM tblServiceTask
WHERE (dtmServiceTaskEndDate >= CDate('01-02-02') AND dtmServiceTaskEndDate <= CDate('28-02-02')
) st

JOIN tblServiceTaskTech as stt
ON st.lngServiceTaskID = stt.lngServiceTaskID

JOIN tblServiceRequest sr ON st.lngServiceRequestID = sr.lngServiceRequestID

JOIN tblServiceRequestType srt
ON srt.lngServiceRequestTypeID = sr.lngServiceRequestTypeID

GROUP BY srt.strServicecode

I start with a subquery to retrieve tasks completed during the timeperiod and give that the alias st. I used SELECT * because I don't know the names of columns in the service task table. You can specify columns or not, it doesn't matter so long as you mention all of the columns used in the other clauses.

I don't think you need to worry about the INNER JOINs to get summary data for the types of tasks that actually were performed during the time period.

I assumed that you can join service requests to service tasks on lngServiceRequestID. That is why I highlighted the table aliases in red for that join.

You can add aggregate functions for the other variables to the SELECT list.