add extra row for calculate time

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

add extra row for calculate time

Author

Topic

kwangba
Starting Member

7 Posts

Posted - 2012-01-22 : 11:55:13

i have table like this

EID EventTime ParameterName Value
1    2011-11-01 07:15:00.0000000     aaa    1
2    2011-11-01 17:15:00.0000000     aaa    0
3    2011-11-02 07:15:00.0000000     bbb    1
4    2011-11-03 17:15:00.0000000     bbb    0

i use row_number() over() to inner join this table and find datediff it work fine the result is
EventTime ParameterName minute
aaa    2011-11-01 07:15:00.0000000    2011-11-01 17:15:00.0000000     600
bbb    2011-11-02 07:15:00.0000000    2011-11-03 17:15:00.0000000     2040

the column value 0 is inactive 1 is active
my problem is when the active and inactive is difference day i want to add extra row to it like this
EventTime ParameterName minute
aaa    2011-11-01 07:15:00.0000000    2011-11-01 17:15:00.0000000     600
bbb    2011-11-02 07:15:00.0000000    2011-11-03 00:00:00.0000000     1005
bbb    2011-11-03 00:00:00.0000000    2011-11-03 17:15:00.0000000     1035

how should i add row like this

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-22 : 13:46:52

Can your data span more than 2 days? For example, could it be like this?

1    2011-11-01 07:15:00.0000000    	aaa    1     
2    2011-11-05 17:15:00.0000000    	aaa    0

kwangba
Starting Member

7 Posts

Posted - 2012-01-22 : 15:36:27

yes it is. but my point is i want each day have a pair of active and inactive. if datetime column is difference day should add extra column to get it pair.

from your question the result that i want should be

EventTime ParameterName minute
aaa 2011-11-01 07:15:00.0000000 2011-11-02 00:00:00.0000000
aaa 2011-11-02 00:00:00.0000000 2011-11-03 00:00:00.0000000
aaa 2011-11-03 00:00:00.0000000 2011-11-04 00:00:00.0000000
aaa 2011-11-04 00:00:00.0000000 2011-11-05 00:00:00.0000000
aaa 2011-11-05 00:00:00.0000000 2011-11-05 17:15:00.0000000

i want to add extra row when i row_number over it and day active and day inactive is difference
sorry for my bad language.
thank you for any advise

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-22 : 19:41:48

Here is a way to do it. It looks a little complicated, but all it is trying to do is slice up the time into chunks that are demarcated by date boundaries. It will work even if your active and inactive dates span more than two days.

-- Sample data
CREATE TABLE #tmp (Eid INT , EventTime DATETIME2, ParameterName VARCHAR(32), [Value] INT );

INSERT INTO #tmp VALUES (1, '2011-11-01 07:15:00.0000000','aaa',1);
INSERT INTO #tmp VALUES (2, '2011-11-01 17:15:00.0000000','aaa',0);
INSERT INTO #tmp VALUES (3, '2011-11-02 07:15:00.0000000','bbb',1);
INSERT INTO #tmp VALUES (4, '2011-11-03 17:15:00.0000000','bbb',1);
GO
	
-- QUERY
;WITH cte1 AS
(
	SELECT 
		t1.ParameterName, 
		t1.EventTime AS startTime, 
		CASE 
			WHEN DATEDIFF(dd,t2.EventTime,t1.EventTime) = 0 THEN t2.EventTime
			ELSE DATEADD(dd,1,CAST(t1.EventTime AS DATE))
		END AS EndTime,
		CASE 
			WHEN DATEDIFF(dd,t2.EventTime,t1.EventTime) = 0 THEN DATEDIFF(minute,t1.EventTime,t2.EventTime)
			ELSE DATEDIFF(minute, t1.EventTime, DATEADD(dd,1,CAST(t1.EventTime AS DATE)))
		END AS Duration,
		
		------ For bookkeeping.
		DATEDIFF(minute,t1.EventTime,t2.EventTime) AS TotalDuration,
		DATEDIFF(minute,t1.EventTime,t2.EventTime) AS RemainingDuration,
		t2.EventTime AS FinalEndTime
	FROM 
		#tmp t1 
		INNER JOIN #tmp t2 
			ON t2.ParameterName = t1.ParameterName AND t2.Eid = t1.Eid+1
		
	
	UNION ALL
	
	SELECT
		c1.ParameterName,
		c1.EndTime AS startTime,
		CASE 
			WHEN DATEDIFF(dd,FinalEndTime,EndTime) = 0 THEN FinalEndTime
			ELSE DATEADD(dd,1,CAST(EndTime AS DATE))
		END AS EndTime,
		CASE 
			WHEN DATEDIFF(dd,FinalEndTime,EndTime) = 0 THEN DATEDIFF(minute,c1.endTime,FinalEndTime)
			ELSE DATEDIFF(minute, c1.endTime, DATEADD(dd,1,CAST(c1.endTime AS DATE)))
		END AS Duration,
		
		------ For bookkeeping.
		TotalDuration,
		RemainingDuration-Duration,
		FinalEndTime
	FROM 
		cte1 c1
	WHERE
		RemainingDuration-Duration > 0

)
SELECT
	ParameterName,
	startTime,
	EndTime,
	Duration
FROM
	cte1;
	
GO
DROP TABLE #tmp
GO

kwangba
Starting Member

7 Posts

Posted - 2012-01-23 : 15:18:26

thank you sunitabeck

my real process is
1. select some range of date (usually monthly)
2. and row over get each pair to cal datediff.

my main problem is
if the range that i select is not get an even row all my query go wrong.
some of example
EID EventTime ParameterName Value
8 2011-11-01 07:15:00.0000000 aaa 0 --- get only inactive because active is in the last month
9 2011-11-01 17:15:00.0000000 aaa 1
10 2011-11-01 20:15:00.0000000 aaa 0
11 2011-11-02 07:15:00.0000000 bbb 1
12 2011-11-03 17:15:00.0000000 bbb 0
.
.
.
30 2011-11-30 11:10:00.0000000 aaa 1 --- get only active because inactive is in the next month

how can i add extra row for the first and the last row if it not get a pair.

EventTime ParameterName minute
aaa 2011-11-01 00:00:00.0000000 2011-11-01 07:15:00.0000000 435
aaa 2011-11-01 17:15:00.0000000 2011-11-01 20:15:00.0000000 180
bbb 2011-11-02 07:15:00.0000000 2011-11-03 00:00:00.0000000 1005
bbb 2011-11-03 00:00:00.0000000 2011-11-03 17:15:00.0000000 1035
aaa 2011-11-30 11:10:00.0000000 2011-12-01 00:00:00.0000000 770

another one from your query it work great but if i have like example below it will go wrong


CREATE TABLE #tmp (Eid INT , EventTime DATETIME2, ParameterName VARCHAR(32), [Value] INT );

INSERT INTO #tmp VALUES (1, '2011-11-01 07:15:00.0000000','aaa',1);
INSERT INTO #tmp VALUES (2, '2011-11-01 17:15:00.0000000','aaa',0);
INSERT INTO #tmp VALUES (3, '2011-11-02 07:15:00.0000000','bbb',1);
INSERT INTO #tmp VALUES (4, '2011-11-02 15:15:00.0000000','bbb',0);
INSERT INTO #tmp VALUES (5, '2011-11-02 16:15:00.0000000','bbb',1);
INSERT INTO #tmp VALUES (6, '2011-11-02 18:15:00.0000000','bbb',0);
GO

1. my main 2 problem add extra row in the first and last if it not get a pair.
2. like my first post.

sorry for bad language.
and thank sunitabeck

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-01-23 : 15:39:26

For fixing the example that you showed, change the join condition to:


...
	FROM 
		#tmp t1 
		INNER JOIN #tmp t2 
			ON t2.ParameterName = t1.ParameterName AND t2.Eid = t1.Eid+1 
                           AND t2.[Value] = 0 AND t1.[Value] = 1

For the missing data at the beginning and end of the month, couldn't you insert the missing rows if you know that they start at the midnight on the first of the month and/or end at the midnight of the first day of the next month?

kwangba
Starting Member

7 Posts

Posted - 2012-01-23 : 17:05:19

[code]
declare @firstDate datetime,@lastDate datetime;
select top 1 @firstDate = eventtime from #tmp where parametername = 'aaa' order by eventtime;
select top 1 @lastDate = eventtime from #tmp where parametername = 'aaa' order by eventtime desc;

if(select top 1 value from #tmp where parametername = 'aaa' order by eventtime) = 0
INSERT INTO #tmp VALUES(dateadd(dd,0,datediff(dd,0,@firstDate)),'aaa',1);
if(select top 1 value from #tmp where parametername = 'aaa' order by eventtime desc) = 1
INSERT INTO #tmp VALUES(dateadd(dd,1,datediff(dd,0,@lastDate)),'aaa',0);
[/code]

i try this but i don't know is correct?
thank you sunitabeck

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources