| Author |
 Topic  |
|
|
Mohamed Faisal
Starting Member
26 Posts |
 Posted - 10/01/2012 : 05:21:28
|
Hi All,
I have a table Service with columns: Servicecode,ServiceDescription, minCharge. another table ServiceRendered with columns actcost,servicecode,service date.
I would need to constract a view with Servicecode,ServiceDescription, minCharge, and averageDifference which is the difference between actcost and minCharge.
And also include the service in the view but with a NULL average Difference
May I know how to construct the view.
|
Edited by - Mohamed Faisal on 10/01/2012 05:25:06
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/01/2012 : 07:06:35
|
I am not quite clear on what you meant by "And also include the service in the view but with a NULL average Difference". The part after the "UNION ALL" in the query below is based on my guess as to what you meant.CREATE VIEW dbo.YourViewNameHere
AS
SELECT
s.ServiceCOde,
s.ServiceDescription,
s.minCharge,
AVG(sr.actcost-minCharge) OVER(PARTITION BY ServiceCode) AS AverageDifference
FROM
[Service] s
INNER JOIN ServiceRendered sr ON
sr.serviceCode = s.ServiceCode
UNION ALL
SELECT
s.ServiceCOde,
s.ServiceDescription,
s.minCharge,
0 AS AverageDifference
FROM
[Service] s;
GO |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47189 Posts |
Posted - 10/01/2012 : 10:21:45
|
best thing would be to post some sample data to give us an idea of what exactly you're expecting
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
Mohamed Faisal
Starting Member
26 Posts |
Posted - 10/07/2012 : 03:17:18
|
Hi there,
i have to Construct a view named ChargeDifferences. This view has the columns:
serviceCode, serviceDescription, minCharge, averageDifference where
averageDifference is the average of the differences between the
actualServiceCost and the minCharge for each service offered.
If a service offered has never been rendered, include also the service in the view but with a NULL average difference.
and my tables are:
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));
|
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/07/2012 : 03:53:51
|
Try something like this:
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;
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
|
|
|
Mohamed Faisal
Starting Member
26 Posts |
Posted - 10/07/2012 : 04:04:16
|
Hi malpashaa,
Thanks allot. just one more question is there away to do that without using join. |
|
|
|
malpashaa
Constraint Violating Yak Guru
Saudi Arabia
257 Posts |
Posted - 10/07/2012 : 05:22:53
|
CREATE VIEW ChargeDifferences
AS
SELECT S.serviceCode, S.serviceDescription, S.minCharge,
(SELECT AVG(SR.actualServiceCost)
FROM ServiceRenderedTable AS SR
WHERE SR.serviceCode = S.serviceCode) - S.minCharge AS averageDifference
FROM ServiceTable AS S;
For us, there is only the trying. The rest is not our business. ~T.S. Eliot
Muhammad Al Pasha |
|
|
| |
Topic |
|
|
|
View
