Querying Help

[Strider]

Hey there... im have a query im not sure how to do


i have 3 tables, Customers, Contracts and Jobs

Customer - iId, sName

Contracts - iId, iCustId, mCleaningCost, mWashCost, mServiceCost

Jobs - iId, dJobDate, iCustId, bCleaning, bWash, bService

i means integer type, m means smallmoney type and b means boolean type

so basically a customer has a contract type which contains the costs of services provided.
the services that are provided to the customer when they come in are marked in the job table as boolean (yes/no)


so what i need to do is a report that reports the cost associated with the customer.Something like

Date Customer Cleaning Wash Service Total
01/01/2007 abc 20.00 25.00 40.00 85.00
15/01/2007 abc 0.00 0.00 40.00 40.00
20/01/2007 abc 20.00 25.00 00.00 45.00

but im not sure how to do this in SQL

[szlamany]

Select	 dJobDate	"Date"
	,sName		"Customer"
	,Sum(Case When bCleaning<>0 Then mCleaningCost Else 0 End)
			"Cleaning"
	,Sum(Case When bWash<>0 Then mWashCost Else 0 End)
			"Wash"
	,Sum(Case When bService<>0 Then mServiceCost Else 0 End)
			"Service"
	,Sum(Case When bCleaning<>0 Then mCleaningCost Else 0 End
		+Case When bWash<>0 Then mWashCost Else 0 End
		+Case When bService<>0 Then mServiceCost Else 0 End)
			"Total"
	From Jobs JO
	Left Join Contracts CO on CO.iId=JO.iId and CO.iCustId=JO=iCustId
	Left Join Customer CS on CS.iId=CO.iId
	Group by dJobDate,sName

I'm unclear on the CustId and Id fields - not sure how the joins are really supposed to work...

Also - not sure if a customer can have more than one job row for a date - so I did a SUM() and GROUP BY - that's not really needed if only one job row...

[Strider]

hi Szlamany

yes the cust can have as many jobs as they want on any days...
the iId field are just identity fields for each table and the iCustId is the link between the tables.
so in the report they want to see all the jobs on any given day/week/month/year

I will try you query... that case statements look interesting to what i need.
do u have anylinks where it does more examples of these

[szlamany]

Ok - so you will need to fix some of those joins a bit - right?

As for the CASE - it basically comes in two forms...

Case When x=y Then 1 When x=z Then 2 When x=y and x=z then 3 Else 4 End

That forms uses a WHEN to check a different condition each time.

The other form is

Case SomeCol When 1 Then 'A' When 2 Then 'B' When 3 Then 'C' Else 'X' End

That form use a CASE on a field (in this example) and each WHEN compares a value to that field.

The most important things about a CASE is to always, always, always have an ELSE - as without an ELSE it can yield a NULL (when no conditions are met) and you probably know what a NULL does to SUM() and +'s.

[Strider]

cheers dude

those case are ideal for what i want to do...

and my sql is finally getting better too!!!!