Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Convert DATEDIFF to Decimal and Help with another DATEDIFF (Excluding weekends) Expand / Collapse
Author
Message
Posted Wednesday, July 31, 2013 2:11 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 185, Visits: 384
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',
CASE WHEN 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
Post #1479334
Posted Wednesday, July 31, 2013 3:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
Devide by 24.0

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1479362
Posted Wednesday, July 31, 2013 5:59 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 185, Visits: 384
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',
CASE WHEN 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
Post #1479422
Posted Wednesday, July 31, 2013 7:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 03, 2014 10:10 AM
Points: 2,792, Visits: 4,874
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.


_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!"
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help
Post #1479461
Posted Wednesday, July 31, 2013 7:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 185, Visits: 384
Thanks Eugene I will do.
Post #1479466
Posted Wednesday, July 31, 2013 7:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 11,934, Visits: 10,971
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1479486
Posted Wednesday, July 31, 2013 7:56 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 185, Visits: 384
Thanks Sean. I forgot to remove that.
Post #1479498
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse