Create a view with calculation.DATEDIFF? How?

Question

Hi, i have tables Receipt and Rent. the database is for rent a car company. in the  Receipt  table i have Total Cost and in the Receipt table Rental Date and Return Date. I want to create a view from those 2 tables and within this view a funktion which would calculate and subtract the 2 dates (return-rental) in hours, multiply it by a number and put it in the Total Cost column. Like this: if the hours>24 then Total Cost = 24*8 else 24*10 - for an example. And the result should go in the Total cost column. Im sql beginner so i really dont know how to do that or if it can be done. This is the code for a view (there are more columns and it´s in german) the important ones are Rechnungsbetrag=Total Cost, Ausleihdatum=Rental Date, Ruckgabedatum=Return Date.  I appreciate your help.

CREATE VIEW vRechnung
AS
SELECT tbl_Rechnung.RechnungsNr,tbl_Rechnung.Kunden_ID,tbl_mieten.Ausleihdatum,tbl_mieten.Rückgabedatum,tbl_Rechnung.Rechnungsbetrag ,tbl._mieten.Rückgabedatum,tbl_Sachbearbeiter.Nachname AS Sachbearbeiter
FROM tbl_Rechnung
join
tbl_mieten ON tbl_Rechnung.Kunden_ID=tbl_mieten.Kunden_ID
join
tbl_Sachbearbeiter ON tbl_Rechnung.SVNr=tbl_Sachbearbeiter.SVNr
GO

Answer 1

You can use CASE conditional expression:

-- CASE Conditional Expression
SELECT CASE WHEN DATEDIFF(HH, RentalDate,ReturnDate) > 24 THEN 24*8
       ELSE 24*10 END FROM YourTable;

You can use CASE anywhere to replace a scalar value in a SELECT statement.

CASE expression examples:

http://www.sqlusa.com/bestpractices/training/scripts/casefunction/

---

Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012

Answer 2

Here is an example with a CASE statement in the View definition -

create table rental (rental_id int, rental_date datetime, return_date datetime)
go

insert into rental (rental_id, rental_date, return_date)
values
(1, '2014-1-1 10:00', '2014-1-1 14:00'),
(2, '2014-1-2 10:00', '2014-1-3 10:00'),
(3, '2014-1-5 10:00', '2014-1-7 23:00')
go

create view vw_TotalCost
as
select *, 
DATEDIFF(HH, rental_date, return_date) as NumberOfHours, 
case
when DATEDIFF(HH, rental_date, return_date) <= 24 
  then DATEDIFF(HH, rental_date, return_date) * 10
when DATEDIFF(HH, rental_date, return_date) > 24 
  then DATEDIFF(HH, rental_date, return_date) * 8
end as TotalCost
from rental
go

select * from vw_TotalCost

---

- Aalamjeet Rangi | (Blog)