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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-12-04 : 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." |
|
|
|
|
|
How to sum a field based on a foreign key field within a data shape