如何根据值获得以分钟为单位的持续时间。 [英] how to get time duration in minute based on a value.

问题描述

我有一个表格，其中存在以下值。

I have a table in which the following values exist.

Client_Id   Load_1  RTC
TS1733         1   8/21/2014 16:10
TS1733         1   8/21/2014 17:00
TS1733         1   8/21/2014 17:10
TS1733         1   8/21/2014 17:20
TS1733         1   8/21/2014 17:30
TS1733         0   8/21/2014 17:40
TS1733         0   8/21/2014 17:50
TS1733         1   8/21/2014 18:00
TS1733         1   8/21/2014 18:10
TS1733         1   8/21/2014 18:20
TS1733         1   8/21/2014 18:40

我想在1和0时获取Client_ID的总持续时间。



结果

I want to fetch total duration of time of Client_ID when it was 1 and 0.

Result

Client_ID   Load_1  TimeDuration(Minute)
TS1733            1     130
TS1733            0      20

如何获得上述结果。请帮助..



我写了以下查询，但它没有给我想要的结果。

How can I get the above result. Please help..

I have written the following query but it is not giving me desired result.

With TimeDuration
AS
(
SELECT Client_Id,Load_1, DATEDIFF(MINUTE, MIN(RTC), MAX(RTC)) AS Duration
FROM TSI_T_ClientDetailMaster
WHERE Client_Id='TS1733' AND CONVERT(DATE,RTC)='8/21/2014'
GROUP BY Load_1,Client_Id
)
SELECT r.Load_1,r.Client_Id,
  TimeDuration.Duration AS TotalMinutes
FROM TSI_T_ClientDetailMaster AS r
INNER JOIN TimeDuration ON TimeDuration.Client_Id = r.Client_Id AND TimeDuration.Load_1=r.Load_1
GROUP BY r.Load_1,r.Client_Id, TimeDuration.Duration
ORDER BY r.Load_1

Load_1	Client_Id	TotalMinutes
0	  TS1733	         10
1	  TS1733	         150

推荐答案

比我聪明的人可能能够提出更优雅的解决方案。这就是我解决这类问题的方法。请注意，我将 load_1 = 0的分钟值和 load_1 = 1作为一行中的列而不是两个单独的行。





创建一个表

Somebody smarter than me may be able to come up with a more elegant solution. This is how I solve this type of problem. Note that I return the minutes value for load_1=0 and for load_1=1 as columns in one row rather than as two separate rows.


Create a table

USE [Test]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TSI_T_ClientDetailMaster](
    [Client_Id] [nvarchar](50) not null,
	[Load_1] [int] NOT NULL,
	[RTC] [datetime] NOT NULL
	) ON [PRIMARY]
GO

填充表格

truncate table tsi_T_ClientDetailMaster;
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 16:10');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 17:00');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 17:10');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 17:20');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 17:30');
insert into tsi_T_ClientDetailMaster values('TS1733',0,'8/21/2014 17:40');
insert into tsi_T_ClientDetailMaster values('TS1733',0,'8/21/2014 17:50');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 18:00');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 18:10');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 18:20');
insert into tsi_T_ClientDetailMaster values('TS1733',1,'8/21/2014 18:40');

用于计算load_1 = 0且load_1 = 1的分钟的Transact-SQL

您可以轻松将其转换为存储过程。

Transact-SQL to compute the minutes where load_1=0 and where load_1=1
You could easily turn this into a Stored Procedure.

Declare @load BIT
Declare @RTC DateTime
Declare @lastLoad BIT
Declare @BeginRTC DateTime
Declare @FIRST_TIME BIT
Declare @Time0 INT = 0
Declare @Time1 INT = 0
Declare @Client_ID NVARCHAR(50) = 'TS1733'
Declare @SelectDate Date = '2014-08-21'
Declare myRows CURSOR FOR SELECT load_1, rtc from TSI_T_ClientDetailMaster  Where Client_Id=@Client_Id and CONVERT(DATE,RTC)=@SelectDate Order By Client_Id,RTC
SET @FIRST_TIME=1
OPEN myRows
--Get First Row
FETCH NEXT FROM myRows INTO @load, @RTC
WHILE @@FETCH_STATUS = 0
BEGIN
	IF @FIRST_TIME=1
		BEGIN
		SET @FIRST_TIME=0
		--Save the current row data
		SET @lastLoad=@load
		SET @BeginRTC=@RTC
		END
	ELSE
	    BEGIN
		IF NOT (@lastLoad=@load)
                        -- A change in the load_1 value occurred
			BEGIN
			-- Change from 0 to 1 or 1 to 0
			If @lastLoad=0
				BEGIN
                                -- @lastLoad = 0
				SET @Time0=@Time0 + DateDiff(MINUTE,@BeginRTC,@RTC)
				SET @BeginRTC=@RTC
				SET @lastLoad=@load
				END
			ELSE
				BEGIN
				-- @lastLoad = 1
				SET @Time1=@Time1 + DateDiff(MINUTE,@BeginRTC,@RTC)
				SET @BeginRTC=@RTC
				SET @lastLoad=@load
				END
			END
		END
        -- Get next row
	FETCH NEXT FROM myRows INTO @load, @RTC
END
-- Compute minutes for last group
If @lastLoad=0
	BEGIN
        -- @lastLoad = 0
	SET @Time0=@Time0 + DateDiff(MINUTE,@BeginRTC,@RTC)
	END
ELSE
	BEGIN
	-- @lastLoad = 1
	SET @Time1=@Time1 + DateDiff(MINUTE,@BeginRTC,@RTC)
	END
-- Close the CURSOR
CLOSE myRows
-- DEALLLOCATE the CURSOR
DEALLOCATE myRows
Select @Client_ID AS Client_Id,@Time0 AS Time_0,@Time1 AS Time_1

结果

The results

Client_Id    Time_0     Time_1
TS1733       20         130

这篇关于如何根据值获得以分钟为单位的持续时间。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！