October 9, 2012 at 6:25 am
I will need to 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,
differenceFromAverageDifference
where differenceFromAverageDifference is the amount above the average
difference for service rendered. To qualify being selected, the
differenceFromAverageDifference for the row should be above $500.
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]' OR
clientContact like'[8][0-9][0-9][0-9][0-9][0-9][0-9][0-9]' OR
clientContact 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' OR
caseType 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]' OR
serviceCode like'[E][0-9][0-9]' OR
serviceCode 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)
);
And my View is:
CREATE VIEW ChargeDifferences
AS
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;
I tried to write the code:
SELECT clientID, clientName, serviceCode, serviceDescription,
minCharge, averageDifference, actualServiceCost,
(actualServiceCost - minCharge)
FROM ClientTable, [ChargeDifferences], ServiceRenderedTable
WHERE LEFT OUTER JOIN
(actualServiceCost - minCharge)>500
the error is :
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'OUTER'
please help me to solve the problem. Thanks
October 9, 2012 at 6:48 am
thbe error is here: you are mixing the old comma delimited join style up with the newer explicit join;
you should stick with the newer version, since it makes it clearer what your join criteria really is:
--bad
FROM ClientTable, [ChargeDifferences], ServiceRenderedTable
WHERE LEFT OUTER JOIN
(actualServiceCost - minCharge)>500
--better style
FROM ClientTable
LEFT OUTER JOIN [ChargeDifferences]
ON ClientTable.?? = [ChargeDifferences].???
LEFT OUTER JOIN ServiceRenderedTable
ON ClientTable.?? = ServiceRenderedTable.???
WHERE
(actualServiceCost - minCharge)>500
Lowell
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply