locked
Create a view with calculation.DATEDIFF? How? RRS feed

  • 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

    Monday, June 23, 2014 9:50 PM

Answers

  • 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








    Monday, June 23, 2014 10:29 PM
  • 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)

    • Proposed as answer by Elvis Long Monday, June 30, 2014 4:39 AM
    • Marked as answer by Elvis Long Wednesday, July 2, 2014 7:20 AM
    Monday, June 23, 2014 11:02 PM

All replies

  • 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








    Monday, June 23, 2014 10:29 PM
  • 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)

    • Proposed as answer by Elvis Long Monday, June 30, 2014 4:39 AM
    • Marked as answer by Elvis Long Wednesday, July 2, 2014 7:20 AM
    Monday, June 23, 2014 11:02 PM