May 31, 2016 at 2:27 am
Dear Team,
My requirement is to calculate the failure time, I just want to know the difference in hours where the status is 'I' and it shouldnt calculate if status is A
5/30/2016 12:30 A
5/30/2016 13:02 A
5/30/2016 14:02 I
5/30/2016 15:02 I
5/30/2016 19:17 A
5/30/2016 20:25 A
5/30/2016 22:02 I
5/30/2016 23:30 I
Please help me
May 31, 2016 at 3:00 am
Please post what you've already tried so that we can see what you're struggling with. If it's getting the time difference, use DATEDIFF. If it's getting a corresponding value from a different row, use LEAD or LAG. If it's filtering out the As, use WHERE.
John
May 31, 2016 at 3:17 am
Hi John
I tried many queries, Below is one of it. Its gives me the time difference of Max and Min value of statustime where Status = 'I' and i am not able to filter the records where Status = 'A'. Result should only contain the time difference of In Active records
with cteMax as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime desc
), cteMin as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime
)
select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin
inner join cteMax on cteMin.SiteCode = cteMax.SiteCode
Can you suggest some queries
May 31, 2016 at 3:23 am
Your query mentions at least one column that you didn't tell us about in your original post. Please will you post DDL in the form of a CREATE TABLE statement, sample data in the form of INSERT statements, and the results you expect to see based on the sample data?
Thanks
John
Edit - corrected typo
May 31, 2016 at 3:23 am
shagil.a.gopinath (5/31/2016)
Dear Team,My requirement is to calculate the failure time, I just want to know the difference in hours where the status is 'I' and it shouldnt calculate if status is A
StatusTime-- Status5/30/2016 12:30 A
5/30/2016 13:02 A
5/30/2016 14:02 I
5/30/2016 15:02 I
5/30/2016 19:17 A
5/30/2016 20:25 A
5/30/2016 22:02 I
5/30/2016 23:30 I
Please help me
to save confusion....what result are you expecting for the above sample data?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 31, 2016 at 3:41 am
Hi John,
CREATE TABLE [dbo].[Temp2](
[SiteCode] [int] NULL,
[StatusTime] [datetime] NULL,
[Status] [char](2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into Temp2 values(111,'2016-05-30 12:30:00.000','A')
insert into Temp2 values(111,'2016-05-30 13:02:00.000','A')
insert into Temp2 values(111,'2016-05-30 14:02:00.000','I')
insert into Temp2 values(111,'2016-05-30 15:02:00.000','I')
insert into Temp2 values(112,'2016-05-30 19:17:00.000','A')
insert into Temp2 values(112,'2016-05-30 20:25:00.000','A')
insert into Temp2 values(112,'2016-05-30 21:02:00.000','I')
insert into Temp2 values(112,'2016-05-30 23:02:00.000','I')
Consider I as InActive and A as Active, So if i pull the records for 'I' so the Result expected for the code 111 is 1 Hour as and for the code 112 its 2 hours, My real scenario is different I just put sample table and records
May 31, 2016 at 3:47 am
Hi,
CREATE TABLE [dbo].[Temp2](
[SiteCode] [int] NULL,
[StatusTime] [datetime] NULL,
[Status] [char](2) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
insert into Temp2 values(111,'2016-05-30 12:30:00.000','A')
insert into Temp2 values(111,'2016-05-30 13:02:00.000','A')
insert into Temp2 values(111,'2016-05-30 14:02:00.000','I')
insert into Temp2 values(111,'2016-05-30 15:02:00.000','I')
insert into Temp2 values(112,'2016-05-30 19:17:00.000','A')
insert into Temp2 values(112,'2016-05-30 20:25:00.000','A')
insert into Temp2 values(112,'2016-05-30 21:02:00.000','I')
insert into Temp2 values(112,'2016-05-30 23:02:00.000','I')
Consider I as InActive and A as Active, So if i pull the records for 'I' , Result expected for the code 111 is 1 Hour and for the code 112 its 2 hours, My real scenario is different I just put sample table and records
May 31, 2016 at 4:16 am
Onthe assumption (dangerous I know!) that the inactive records always appear as two consecutive rows when ordered by site/datetime then the following mayhelp
CREATE TABLE [dbo].[Temp2](
[SiteCode] [int] NULL,
[StatusTime] [datetime] NULL,
[Status] [char](2) NULL
)
insert into Temp2 values(111,'2016-05-30 12:30:00.000','A')
insert into Temp2 values(111,'2016-05-30 13:02:00.000','A')
insert into Temp2 values(111,'2016-05-30 14:02:00.000','I')
insert into Temp2 values(111,'2016-05-30 15:02:00.000','I')
insert into Temp2 values(112,'2016-05-30 19:17:00.000','A')
insert into Temp2 values(112,'2016-05-30 20:25:00.000','A')
insert into Temp2 values(112,'2016-05-30 21:02:00.000','I')
insert into Temp2 values(112,'2016-05-30 23:02:00.000','I')
--added more rows to test for multiple inactive sessions per site
insert into Temp2 values(113,'2016-05-30 02:02:00.000','A')
insert into Temp2 values(113,'2016-05-30 03:06:00.000','I')
insert into Temp2 values(113,'2016-05-30 04:00:00.000','I')
insert into Temp2 values(113,'2016-05-30 08:06:00.000','A')
insert into Temp2 values(113,'2016-05-30 10:02:00.000','A')
insert into Temp2 values(113,'2016-05-30 12:12:00.000','I')
insert into Temp2 values(113,'2016-05-30 14:12:00.000','I')
insert into Temp2 values(113,'2016-05-30 15:02:00.000','A');
WITH cte as (
SELECT sitecode,
StatusTime,
status,
lag(statustime, 1) OVER(PARTITION BY sitecode ORDER BY statustime) st_lag,
lag(status, 1) OVER(PARTITION BY sitecode ORDER BY statustime) s_lag
FROM temp2
)
SELECT sitecode,
st_lag start_inactive,
StatusTime end_Inactive,
DATEDIFF(hour, st_lag, statustime) Inactive_hours
FROM cte
WHERE(status = 'I') AND (s_lag = 'I');
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 31, 2016 at 4:37 am
I trust you understand that DATEDIFF returns the count of the specified datepart boundaries crossed between the specified startdate and enddate.
eg...even though the start and end times below are only 2 minutes apart, datediff for hours returns 1
DECLARE @start datetime = '2016-05-30 09:59:00.000',
@end datetime = '2016-05-30 10:01:00.000'
SELECT DATEDIFF(hour,@start,@end)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
May 31, 2016 at 7:59 am
Hi John
Still its not meet my requirement, i need only the total hours as result. In the below query i get the total hours but i am not able to filter only the status = "I"
with cteMax as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime desc
), cteMin as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime
)
select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin
inner join cteMax on cteMin.SiteCode = cteMax.SiteCode
May 31, 2016 at 8:19 am
shagil.a.gopinath (5/31/2016)
Hi JohnStill its not meet my requirement, i need only the total hours as result. In the below query i get the total hours but i am not able to filter only the status = "I"
with cteMax as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime desc
), cteMin as
(
SELECT TOP 1 StatusTime, SiteCode
FROM [Temp2]
WHERE [CreateDate] > DateAdd(DAY, -1, GETDATE()) and [CreateDate]<=GETDATE() and SiteCode = '11119'
and Status = 'I'
group by Status, StatusTime, siteCode
order by StatusTime
)
select DATEDIFF(HOUR,MIN(cteMin.StatusTime), MAX(cteMax.StatusTime)), MIN(cteMin.StatusTime), MAX(cteMax.StatusTime) from cteMin
inner join cteMax on cteMin.SiteCode = cteMax.SiteCode
assuming you are replying to me (my name is not John by the way)
the code I gave you gives the results you asked for based on your sample data (Result expected for the code 111 is 1 Hour and for the code 112 its 2 hours,)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
June 1, 2016 at 12:07 pm
; WITH MyData AS
(
SELECT
SiteCode,
RowSeq = row_number() --- enumerate StatusTime
OVER (PARTITION BY SiteCode
ORDER BY StatusTime)
, StatusTime, [Status]
FROM [Temp2]
)
SELECT---- Show two consecutive states next to each other
SiteCode = A.SiteCode
, FromRow = A.RowSeq
, ToRow = B.RowSeq
, FromStatus = A.[Status]
, ToStatus = B.[Status]
, FromTime = A.StatusTime
, ToTime = B.StatusTime
-- and calculate difference in seconds, or convert it to minutes or hours:
, TimeDiff_Seconds = datediff (s,A.StatusTime,B.StatusTime)
, TimeDiff_Minutes = 1.0 * datediff (s,A.StatusTime,B.StatusTime)/60
, TimeDiff_Hours = 1.0 * datediff (s,A.StatusTime,B.StatusTime)/3600
FROM MyData AS A
JOIN MyData AS B ON A.RowSeq = B.RowSeq-1 AND A.SiteCode = B.SiteCode
ORDER BY A.SiteCode, A.RowSeq
;
The result of this T-SQL statement is:
SiteCode FromRow ToRow FromStatus ToStatus FromTime ToTime Seconds Minutes Hours
----------- -------- ----- ---------- -------- ---------------- ---------------- ------- ---------- --------
111 1 2 A A 2016-05-30 12:30 2016-05-30 13:02 1920 32.00 0.533333
111 2 3 A I 2016-05-30 13:02 2016-05-30 14:02 3600 60.00 1.000000
111 3 4 I I 2016-05-30 14:02 2016-05-30 15:02 3600 60.00 1.000000
112 1 2 A A 2016-05-30 19:17 2016-05-30 20:25 4080 68.00 1.133333
112 2 3 A I 2016-05-30 20:25 2016-05-30 21:02 2220 37.00 0.616666
112 3 4 I I 2016-05-30 21:02 2016-05-30 23:02 7200 120.00 2.000000
(6 row(s) affected)
I believe you can figure out the rest...
June 4, 2016 at 12:06 pm
Thank you so much, This is the perfect solution 🙂
Hats off
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply