SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 View
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/01/2012 :  05:21:28  Show Profile  Reply with Quote
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

5155 Posts

Posted - 10/01/2012 :  07:06:35  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 10/01/2012 :  10:21:45  Show Profile  Reply with Quote
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/

Go to Top of Page

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/07/2012 :  03:17:18  Show Profile  Reply with Quote
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));


Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/07/2012 :  03:53:51  Show Profile  Reply with Quote
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
Go to Top of Page

Mohamed Faisal
Starting Member

44 Posts

Posted - 10/07/2012 :  04:04:16  Show Profile  Reply with Quote
Hi malpashaa,

Thanks allot. just one more question is there away to do that without using join.
Go to Top of Page

malpashaa
Constraint Violating Yak Guru

Saudi Arabia
257 Posts

Posted - 10/07/2012 :  05:22:53  Show Profile  Reply with Quote

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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000