CODEWITHSUNDEEP
sqlsql-serverpivot
anshul
anshul

Reputation: 63

transform sql rows to column

I have a very unusual request. I have some filtered data in a table, columns being ID, Date, and Event. Event is an XML column where in one of the tag is StartWork and EndWork. From huge data I have filtered out data for a request. My Sample data has 6 rows, in sequence of StartWork, EndWork, StartWork, EndWork and so on. What exactly I want to do is find time difference between each combination. I mean, EndWork - StartWork = Difference. Next EndWork - StartWork = Difference2 and so on.

enter image description here

Basically I want, 2-1, 4-3, 6-5 and so on. I tried doing it with Pivot, but couldn't get desired result

Upvotes: 1

Views: 83

Answers (3)

Hisham Maudarbocus
Hisham Maudarbocus

Reputation: 620

Suppose "tbl" is your table name. Here you go:

SELECT t.datecreated - t2.datecreated
FROM tbl t, tbl t2
WHERE t.id IN
    (SELECT id FROM tbl WHERE _eventdata LIKE ('%EndWork%'))
    AND t2.id = t.id -1

Please flag it as answered if it works...

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

This should help you: 

declare @xmlStart XML='<data><status>StartWork</status></data>';
declare @xmlENd XML='<data><status>EndWork</status></data>';

declare @tbl TABLE(id INT,datecreated DATETIME,eventdata XML);
INSERT INTO @tbl VALUES(1,{ts'2015-07-29 09:17:34'},@xmlStart)
                      ,(2,{ts'2015-07-29 09:20:24'},@xmlEnd)
                      ,(3,{ts'2015-07-29 10:05:41'},@xmlStart)
                      ,(4,{ts'2015-07-29 10:18:34'},@xmlEnd);

WITH resolvedCTE AS
(
    SELECT TOP 100 PERCENT id,datecreated,eventdata.value('(/data/status)[1]','varchar(max)') AS EventStatus FROM @tbl
)
,StartEvnets AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY datecreated) AS inx,id,datecreated FROM resolvedCTE WHERE EventStatus='StartWork' 
)
,EndEvnets AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY datecreated) AS inx,id,datecreated FROM resolvedCTE WHERE EventStatus='EndWork' 
)SELECT StartEvnets.id, CAST(EndEvnets.datecreated - StartEvnets .datecreated AS TIME)
FROM StartEvnets
INNER JOIN EndEvnets ON StartEvnets.inx =EndEvnets.inx

Upvotes: 1

Hercule
Hercule

Reputation: 186

Select DateDiff(minute,StartWork.datacreated,EndWork.datacreated)
from 
(Select datacreated,LineNb=row_number() over(Order by datacreated) from Table where eventdata.value('(/data/status/text())[1]','varchar(15)')='StartWork') StartWork
INNER JOIN 
(Select datacreated,LineNb=row_number() over(Order by datacreated) from Table where eventdata.value('(/data/status/text())[1]','varchar(15)')='EndWork') EndWork
ON StartWork.LineNb=EndWork.LineNb

Upvotes: 2

Related Questions