CODEWITHSUNDEEP
sqlsql-serverdatabaset-sql
user3613744
user3613744

Reputation: 75

Create a view with datetime calculation?How?

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_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

Upvotes: 1

Views: 210

Answers (1)

D Stanley
D Stanley

Reputation: 152626

Well the hard-coded way would be:

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,  
    DATEDIFF(hour, rental, return) * 
        CASE WHEN DATEDIFF(hour, rental, return) > 24
           then 8
           ELSE 10
        END AS TotalCost
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

(I don't know German so I added the English words you mentioned in the query)

Note that I've added line breaks to make your SQL MUCH easier to read.

A better way would be to have a table that specifies the rate for a given range of hours and join to that, but that's another question :)

Upvotes: 1

Related Questions