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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Calculation in a View		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Steve2106
Posting Yak Master

United Kingdom
149 Posts
Posted - 08/22/2012 :  10:27:16  Show Profile  Reply with Quote
Hi There,

I have a view coded as:
SELECT dbo.Rental.RentalTotalCost, dbo.Company.CompanyID
FROM dbo.Rental INNER JOIN
dbo.Vehicle ON dbo.Rental.VehicleId = dbo.Vehicle.VehicleId INNER JOIN
dbo.Company ON dbo.Vehicle.CompanyID = dbo.Company.CompanyID
WHERE (YEAR(dbo.Rental.RentalStartDate) = YEAR({ fn NOW() })) AND (MONTH(dbo.Rental.RentalStartDate) = MONTH({ fn NOW() }))

This returns 2 Columns. 1 with a companyId and 1 with a value.

So it could return:
1 350
1 50
2 60
1 40
1 40
2 30
2 50

What I would like is the view to return a total count for each Company1d. so from the values above my view would return.
1 480
2 140

How would I do that.

Thanks for your help.

Best Regards,



Steve

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/22/2012 :  10:46:29  Show Profile  Reply with Quote 

SELECT SUM(dbo.Rental.RentalTotalCost) AS TotalCost, dbo.Company.CompanyID
FROM dbo.Rental INNER JOIN
dbo.Vehicle ON dbo.Rental.VehicleId = dbo.Vehicle.VehicleId INNER JOIN
dbo.Company ON dbo.Vehicle.CompanyID = dbo.Company.CompanyID
WHERE (YEAR(dbo.Rental.RentalStartDate) = YEAR({ fn NOW() })) AND (MONTH(dbo.Rental.RentalStartDate) = MONTH({ fn NOW() }))
GROUP BY dbo.Company.CompanyID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/22/2012 :  10:48:06  Show Profile  Reply with Quote
also

WHERE (YEAR(dbo.Rental.RentalStartDate) = YEAR({ fn NOW() })) AND (MONTH(dbo.Rental.RentalStartDate) = MONTH({ fn NOW() }))

can be better written as

WHERE dbo.Rental.RentalStartDate >=DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)
AND dbo.Rental.RentalStartDate < DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0)

to make use of an available index on RentalStartDate column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

Steve2106
Posting Yak Master

United Kingdom
149 Posts
Posted - 08/22/2012 :  17:09:32  Show Profile  Reply with Quote
Hi Visakh,

Thanks for the reply.

That works perfect. Thankyou.

As always I appreciate your help & time.

Best Regards,

Steve.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47173 Posts
Posted - 08/22/2012 :  18:00:54  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
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.08 seconds. Powered By: Snitz Forums 2000