View - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

View

Author

Topic

Mohamed Faisal
Yak Posting Veteran

51 Posts

Posted - 2012-10-01 : 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.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-01 : 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

52326 Posts

Posted - 2012-10-01 : 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
Yak Posting Veteran

51 Posts

Posted - 2012-10-07 : 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

264 Posts

Posted - 2012-10-07 : 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
Yak Posting Veteran

51 Posts

Posted - 2012-10-07 : 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

264 Posts

Posted - 2012-10-07 : 05:22:53

[code]
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;
[/code]

For us, there is only the trying. The rest is not our business. ~T.S. Eliot

Muhammad Al Pasha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources