July 31, 2013 at 2:11 am
Hi there,
As always thanks in advance with your help on my questions. I really appreaciate it.
I have the following script that in short shows me a bunch of jobs that have completed between a certain date.
SELECT DISTINCT
[Client Name]
,SiteName
,SitePostcode
,[Job Ref]
,[Client Ref]
,[Job Priority]
,[Job Status]
,[Reported Date]
,[Created Date]
,[Job Start Date]
,[Job End Date]
,[Job Target Date]
,[Hours]
,CASE WHEN [Days Or Hours] = 'Days'
THEN DATEDIFF(hh,[Reported Date],[Job End Date]) / 24
ELSE DATEDIFF(hh,[Reported Date],[Job End Date])
END AS [Days or Hours]
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 Date',
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 as 'Days',
CASEWHEN DATEDIFF(hh,WORK.reported_datetime,JOB.actual_enddatetime) <= '24' /* 24 hours for a day*/
THEN 'Hours'
ELSE 'Days'
END AS 'Days Or Hours'
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_startdate >= CONVERT(DATETIME, '2013-07-01 00:00:00', 102))
AND (JOB.actual_startdate <= CONVERT(DATETIME, '2013-07-29 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) IN ('J03HH5','J03VQ0')
ORDER BY JOB.created_date
) AS REPORT
Client Name SiteName SitePostcode Job Ref Client Ref Job Priority Job Status Reported Date Created Date Job Start Date Job End Date Job Target Date Hours Days or Hours
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------- -------------
Hexagon Housing Association 104 George Lane SE13 6HW J03HH5 202636 Routine (28 Days) 4 2013-05-23 15:57:00.000 2013-05-23 15:40:06.617 2013-07-01 11:15:00.000 2013-07-01 12:15:00.000 2013-07-12 15:57:00.000 933 38
Hexagon Housing Association 11 Orchard Mews 104 George Lane SE13 6HW J03VQ0 203906 24 hr Emergency 4 2013-07-04 10:59:00.000 2013-07-04 10:55:07.193 2013-07-04 11:25:37.000 2013-07-04 13:49:31.000 2013-07-05 10:59:00.000 3 3
This pulls me back two jobs as you can see from above.
Note the following -
Both jobs have different priorities. J03HH5 = Routine (28 Days) and J03VQ0 = 24 hr Emergency.
The hours columns pulls back the differance between the reported date of the job and the completion time.
I then work out if the hours are less then 24. If it is it should report hours if it is over 24 it should be days.
Then using the following I pull back Hours or days -
,CASE WHEN [Days Or Hours] = 'Days'
THEN DATEDIFF(hh,[Reported Date],[Job End Date]) / 24
ELSE DATEDIFF(hh,[Reported Date],[Job End Date])
END AS [Days or Hours]
What I want to achieve with this column is the following -
In the statement above in the CASE statement I want to add the text "hours" at the end of field if it is an hour and "days" if it is a day or over.
Also you will notice that for job J03HH5 that it took 38 hours to complete. How do I get it more exact to 38.88 which is the exact differance between the two dates.
So in the "Days and Hours" column for J03HH5 it would pull back 38.88 days and 3 hours for J03VQ0
I do have an extra question about target dates and working whether something is in target or not...but I will exaplin that if someone can help me with my first query.
Thank you
July 31, 2013 at 3:28 am
July 31, 2013 at 5:59 am
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
July 31, 2013 at 7:06 am
Right now I have no time to prepare setup for your case. So if you could post DDL of tables involved with some sample data (as INSERT) and clearly defined output, then I can have a look this again.
Just follow the tips in article from the link found at the bottom of my signature, it would enable more people to help you promptly.
July 31, 2013 at 7:13 am
Thanks Eugene I will do.
July 31, 2013 at 7:41 am
Just a quick thought as I happened through here. Why do you have select top 100 percent in a subquery here? You have introduced a performance hit when it is not needed. You have to order your subquery but you are getting ALL of the rows, then your final output doesn't even have an order by so the final result is in random order. I would recommend removing the TOP 100 PERCENT and the ORDER BY from your subquery.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 31, 2013 at 7:56 am
Thanks Sean. I forgot to remove that.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply