| Author |
 Topic  |
|
|
AskSQLTeam
Ask SQLTeam Question
USA
0 Posts |
 Posted - 12/04/2000 : 08:57:54
|
Bob writes "I'm building a report that requires I total fields based on various criteria. I've very recently learned data shaping so I'm not certain I'm doing this as efficiently as I might but it is working so far. My problem: I need to sum yet another field based on a boolean field in another table.
I am trying to calculate the total price of equipment used per project where the bit field Equipment.IsEquipment = 1. I can do so progrmatically by looping through the child recordset but I'm certain I should be able to do so in the SQL statement. I'm afraid I've overreached my understanding of data shaping. I've attempted a join within the shape but apparently don't understand enough of the syntax to make it work.
I'm working on an existing application that I can't change the data model in much. Here is the data model as it applies to this query.
Tables:
Customer - individual company information
Projects - project info with a foreign key to the Customer table
ProjectDetail - line items for each project detailing equipment/quantity with a foreign key to the Projects table and another to the Equipment table
Equipment - individual equipment/supply options with pricing and unit of measure information
Relationships:
Customer.CustomerID -> Projects.CustomerID
Projects.ProjectID -> ProjectDetail.ProjectID
ProjectDetail.EquipID -> Equipment.EquipID
Equipment Table:
EquipID
Rate
IsEquipment
This is the working SQL I have so far:
sql="SHAPE {Select Company, CustomerID from Customer}"&_
" APPEND("&_
" (SHAPE {Select *,"&_
" (Select sum(hours) from ProjectDetail where ProjectDetail.ProjectID = Projects.ProjectID) as sumHours,"&_
" (Select sum(oth) from ProjectDetail where ProjectDetail.ProjectID = Projects.ProjectID) as sumOTH,"&_
" (Select sum(oth) from ProjectDetail where ProjectDetail.ProjectID = Projects.ProjectID and potr > 0 ) as sumOT,"&_
" (Select sum(oth) from ProjectDetail where ProjectDetail.ProjectID = Projects.ProjectID and potr = 0 ) as sumDH,"&_
" ((Select sum(hours) from ProjectDetail where ProjectDetail.ProjectID = Projects.ProjectID) + ((Select sum(oth) from ProjectDetail where potr = .5 and ProjectDetail.ProjectID = Projects.ProjectID)/2) + (Select sum(oth) from ProjectDetail where potr = 1 and ProjectDetail.ProjectID = Projects.ProjectID)*1) as sumLabor"&_
" from Projects}"&_
" APPEND("&_
" {Select * from ProjectDetail WHERE date >= '" & request.form("sdate") & "' and date <= '" & request.form("edate") & "'} as DetailList"&_
" RELATE ProjectID TO ProjectID))AS ProjectList"&_
" RELATE CustomerID to CustomerID)"
I've tried various themes along the lines of
Select ProjectDetail.Units, Equipment.Rate, Equipment.IsEquipment
from ProjectDetail join Equipment on ProjectDetail.EquipID = Equipment.EquipID
and trying to calculate ProjectDetail.Units*Equipment.Rate where Equipment.IsEquipment=1 but have been unsuccesful.
Any light you can shed on this will be greatly appreciated! This is the first project that's ever stumped me and it's stumped me good." |
|
|
robvolk
Most Valuable Yak
USA
15559 Posts |
Posted - 12/04/2000 : 22:35:12
|
Bob-
I'm assuming SQL Server, so you might have to modify the syntax for Oracle or Access. I might miss some details, but hopefully it'll make sense to you and you can fill in the blanks.
First, if you're not familiar with the CASE statement, FOR GOD'S SAKE READ UP ON IT! It is a lifesaver when you need to do conditional sums, groups, etc. Access has the Switch() and Iif() functions, and Oracle has DECODE(), so check them out if that's what you're using. Brush up on JOINs and table aliasing as well.
CASE lets you test a value or values and will return an expression if it's true. The following should do what you're looking for:
----
SELECT C.Company,
C.CustomerID,
Sum(PD.oth) AS sumOTH,
Sum(CASE WHEN PD.potr>0 THEN PD.oth ELSE 0 END) AS sumOT,
Sum(CASE WHEN PD.potr=0 THEN PD.oth ELSE 0 END) AS sumDH,
Sum(PD.hours) + (Sum(CASE WHEN PD.potr=0.5 THEN PD.oth ELSE 0 END)/2) + Sum(CASE WHEN PD.potr=1 THEN PD.oth ELSE 0 END) AS sumLabor,
SUM(CASE E.IsEquipment WHEN 1 THEN PD.Units*E.EquipmentRate ELSE 0 END) AS sumEquipment
FROM Projects AS P INNER JOIN ProjectDetail AS PD ON (P.ProjectID=PD.ProjectID)
INNER JOIN Customer AS C ON (C.CustomerID=P.CustomerID)
INNER JOIN Equipment AS E ON (PD.EquipID=E.EquipID)
WHERE date between '" & request.form("sdate") & "' AND '" & request.form("edate")
GROUP BY C.Company, C.CustomerID
---
I've listed the syntax for sumOT for each DBMS flavor (SQL, Access, Oracle):
SQL: CASE WHEN PD.potr>0 THEN PD.oth ELSE 0 END
Access: Iif(PD.potr>0,PD.oth,0)
Oracle: DECODE(SIGN(PD.potr),1,PD.potr,0)
I'm not up on DECODE that well, so there might be a way of using a logical test instead of a valued expression.
(gotta tell ya that if that SQL statement works on the first try, I will charge you :) In case it doesn't work, I'm sure you can write it in straight SQL without using data shaping. Worst case would require subqueries, but I believe you can do it without them.
Good luck!
Edited by - robvolk on 12/05/2000 09:52:08 |
 |
|
|
Bob
Starting Member
USA
5 Posts |
Posted - 12/05/2000 : 16:01:57
|
Thank you!
Except for a couple missing quotes, it DID work the first time :) Guess you'll have to bill me 
Since I only posted the part that was giving me fits, I still have some work to do but learning about the case statement has made everything MUCH easier. Joins weren't a problem but without the case statement, I didn't have a clue how to get what I wanted from the server. Thank you again.
Bob
<>< |
|
|
| |
Topic |
|
|
|
How to sum a field based on a foreign key field within a data shape
