[T-SQL] Parking System Calculation(Update Feb 19, 2010)

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

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s