Need help with SQL; Date Diff between more than 2 dates
- or -
Post a project like this3568
$$
- Posted:
- Proposals: 5
- Remote
- #510339
- Awarded
Description
Experience Level: Intermediate
General information for the website: metrics
Database management system (DBMS): Microsoft SQL Server
Description of requirements/features: 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' ?
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
here is the sql I used to get sample data
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'
--------------------------------------------------------------------------------
Here is the sample date in a consumable format:
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.')
Extra notes:
Database management system (DBMS): Microsoft SQL Server
Description of requirements/features: 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' ?
--------------------------------------------------------------------------------
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
--------------------------------------------------------------------------------
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.
--------------------------------------------------------------------------------
here is the sql I used to get sample data
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'
--------------------------------------------------------------------------------
Here is the sample date in a consumable format:
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.')
Extra notes:
Paul D.
100% (31)Projects Completed
11
Freelancers worked with
11
Projects awarded
47%
Last project
8 Mar 2016
United States
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies