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