create a view with datetime calculation? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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 2012 Forums

Transact-SQL (2012)

create a view with datetime calculation?

Author

Topic

daddo
Starting Member

1 Post

Posted - 2014-06-23 : 17:56:28

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-23 : 19:31:29

You'll need to post some sample data and expected output for us to help.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources