how to get time duration in minute based on a value.

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

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

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.

SQL

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

Posted 28-Aug-14 23:27pm

himanshu034

Updated 29-Aug-14 4:52am

Mike Meinz

v3

Add a Solution

Comments

[no name] 29-Aug-14 5:30am

http://www.codeproject.com/Questions/812975/Fetch-Total-duration-of-time-in-minutes-based-on-a?arn=5

himanshu034 29-Aug-14 5:57am

But I have already found this result with my query. I want to get the required result. If Load_1 value changes from 1 to 0 then that time duration will be calculated in 1.
If the Load_1 value changes 0 to 1 then it should be calculated in 0. How can i write a query to do the same. Please help..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mike Meinz · Accepted Answer · 2014-08-29T04:14:00

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

SQL

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

Populate the table

SQL

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');

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

SQL

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