Thanks Eugene. I managed to get this working.

In the end my script looked like below -

SELECT DISTINCT

[Client Name]

,SiteName

,SitePostcode

,[Job Ref]

,[Client Ref]

,[Job Priority]

,[Job Status]

,[Reported Date]

,[Created Date]

,[Job Start Date]

,[Job End DateTime]

,[Job End Date]

,[Job End Month]

,[Job End Week]

,[Job Target Date]

,[Hours]

,[Days]

,CASE WHEN [timeframe] = 'Hours'

THEN CONCAT ([Hours],' ',[timeframe])

ELSE CONCAT ([Days],' ',[timeframe]) END AS 'DaysorHoursToComplete'

FROM

(

SELECT TOP 100 PERCENT

CLI.client_name AS 'Client Name',

SIT.property_name AS 'SiteName',

SIT.property_postcode AS 'SitePostcode',

JOB.ref AS 'Job Ref',

JOB.client_ref AS 'Client Ref',

PRI.description AS 'Job Priority',

JOB.status_id AS 'Job Status',

WORK.reported_datetime AS 'Reported Date',

WORK.created_datetime AS 'Created Date',

JOB.actual_startdatetime AS 'Job Start Date',

JOB.actual_enddatetime AS 'Job End DateTime',

Job.actual_enddate AS 'Job End Date',

DATEPART(MM, actual_enddate) AS 'Job End Month',

DATEPART(WW, actual_enddate) AS 'Job End Week',

JOB.resolutiontarget_datetime AS 'Job Target Date',

DATEDIFF(hh,WORK.reported_datetime,JOB.actual_enddatetime) AS 'Hours',

DATEDIFF(hh,WORK.reported_datetime,JOB.actual_enddatetime) / 24.0 as 'Days',

CASEWHEN DATEDIFF(hh,WORK.reported_datetime,JOB.actual_enddatetime) <= 24.0 /* 24 hours for a day*/

THEN 'Hours'

ELSE 'Days'

END AS 'timeframe'

FROM dbo.Job AS JOB

INNER JOIN

dbo.Client AS CLI

ON JOB.client_id = CLI.client_id

INNER JOIN

dbo.WorkLog AS WORK

ON JOB.incident_id = WORK.ID

INNER JOIN

dbo.Site AS SIT

ON WORK.SiteID = SIT.SiteID

INNER JOIN

dbo.Priority AS PRI

ON JOB.priority_id = PRI.[ID]

WHERE (JOB.actual_enddate >= CONVERT(DATETIME, '2013-04-01 00:00:00', 102))

AND (JOB.actual_enddate <= CONVERT(DATETIME, '2014-03-31 00:00:00', 102))

AND (JOB.version_status <> 'Closed')

AND (JOB.status_id NOT IN ('0','5','6','1','2'))

--AND (JOB.client_id = '1')

--AND (JOB.ref = 'J03FEF')

--AND(JOB.ref) IN ('J03HH5','J03VQ0')

ORDER BY JOB.created_date

) AS REPORT

The next thing I need to work out is dependant on the [Job Priority] I need to work out if the job is "In Target" or "OverTarget" by comparing the [Reported Date] to the [Job End DateTime]. But I don't want to include non working days. How can I do this? I do have a Date table that I have created called "DimDate"

that has all my dates and whether they are a Work Day or not but I have no idea how I can script this.

All the priorities have different targets as below which is the dbo.Priority I have in my TSQL-

ID description hours_to_respond

----------- -------------------------------------------------- ----------------

1 Out of Hours 2

2 24 hr Emergency 24

3 Urgent (7 days) 168

4 10 day 240

5 Routine (28 Days) 672

6 Gas Servicing 1560

7 Void 672

8 Planned (90 Days) 2160

9 Standard Void (10 Days) 240

10 SHG_Void 120

11 SHG_Routine (22 Days) 528

(11 row(s) affected)

As you can see each Priority has a different response time so I need logic that will calculate the fact if it is in target or not by looking at it's priority, not taking into account non working days and also if it is a 24 hour emergency that it will use the [Reported Date] and compare it to the [Job Start Date]

Far too complicated for me to get my head around.

Thanks