According to general parking lot sheet:

1 hour or less $4

2 hours or less $7

3 hours or less $10

4 hours or less $15

>4 hours to 24 hours max $18

After weekday 5 PM or weekends (Time In)

3 hours or less $5

4 hours or less $15

>4 hours to 24 hours max $18

Which comes the following SQL and table design:

(The following is parametered with SQL declaration : Declare/Set)

Declare @time_in datetime Set @time_in = '2010-02-19 8:49' Declare @time_out datetime Set @time_out = '2010-02-19 9:10' Declare @discount int Set @discount = 0 /* Test Case For 30, 60, 120, 180, 240 Unit is minutes */ select ( select df = ( select duration = case when ((datediff(minute, @time_in, @time_out) - @discount) / 60) / 24 = 0 then 0 else ((datediff(minute, @time_in, @time_out) - @discount) / 60) / 24 end ) * rate from pkrate where rategroup = ( select rategroup = case when datepart(weekday, @time_in) between 2 and 6 and datepart(hour, @time_in) < 17 then 1 else 2 end ) and rateduration = ( select duration = case when ((datediff(minute, @time_in, @time_out) - @discount) / 60) / 24 = 0 then 0 else 5 end ) ) + ( select df = ( select duration = case when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 0 and (datediff(minute, @time_in, @time_out) - @discount) % 60 <= 0 then 0 else 1 end ) * rate from pkrate where rategroup = ( select rategroup = case when datepart(weekday, @time_in) between 2 and 6 and datepart(hour, @time_in) < 17 then 1 else 2 end ) and rateduration = ( select duration = case when (datediff(minute, @time_in, @time_out) - @discount) < 0 then 0 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 0 and (datediff(minute, @time_in, @time_out) - @discount) % 60 = 0 then 0 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 0 and (datediff(minute, @time_in, @time_out) - @discount) % 60 > 0 then 1 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 1 and (datediff(minute, @time_in, @time_out) - @discount) % 60 = 0 then 1 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 1 and (datediff(minute, @time_in, @time_out) - @discount) % 60 > 0 then 2 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 2 and (datediff(minute, @time_in, @time_out) - @discount) % 60 = 0 then 2 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 2 and (datediff(minute, @time_in, @time_out) - @discount) % 60 > 0 then 3 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 3 and (datediff(minute, @time_in, @time_out) - @discount) % 60 = 0 then 3 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 3 and (datediff(minute, @time_in, @time_out) - @discount) % 60 > 0 then 4 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 4 and (datediff(minute, @time_in, @time_out) - @discount) % 60 = 0 then 4 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 = 4 and (datediff(minute, @time_in, @time_out) - @discount) % 60 > 0 then 5 when ((datediff(minute, @time_in, @time_out) - @discount) / 60) % 24 > 4 then 5 else 5 end ) ) as Total

and the following table design

Here is the brief version which applied with an additional parameter, looks simpler:

Declare @time_in datetime Set @time_in = '2010-02-19 8:49' Declare @time_out datetime Set @time_out = '2010-02-19 9:10' Declare @discount int Set @discount = 0 Declare @time_calc int Set @time_calc = datediff(minute, @time_in, @time_out) - @discount /* Test Case For 30, 60, 120, 180, 240 Unit is minutes */ select ( select df = ( select duration = case when (@time_calc / 60) / 24 = 0 then 0 else (@time_calc / 60) / 24 end ) * rate from pkrate where rategroup = ( select rategroup = case when datepart(weekday, @time_in) between 2 and 6 and datepart(hour, @time_in) < 17 then 1 else 2 end ) and rateduration = ( select duration = case when ((@time_calc / 60) / 24) = 0 then 0 else 5 end ) ) + ( select df = ( select duration = case when ((@time_calc / 60) % 24) = 0 and (@time_calc % 60) <= 0 then 0 else 1 end ) * rate from pkrate where rategroup = ( select rategroup = case when datepart(weekday, @time_in) between 2 and 6 and datepart(hour, @time_in) < 17 then 1 else 2 end ) and rateduration = ( select duration = case when (@time_calc) < 0 then 0 when ((@time_calc / 60) % 24) = 0 and (@time_calc % 60) = 0 then 0 when (@time_calc / 60) % 24 = 0 and (@time_calc) % 60 > 0 then 1 when (@time_calc / 60) % 24 = 1 and (@time_calc) % 60 = 0 then 1 when (@time_calc / 60) % 24 = 1 and (@time_calc) % 60 > 0 then 2 when (@time_calc / 60) % 24 = 2 and (@time_calc) % 60 = 0 then 2 when (@time_calc / 60) % 24 = 2 and (@time_calc) % 60 > 0 then 3 when (@time_calc / 60) % 24 = 3 and (@time_calc) % 60 = 0 then 3 when (@time_calc / 60) % 24 = 3 and (@time_calc) % 60 > 0 then 4 when (@time_calc / 60) % 24 = 4 and (@time_calc) % 60 = 0 then 4 when (@time_calc / 60) % 24 = 4 and (@time_calc) % 60 > 0 then 5 when (@time_calc / 60) % 24 > 4 then 5 else 5 end ) ) as Total

PS1 : 24 hours, 2. Second query runs for condition < 24 hours, and then summed up with the final price.

PS3 : The @discount is a variable for any coupon or discount, which will deduct the time with new calculated price.

This query is performed on Windows 2003 Server with Microsoft SQL 2000 Enterprise Edition which should be compatible with the rest of the T-SQL DBMS.