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 |
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2012-10-09 : 07:46:18
|
I have Write an SQL query to list out details of clients who have been charged more than $500 above the average difference for service rendered. You should have these columns in your listing:clientID, name, serviceCode, serviceDescription, minCharge, averageDifference, actualServiceCost, differenceFromAverageDifferencewhere differenceFromAverageDifference is the amount above the average difference for service rendered. To qualify being selected, the differenceFromAverageDifference for the row should be above $500.and my tables are:CREATE TABLE ClientTable(clientID char(5) NOT NULL,clientName Char(30) NOT NULL,clientAddress char(50) NOT NULL,clientContact Numeric(8) NOT NULL,CONSTRAINT ClientTablePK PRIMARY KEY(clientID),CHECK(clientID like '[A-Z][0-9][0-9][0-9][A-Z]'),CHECK (clientContact like '[6][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ORclientContact like'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]' ORclientContact like'[9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'));CREATE TABLE CaseTable(referenceNum int NOT NULL IDENTITY(100000,1),startDate DATETIME NOT NULL,endDate DATETIME NULL,caseDetail Char(255) NOT NULL,caseType Char(255) NOT NULL DEFAULT'copyright and trademark',clientID Char(5) NOT NULL,CONSTRAINT CaseTablePK PRIMARY KEY(referenceNum),CONSTRAINT ClientTableFK FOREIGN KEY(clientID)REFERENCES ClientTable(clientID),CONSTRAINT caseTypeValues CHECK(caseType LIKE 'intellectual property enforcement' ORcaseType LIKE'copyright and trademark' OR caseType LIKE'patent and industrial design' OR caseType LIKE'trade secret' OR caseType LIKE 'risk management' OR caseType LIKE'litigation'), CONSTRAINT clientIDValues CHECK(clientID LIKE '[A-Z][0-9][0-9][0-9][A-Z]')); CREATE TABLE ServiceTable(serviceCode Char(3) NOT NULL,serviceDescription Char(255) NOT NULL,minCharge Money NOT NULL,CONSTRAINT ServiceTablePK PRIMARY KEY(serviceCode),CONSTRAINT serviceCodeValues CHECK(serviceCode like '[C][0-9][0-9]' ORserviceCode like'[E][0-9][0-9]' ORserviceCode like'[O][0-9][0-9]'),CONSTRAINT minChargeValues CHECK(minCharge BETWEEN '500' AND '1000000') );CREATE TABLE ServiceRenderedTable(referenceNum int NOT NULL IDENTITY(100000,1),serviceDate DATETIME NOT NULL,serviceCode Char(3) NOT NULL,actualServiceCost Money NOT NULL ,CONSTRAINT ServiceRenderedTablePK PRIMARY KEY(referenceNum,serviceDate),CONSTRAINT CaseTableFK FOREIGN KEY(referenceNum) REFERENCES CaseTable(referenceNum),CONSTRAINT ServiceTableFK FOREIGN KEY(serviceCode) REFERENCES ServiceTable(serviceCode),CONSTRAINT actualServiceCost_positive CHECK (actualServiceCost>0.00)); |
|
jabu
Starting Member
2 Posts |
Posted - 2012-10-09 : 07:55:04
|
I would suggest that since averageDifference, actualServiceCost,differenceFromAverageDifference would be calculated values you dont have to create columns for the |
|
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2012-10-09 : 08:08:20
|
SELECT clientID, clientName, serviceCode, serviceDescription,minCharge, averageDifference, actualServiceCost,(actualServiceCost - minCharge)FROM ClientTable, [ChargeDifferences], ServiceRenderedTableWHERE LEFT JOIN (actualServiceCost - minCharge)>500this is my codes. the error am getting:Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'JOIN'.i am sorry forgot to show my view:CREATE VIEW ChargeDifferencesAS SELECT S.serviceCode, S.serviceDescription, S.minCharge, AVG(SR.actualServiceCost - S.minCharge) AS averageDifference FROM ServiceTable AS S LEFT OUTER JOIN ServiceRenderedTable AS SR ON SR.serviceCode = S.serviceCode GROUP BY S.serviceCode, S.serviceDescription, S.minCharge; |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-09 : 08:20:34
|
SELECT clientID, clientName, serviceCode, serviceDescription,minCharge, averageDifference, actualServiceCost,(actualServiceCost - minCharge)FROM ClientTable, [ChargeDifferences], ServiceRenderedTableWHERE LEFT JOIN (actualServiceCost - minCharge)>500--Chandu |
|
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2012-10-09 : 08:29:50
|
hi bandi,Thanks. but i guess you need to use joins or group by for the question.cause there is no way you can join three tables without joins or group by |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-09 : 08:53:41
|
quote: Originally posted by Mohamed Faisal hi bandi,Thanks. but i guess you need to use joins or group by for the question.cause there is no way you can join three tables without joins or group by
Try this one... SELECT ct.clientID, clientName, srt.serviceCode, serviceDescription,minCharge, averageDifference, actualServiceCost, (actualServiceCost - minCharge)FROM ServiceRenderedTable srt JOIN [ChargeDifferences] cd ON srt.serviceCode = cd.serviceCodeJOIN CaseTable ct ON ct.referenceNum = srt.referenceNumJOIN ClientTable clt ON clt.clientID = ct.clientIDWHERE (actualServiceCost - minCharge)>500;In your question you have asked for 'differenceFromAverageDifference'. what it means?But you are calculating (actualServiceCost - minCharge).--Chandu |
|
|
Mohamed Faisal
Yak Posting Veteran
51 Posts |
Posted - 2012-10-09 : 09:03:08
|
Hi bandi,Thanks, for the differenceFromAverageDifference is the amount above the average difference(averageDifference) for service rendered. To qualify being selected, the differenceFromAverageDifference for the row should be above $500. so i guessing that the (averageDifference)must be above 500. |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-09 : 09:10:54
|
quote: Originally posted by Mohamed Faisal Hi bandi,Thanks, for the differenceFromAverageDifference is the amount above the average difference(averageDifference) for service rendered. To qualify being selected, the differenceFromAverageDifference for the row should be above $500. so i guessing that the (averageDifference)must be above 500.
Welcome --Chandu |
|
|
|
|
|
|
|