Calculation in a 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

SQL Server 2008 Forums

Transact-SQL (2008)

Calculation in a View

Author

Topic

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-08-22 : 10:27:16

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

52326 Posts

Posted - 2012-08-22 : 10:46:29

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

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 10:48:06

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/

Steve2106
Posting Yak Master

183 Posts

Posted - 2012-08-22 : 17:09:32

Hi Visakh,

Thanks for the reply.

That works perfect. Thankyou.

As always I appreciate your help & time.

Best Regards,

Steve.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-22 : 18:00:54

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources