Calculate milliseconds iterativelly (Network Steve Forum)

Calculate milliseconds iterativelly

Experts,

CREATE TABLE [dbo].[TimeStats]
(
	[TimingStatsID] [int] IDENTITY(1,1) NOT NULL,
	[TimingObject] [varchar](50) NOT NULL,
	[TimingStep] [int] NULL,
	[TimingBatch] [uniqueidentifier] NOT NULL,
	[Time] [datetime] NOT NULL
)

INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 1, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:56:56.533' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 2, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:56:58.770' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 3, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:01.420' )

INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 1, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:14.067' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 2, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:14.640' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 3, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:15.287' )

SELECT	*
		--, (TimingStep2 - TimingStep1 for each TimingBatch value) AS 'MS'
FROM	[dbo].[TimeStats]
ORDER BY	[TimingStep], [Time]

SELECT	'1' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '1' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:56.533' AS 'Time', '2014-02-07 13:56:58.770 - 2014-02-07 13:56:56.533' AS 'Ms'
UNION ALL
SELECT	'4' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '1' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 16:29:14.067' AS 'Time', '' AS 'Ms'
UNION ALL
SELECT	'2' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '2' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', NULL AS 'Ms'

DROP TABLE [dbo].[TimeStats]

Can anybody please help me out?

Thank You

Regards,

February 7th, 2014 6:49pm

I'm not entirely sure exactly what you are looking for, but to get the difference between two datetimes, you use the DATEDIFF function. You can specify the units you want the difference in. In your case it is ms (for milliseconds). See the documentation for the DATEDIFF function. So the difference between the [Time] values for TimingStep 1 and 2 for each TimingBatch value can be done by

Select t.TimingBatch, DateDiff(ms, Max(Case When t.TimingStep = 1 Then t.[Time] End), Max(Case When t.TimingStep = 2 Then t.[Time] End))
From dbo.TimeStats t
Where t.TimingStep In (1,2)
Group By t.TimingBatch;

Tom

Free Windows Admin Tool Kit Click here and download it now

February 7th, 2014 9:02pm

Nope you are hard cording into CASE statement, I was looking something dynamic.

What if I'have more values something like :-

CREATE TABLE [dbo].[TimeStats]
(
	[TimingStatsID] [int] IDENTITY(1,1) NOT NULL,
	[TimingObject] [varchar](50) NOT NULL,
	[TimingStep] [int] NULL,
	[TimingBatch] [uniqueidentifier] NOT NULL,
	[Time] [datetime] NOT NULL
)

INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 1, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:56:56.533' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 2, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:56:58.770' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 3, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:01.420' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 4, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:06.640' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 5, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:06.920' )

INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 1, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:14.067' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 2, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:14.640' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 3, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:15.287' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 4, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:15.707' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 5, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:16.005' )

SELECT	*
		--, (TimingStep2 - TimingStep1 for each TimingBatch value) AS 'MS'
FROM	[dbo].[TimeStats]
ORDER BY	[TimingStep], [Time]

--Select t.TimingBatch, DateDiff(ms, Max(Case When t.TimingStep = 1 Then t.[Time] End), Max(Case When t.TimingStep = 2 Then t.[Time] End))
--From dbo.TimeStats t
--Where t.TimingStep In (1,2)
--Group By t.TimingBatch;

SELECT	'1' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '1' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:56.533' AS 'Time', '2014-02-07 13:56:58.770 - 2014-02-07 13:56:56.533' AS 'Ms'
UNION ALL
SELECT	'6' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '1' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 16:29:14.067' AS 'Time', '2014-02-07 16:29:14.640 - 2014-02-07 16:29:14.067' AS 'Ms'
UNION ALL
SELECT	'2' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '2' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 13:57:01.420 - 2014-02-07 13:56:58.770' AS 'Ms'
UNION ALL
SELECT	'7' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '2' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 16:29:15.287 - 2014-02-07 16:29:14.640' AS 'Ms'
UNION ALL
SELECT	'3' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '3' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 13:57:06.640 - 2014-02-07 13:57:01.420' AS 'Ms'
UNION ALL
SELECT	'8' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '3' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 16:29:15.707 - 2014-02-07 16:29:15.287' AS 'Ms'
UNION ALL
SELECT	'4' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '4' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 13:57:06.920 - 2014-02-07 13:57:06.640' AS 'Ms'
UNION ALL
SELECT	'9' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '4' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 16:29:16.007 - 2014-02-07 16:29:15.707' AS 'Ms'
UNION ALL
SELECT	'5' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '5' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', NULL AS 'Ms'
UNION ALL
SELECT	'10' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '5' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', NULL AS 'Ms'


DROP TABLE [dbo].[TimeStats]

Can somebody help me with the Ms column values in milliseconds or seconds?

Thanks

Regards,

Kumar

February 7th, 2014 10:58pm

Try Common table express

I guess this would help you

CREATE TABLE [dbo].[TimeStats] (
[TimingStatsID] [int] IDENTITY(1, 1) NOT NULL
,[TimingObject] [varchar](50) NOT NULL
,[TimingStep] [int] NULL
,[TimingBatch] [uniqueidentifier] NOT NULL
,[Time] [datetime] NOT NULL
)

INSERT INTO [dbo].[TimeStats]
VALUES (
'gpmp'
,1
,'492939F4-611B-4BBC-9DF4-142F4AB774D9'
,'2014-02-07 13:56:56.533'
)

INSERT INTO [dbo].[TimeStats]
VALUES (
'gpmp'
,2
,'492939F4-611B-4BBC-9DF4-142F4AB774D9'
,'2014-02-07 13:56:58.770'
)

INSERT INTO [dbo].[TimeStats]
VALUES (
'gpmp'
,3
,'492939F4-611B-4BBC-9DF4-142F4AB774D9'
,'2014-02-07 13:57:01.420'
)

INSERT INTO [dbo].[TimeStats]
VALUES (
'gpmp'
,1
,'95853BD6-7B77-4142-8E43-F4C528B67AF3'
,'2014-02-07 16:29:14.067'
)

INSERT INTO [dbo].[TimeStats]
VALUES (
'gpmp'
,2
,'95853BD6-7B77-4142-8E43-F4C528B67AF3'
,'2014-02-07 16:29:14.640'
)

INSERT INTO [dbo].[TimeStats]
VALUES (
'gpmp'
,3
,'95853BD6-7B77-4142-8E43-F4C528B67AF3'
,'2014-02-07 16:29:15.287'
)

SELECT *
--, (TimingStep2 - TimingStep1 for each TimingBatch value) AS 'MS'
FROM [dbo].[TimeStats]
ORDER BY [TimingStep]
,[Time]

SELECT '1' AS 'TimingStatsID'
,'gpmp' AS 'TimingObject'
,'1' AS 'TimingStep'
,'492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch'
,'2014-02-07 13:56:56.533' AS 'Time'
,'2014-02-07 13:56:58.770 - 2014-02-07 13:56:56.533' AS 'Ms'

UNION ALL

SELECT '4' AS 'TimingStatsID'
,'gpmp' AS 'TimingObject'
,'1' AS 'TimingStep'
,'95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch'
,'2014-02-07 16:29:14.067' AS 'Time'
,'' AS 'Ms'

UNION ALL

SELECT '2' AS 'TimingStatsID'
,'gpmp' AS 'TimingObject'
,'2' AS 'TimingStep'
,'492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch'
,'2014-02-07 13:56:58.770' AS 'Time'
,NULL AS 'Ms'
GO

WITH cte
AS (
SELECT Min(isnull([TimingStep], 0)) AS MinStep
,Max(isnull([TimingStep], 0)) AS MaxStep
,TimingBatch
FROM TimeStats
GROUP BY TimingBatch
)
SELECT TimingStatsID
,TimingObject
,cte.TimingBatch
,cte.MaxStep - cte.MinStep AS MS
FROM cte
Inner JOIN TimeStats ON cte.TimingBatch = TimeStats.TimingBatch
GO

DROP TABLE [dbo].[TimeStats]

Mark as ans if you find it useful

Shridhar J Joshi

Free Windows Admin Tool Kit Click here and download it now

February 7th, 2014 11:27pm

CREATE TABLE [dbo].[TimeStats]
(
	[TimingStatsID] [int] IDENTITY(1,1) NOT NULL,
	[TimingObject] [varchar](50) NOT NULL,
	[TimingStep] [int] NULL,
	[TimingBatch] [uniqueidentifier] NOT NULL,
	[Time] [datetime] NOT NULL
)

INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 1, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:56:56.533' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 2, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:56:58.770' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 3, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:01.420' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 4, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:06.640' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 5, '492939F4-611B-4BBC-9DF4-142F4AB774D9', '2014-02-07 13:57:06.920' )

INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 1, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:14.067' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 2, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:14.640' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 3, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:15.287' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 4, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:15.707' )
INSERT INTO [dbo].[TimeStats] VALUES ( 'gpmp', 5, '95853BD6-7B77-4142-8E43-F4C528B67AF3', '2014-02-07 16:29:16.005' )

SELECT	*
		--, (TimingStep2 - TimingStep1 for each TimingBatch value) AS 'MS'
FROM	[dbo].[TimeStats]
ORDER BY	[TimingStep], [Time]





SELECT	'1' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '1' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:56.533' AS 'Time', '2014-02-07 13:56:58.770 - 2014-02-07 13:56:56.533' AS 'Ms'
UNION ALL
SELECT	'6' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '1' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 16:29:14.067' AS 'Time', '2014-02-07 16:29:14.640 - 2014-02-07 16:29:14.067' AS 'Ms'
UNION ALL
SELECT	'2' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '2' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 13:57:01.420 - 2014-02-07 13:56:58.770' AS 'Ms'
UNION ALL
SELECT	'7' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '2' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 16:29:15.287 - 2014-02-07 16:29:14.640' AS 'Ms'
UNION ALL
SELECT	'3' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '3' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 13:57:06.640 - 2014-02-07 13:57:01.420' AS 'Ms'
UNION ALL
SELECT	'8' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '3' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 16:29:15.707 - 2014-02-07 16:29:15.287' AS 'Ms'
UNION ALL
SELECT	'4' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '4' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 13:57:06.920 - 2014-02-07 13:57:06.640' AS 'Ms'
UNION ALL
SELECT	'9' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '4' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', '2014-02-07 16:29:16.007 - 2014-02-07 16:29:15.707' AS 'Ms'
UNION ALL
SELECT	'5' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '5' AS 'TimingStep', '492939F4-611B-4BBC-9DF4-142F4AB774D9' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', NULL AS 'Ms'
UNION ALL
SELECT	'10' AS 'TimingStatsID', 'gpmp' AS 'TimingObject', '5' AS 'TimingStep', '95853BD6-7B77-4142-8E43-F4C528B67AF3' AS 'TimingBatch', '2014-02-07 13:56:58.770' AS 'Time', NULL AS 'Ms'







--Query

;with mycte as (SELECT	[TimingStatsID],[TimingObject] ,[TimingStep] ,[TimingBatch],Time,
row_number() Over(Partition By TimingStep ORDER BY [Time]) rn		
FROM	[dbo].[TimeStats]
)

select m1.[TimingStatsID],
	m1.[TimingObject] ,
	m1.[TimingStep] ,
	m1.[TimingBatch],
	 datediff(ms,m2.[Time],m1.[Time]) as ms,
	m2.[Time],m1.[Time] 
	from mycte m1 left join mycte m2 on m1.rn=m2.rn and m1.TimingStep=m2.TimingStep-1




DROP TABLE [dbo].[TimeStats]

February 7th, 2014 11:33pm

This topic is archived. No further replies will be accepted.