DATETIME CALCULATION PROBLEM SQL

[SQLRory]

Hello,

I have been given a task to write a report which calculates cost based upon the time elapsed between two datetime values. I would do this by writing my sql then importing to Crystal V11 to format accordingly.

This may seem fine as you would expect a typical
datetime(minutes,[x datetime],[y datetime]) to work, which it does, when you have one constant cost!

BUT!!!!

My calculations have to calculate based upon different rates for different times in the day.

For example,

Mon - Fri 07:00 - 18:00hrs (charged at £11.20 per hour)
Outside of these times (£13 per hour)
note:the calculations have to be exact to the minute!

the fields I would use are called

visits.plandate and visits.planenddate for example
'20080606 09:00' <--------> '20080606 19:00'

this would cost:

9 hours at £11.20 = (9*11.20) +
1 hour at £13 = (1*13)
= £113.80

I have tried to use a number of different case when's and other stuff but I just can't seem to crack it and I am not the best with handling datetime values etc.

Could anyone please help.

Thanks so much

Rory

 

[Qik3Coder]

Start by breaking up what you have to do into logical chunks.

Figure out the logic in pieces, then put it back into one big puzzle if you want.

Start by getting all the times that you need to calculate costs for.

Next break the times into 2 pieces if you feel that you have to price 1 part at a different rate.

Then, and only then, can you calculate your costs.

Now lump the costs back together, to get your cost for the total job.

Then fancify the results and pass them back.

David

-Sometimes the answer to your question is the hack that works

 

[gmmastros]

I recommend you create a table in your database that stored DayNumber, MinuteNumber, and Cost.

DayNumber range = 1 to 7, minute number range from 1 to 1440 (the number of minutes in a day), and the cost for that minute (your hourly rate / 60). Then, you could join to this table and SUM the cost column to get the total cost.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLRory]

Hi David,

This is kind of how I am trying to work through it now but to be honest I am really struggling. The main problem I am having is trying to say whether a datetime is between a certain range.

For example

'20080606 15:00' between '20080606 07:00' and '20080606 18:00'

so I need to be able to say

'[x date] 15:00]' between '[xdate] 07:00' and '[xdate] 18:00'

I have tried to use datepart but to no joy. Would you know how to do this using for example my datetime field visits.plandate

I thought by doing something like this it may work.
vistis.plandate between (datepart(?,visits.plandate + ' ' + 07:00) and (datepart(?,visits.plandate + ' ' + 18:00)

I know the above is wrong but you get the idea.

Thanks

Rory

 

[gmmastros]

You can remove the date component of a date time like this...

Select '20080606 15:00' - DateAdd(Day, DateDiff(Day, 0, '20080606 15:00'), 0)

You can even do between's like this...

Select 1
Where '20080606 15:00' - DateAdd(Day, DateDiff(Day, 0, '20080606 15:00'), 0) Between '07:00' And '18:00'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLRory]

Why didn't I think of that before.

Right I'm going to get on with this and see how I get on. I'll give you a shout if I get stuck. Which I will....

Thanks

Rory

 

[SQLRory]

I'm stuck already,

I have just put this case when just see if it worked and it wouldn't run

case
when (visits.plandate - DateAdd(Day, DateDiff(Day, 0, 'visits.plandate'), 0) Between '07:00' And '18:00') then 'Yes'
else 'No' end as 'Test',

Why do you think this wouldn't run?

Thanks

Rory

 

[Qik3Coder]

For auditing purposes you may want to use a real, not a temp table, like gmmastros said, to save the data parts that you used for your calculations (almost as a "show your work" section).

Depending on how you wind up getting your times out of the date ranges you might wind up with a pair of case statements, so that you can get overlapping times into the base data table.

Some thing like

case when time > 17:00 then datepart(time after 5pm) then
when time < 17:00 then datepart (time before 5pm) end,
case when time > 17:00 then 'Base Rate' then
when time < 17:00 then datepart 'O/T Rate' end,

-Sometimes the answer to your question is the hack that works

 

[Qik3Coder]

your '7:00' and '18:00' would probably be better suited as declared datetime variables, for speed clarity, and in case you change your business hours. In addition, when you get down to calculating total cost by rate, you should consider putting the rates in a separate real table, and joining on it, in case things ever get to the point where you have diff rates based on the type, or employee working.

-Sometimes the answer to your question is the hack that works

 

[gmmastros]

Remove the single-quotes from this....

DateDiff(Day, 0, [!]'[/!]visits.plandate[!]'[/!])


Should be...
[tt][blue]
DateDiff(Day, 0, visits.plandate)[/blue][/tt]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLRory]

Thanks I really appreciate your help guys.

I think I will create a columns in my select which shows...

Hours 07:00 - 18:00 Mon - Fri ¦ Hours outside 07:00 - 18:00 ¦ Base Cost ¦ Other Cost ¦ etc.

Therefore covering auditing purposes and hopefully achieving the desired goal.

I'll get there.

Rory

 

[Qik3Coder]

Little crack of brain lightning.

You might want to figure out the calculations for splitting the time into "between x and y" and time outside of "x and y"

Then create Calculated columns on your base table, that stores the start and end time, so that you can just gen the report.

-Sometimes the answer to your question is the hack that works

 

[SQLRory]

Hi guys,

I managed to get what I wanted. Just though I would add the code so you can see what I did.

SELECT

trainee.traineeid as 'TraineeID',
traineepot.pot as 'POT',
trainee.firstname as 'First Name(s)',
trainee.lastname as 'Last Name',
trainee.firstname + ' ' + trainee.lastname as 'Trainee Name',
traineepot.startdate as 'Start Date',
contractor.description as 'Contract',
diary.longstring as 'Visit Type',
assessor.firstname + ' ' + assessor.lastname as 'PA',
visits.plandate as 'Plan Start',
visits.planenddate as 'Plan End',
visits.actualdate as 'Actual Start',
visits.actualenddate as 'Actual End',
case
when datename(dw,visits.plandate) NOT LIKE 'S%' then 'Yes'
else 'No'
end as 'Week Day',

--Outside of 07:00 - 18:00
case
when datename(dw,visits.plandate) NOT LIKE 'S%' then
(case
--Starts & Ends 00:00 - 07:00
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '00:00' and '07:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '00:00' and '07:00'))
then (datediff(minute,visits.plandate,visits.planenddate))

--Starts < 07:00 & Ends 07:00 - 18:00
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '00:00' and '07:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '07:00' and '18:00'))
then datediff(minute,visits.plandate, dateadd(day,datediff(day, 0, visits.plandate), '19000101 07:00'))

--Starts 00:00 - 07:00 & Ends 18:00 - 23:59
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '00:00' and '07:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '18:00' and '23:59'))
then (datediff(minute,visits.plandate, dateadd(day,datediff(day, 0, visits.plandate), '19000101 07:00')) +
datediff(minute,dateadd(day,datediff(day, 0, visits.plandate), '19000101 18:00'),visits.planenddate) +
(case when visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) = '23:59' then '1' else '0' end))

--Starts 07:00-18:00 & Ends 18:00-23:59
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '07:00' and '18:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '18:00' and '23:59'))
then (datediff(minute,dateadd(day,datediff(day, 0, visits.plandate), '19000101 18:00'),visits.planenddate) +
(case when visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) = '23:59' then '1' else '0' end))

--Starts & Ends 18:00-23:59
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '18:00' and '23:59') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '18:00' and '23:59'))
then (datediff(minute,visits.plandate,visits.planenddate) +
(case when visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) = '23:59' then 1 else 0 end))
end)
else (datediff(minute,visits.plandate,visits.planenddate) +
(case when visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) = '23:59' then 1 else 0 end))
end as 'Peak',


--Inside 07:00 - 18:00
case
when datename(dw,visits.plandate) NOT LIKE 'S%' then
(case
--Starts 00:00-07:00 & Ends 07:00-18:00
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '00:00' and '07:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '07:00' and '18:00'))
then datediff(minute,dateadd(day,datediff(day, 0, visits.plandate), '19000101 07:00'),visits.planenddate)

--Starts & Ends 07:00-18:00
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '07:00' and '18:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '07:00' and '18:00'))
then datediff(minute,visits.plandate,visits.planenddate)

--Starts 00:00-07:00 & Ends 18:00-23:59
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '00:00' and '07:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '18:00' and '23:59'))
then '660'

--Starts 07:00-18:00 & Ends 18:00-23:59
when ((visits.plandate - dateadd(day, datediff(day, 0, visits.plandate), 0) between '07:00' and '18:00') and
(visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) between '18:00' and '23:59'))
then datediff(minute,visits.plandate,dateadd(day,datediff(day, 0, visits.plandate), '19000101 18:00'))
end)
else '0'
end as 'Standard',

datediff(minute,visits.plandate,visits.planenddate) +
(case when visits.planenddate - dateadd(day, datediff(day, 0, visits.planenddate), 0) = '23:59' then 1 else 0 end)
as 'Planned Time',
datediff(minute,visits.actualdate,visits.actualenddate) +
(case when visits.actualenddate - dateadd(day, datediff(day, 0, visits.actualenddate), 0) LIKE '23:59%' then 1 else 0 end)
as 'Actual Time'


FROM

maytas3.trainee trainee
inner join maytas3.traineepot traineepot on trainee.traineeid = traineepot.traineeid
inner join maytas3.contractor contractor on traineepot.contractorid = contractor.contractorid
inner join maytas3.trvisit trvisit on traineepot.traineeid = trvisit.traineeid
inner join maytas3.visits visits on trvisit.visid = visits.visid
inner join maytas3.assessor assessor on visits.assessorid = assessor.assessorid
left join maytas3.lookup diary on diary.tablename = 'visits' and diary.fieldname = 'visittype' and diary.shortstring = visits.visittype

WHERE

(contractor.description = 'Solutions for Independence')

Please note all events never last longer than 24hours or pass over the point of Midnight

Thanks

Rory

 

[Qik3Coder]

Wow,that projection list is nice and complicated. At least the table structure is nice and clean. Way to go.

-Sometimes the answer to your question is the hack that works