0

I am working with an equipment reservation system and need to calculate days that the equipment is being rented for so I can multiply it by the total cost per day of each reservation, then total the cost for all reservations in the query. The equipment pickup date and return date are stored in separate fields as yyyy/mm/dd 00:00:00.000 (no entries contain a time other than 00:00:00.000), there is also fields for pickup AM/PM and return AM/PM. If the pickup date is in the PM then that day does not count as a day, if the return date is in the AM then it does not count as a day. Ideally I would like to use the query below to get to my end goal, however it calculates the number of days as too many. 

SELECT
    SUM(TOTAL*DATEDIFF(day,PICKUPDT,RETURNDT)) 
FROM
    RENTAL 
WHERE
    AGENTCODE = '$AGENT' AND
    PICKUPDT >= '06/01/2013' AND
    RETURNDT <= '06/01/2014' AND
    PAIDOUT = '1' )

The above is nested within the whole query that I am running. The TOTAL field is the cost per day of rental. I need a way to accurately count the number of days the consumer will be charged for. I appreciate any help that can be given on the topic. This is my first question here and apologize in for any mistakes I made.

The finished screen I am working towards can be seen here:

http://jeffreyalanscott.com/stack_overflow1.jpg

jeffreyscott
  • 11
  • 5
  • May we know the name of this `AM/PM` field? – MonkeyZeus Feb 22 '14 at 01:02
  • RETURNAMPM, PICKUPAMPM are the two fields, if there is any other information I can offer please let me know. Thank you – jeffreyscott Feb 22 '14 at 01:05
  • Overall this query is going to be really tough. Have you looked into MySQL IF() constructs? http://stackoverflow.com/questions/5951157/mysql-if-in-select-statement – MonkeyZeus Feb 22 '14 at 01:10
  • Does this work the same in a SQL-server environment? – jeffreyscott Feb 22 '14 at 01:13
  • My bad, should have read the tags. I can check – MonkeyZeus Feb 22 '14 at 01:16
  • Sorry looks like MSSQL only supports the `CASE WHEN` stuff: http://stackoverflow.com/questions/63447/how-do-you-perform-an-if-then-in-an-sql-select – MonkeyZeus Feb 22 '14 at 01:16
  • Actually my bad I made the change after your last post :p I can't say I wasn't a bit discouraged by the response, but I appreciate any direction. I am able to calculate days between with php but for this cannot figure out a way get the final value that I need for this particular case without a query. – jeffreyscott Feb 22 '14 at 01:17
  • @jeffreyscott Show us the data you have (schema AND example data) and the desired output. It helps a lot! – nathan_jr Feb 22 '14 at 02:18
  • @NathanSkerl I have attached an image of the screen I am working towards. everything is working aside from the total value owed at the bottom as it does not calculate the value properly like it does for the other situations, since the day fields and total days are calculated in php. – jeffreyscott Feb 22 '14 at 03:17

2 Answers2

0

I would suggest using TIMESTAMPDIFF instead of DATEDIFF as DATEDIFF only considers the date components and not the time.

SELECT SUM(total * TIMESTAMPDIFF(DAY, pickupdt, returndt) + 1)
...

For example:

SELECT TIMESTAMPDIFF(DAY, '2014-01-01 08:00:00', '2014-01-02 12:00:00') + 1;

would yield an integer value of 2 indicating that the item was check out for longer than 24 hours.

The + 1 part is because the function rounds down partial days.

Mike Brant
  • 70,514
  • 10
  • 99
  • 103
  • Thanks for the response Mike, however every pickup and return date is formatted as such in the database: YYYY-MM-DD 00:00:00.000 Not a single on contains a time :( Would it possible to modify all of the pickup and return date values so that if PICKUPAMPM/RETURNAMPM is a PM time it would format the date to YYYY-MM-DD 23:59:59.000 and AM could be YYYY-MM-DD 08:00:00.000 or something of the sort? – jeffreyscott Feb 22 '14 at 01:46
0

You can use a case statement to increment the day count based on the hour-of-day of the return time. You might implement more granular rules based on the Days:Hours:

declare @rental table (AgentCode varchar(100), PickUpDt datetime, ReturnDt datetime, PickupAmPm char(2), ReturnAmPm char(2));

insert into @rental
    select '123', '2014-02-01', '2014-02-02', 'AM', 'PM' union all
    select '456', '2014-02-01', '2014-02-02', 'AM', 'AM'



select  AgentCode, 
        [total] = datediff(dd, PickupDt, ReturnDt) + case ReturnAmPm when 'AM' then 0 else 1 end
from @rental

Returns:

AgentCode   total       
----------- ----------- 
123         2           
456         1
nathan_jr
  • 9,092
  • 3
  • 40
  • 55
  • I'm off to a bad start here with my questions... The TOTAL field is a $ value for the cost of the reservation per day, and there is no time attached to any of the date entries in the database. The goal of this is to calculate the sum of the actual cost of all the reservations in question at the time of res, if the customer does not honor that, its out of my control. The PAIDOUT field is for if the commissions on the reservation have been paid for or not, which leads me to the real end goal which will be calculating the total commission due on the res. that have not been paid out. – jeffreyscott Feb 22 '14 at 02:12