Date Difference for multiple dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date Difference for multiple dates

Author

Topic

delanopg
Starting Member

6 Posts

Posted - 2014-06-18 : 15:32:59

I have a field called 'LOG_COMMENTS' in a table named T_PRODUCTION_WORK_LOG.

In the 'LOG_COMMENTS' whenever a request is placed on hold comments are added by the application, such as 'Status changed from Open to On Hold' and 'Status changed from On Hold to Open' along with a 'LOG_DATESTAMP' field. A request can go on and off Hold multiple times, how do I detrimine the days a request is On Hold?

I know I can use the sql function DATEDIFF ( datepart , startdate , enddate ), but how do I account for the possiblity that the request was On Hold more than once? And how would I get LOG_DATESTAMP' times for 'LOG_COMMENTS' that contain
'Status changed from Open to On Hold' and 'Status changed from On Hold to Open' ?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-18 : 15:40:16

Please show us some sample data and expected output as your post is hard to follow.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

delanopg
Starting Member

6 Posts

Posted - 2014-06-18 : 15:51:00

ok sample data:
LOG_ID LOG_DATESTAMP LOG_UID LOG_COMMENTS
1530253 2014-03-27 10:17:29.000 317690 Status changed from On Hold to Open
1531819 2014-04-03 12:45:47.000 317690 Status changed from Open to On Hold
1511894 2013-12-16 07:18:54.000 317690 Status changed from Open to On Hold
1536836 2014-04-30 12:47:34.000 317690 Status changed from On Hold to Open

Paul D

delanopg
Starting Member

6 Posts

Posted - 2014-06-18 : 15:54:18

Expected result is the total time the request was On Hold.
Not the time between the 1st On Hold time and the last, but the time between the 1st On Hold and 1st On Hold to Open and the time between the 2nd On Hold and the 2nd On HOld to Open.

Paul D

delanopg
Starting Member

6 Posts

Posted - 2014-06-18 : 15:56:01

here is the sql I used to get sample

SELECT
[LOG_ID]
,[LOG_DATESTAMP]
, LOG_UID
,[LOG_COMMENTS]
FROM [aid1426].[dbo].[T_PRODUCTION_WORK_LOG]
where LOG_COMMENTS LIKE '%On Hold%'
AND LOG_UID = '317690'

Paul D

delanopg
Starting Member

6 Posts

Posted - 2014-06-18 : 15:57:07

quote:
Originally posted by tkizer

Please show us some sample data and expected output as your post is hard to follow.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Sample above
Paul D

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-18 : 15:58:45

Please see this article for how to post sample data in a consumable format: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

delanopg
Starting Member

6 Posts

Posted - 2014-06-18 : 16:21:28

quote:
Originally posted by tkizer

Please see this article for how to post sample data in a consumable format: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Thanks.
CREATE TABLE [dbo].[Aid1426_sampleData](
[LOG_ID] [int] NULL,
[LOG_DATESTAMP] [datetime] NULL,
[LOG_UID] [int] NULL,
[LOG_COMMENTS] [nvarchar](255) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Aid1426_sampleData] ([LOG_ID], [LOG_DATESTAMP], [LOG_UID], [LOG_COMMENTS]) VALUES (1530253, CAST(0x0000A2FB00A998CC AS DateTime), 317690, N'Status changed from On Hold to Open. ')
INSERT [dbo].[Aid1426_sampleData] ([LOG_ID], [LOG_DATESTAMP], [LOG_UID], [LOG_COMMENTS]) VALUES (1531819, CAST(0x0000A30200D25424 AS DateTime), 317690, N'Status changed from Open to On Hold. ')
INSERT [dbo].[Aid1426_sampleData] ([LOG_ID], [LOG_DATESTAMP], [LOG_UID], [LOG_COMMENTS]) VALUES (1511894, CAST(0x0000A29600788C28 AS DateTime), 317690, N'Status changed from Open to On Hold.')
INSERT [dbo].[Aid1426_sampleData] ([LOG_ID], [LOG_DATESTAMP], [LOG_UID], [LOG_COMMENTS]) VALUES (1536836, CAST(0x0000A31D00D2D188 AS DateTime), 317690, N'Status changed from On Hold to Open.')

Paul D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources