January 29, 2018 at 3:28 pm
Hi
From the table script below I would like to write a query that creates one row for each User. Each row should contain the User, the Activity DateTimes and the date/time difference between each activity DateTime. So for example User 2 would have 4 dates with the difference in time between date 1 and 2 , 2 and 3, and 3 and 4.
Any help much appreciated.
BO
CREATE TABLE #UserActivity (
[User] int NOT NULL,
ActivityTime datetime NOT NULL
)
GOINSERT #UserActivity ([User], ActivityTime)
VALUES (1, CAST('2017-01-01 10:30' AS datetime))
, (1, CAST('2017-01-03 10:45' AS datetime))
, (1, CAST('2017-01-05 13:15' AS datetime))
, (1, CAST('2017-01-06 15:30' AS datetime))
, (1, CAST('2017-01-08 16:15' AS datetime))
, (1, CAST('2017-01-09 17:00' AS datetime))
, (2, CAST('2017-01-02 08:00' AS datetime))
, (2, CAST('2017-01-03 10:45' AS datetime))
, (2, CAST('2017-01-04 13:00' AS datetime))
, (2, CAST('2017-01-07 16:45' AS datetime))
, (3, CAST('2017-01-01 8:25' AS datetime))
, (3, CAST('2017-01-03 11:30' AS datetime))
, (3, CAST('2017-01-03 12:35' AS datetime))
, (3, CAST('2017-01-04 14:45' AS datetime));
January 29, 2018 at 4:37 pm
Could you post the CREATE TABLE script for the expected result?
It seems that you could do this using a combination of the windowing function LAG() and STUFF() to concatenate all the values into a single string. But before I go down that road, a sample correct "answer" (concatenated record) would be really helpful.
January 30, 2018 at 12:34 am
Here is one way of doing this
😎
USE TEEST;
GO
SET NOCOUNT ON;
CREATE TABLE #UserActivity (
[User] int NOT NULL,
ActivityTime datetime NOT NULL
)
GO
INSERT #UserActivity ([User], ActivityTime)
VALUES (1, CAST('2017-01-01 10:30' AS datetime))
, (1, CAST('2017-01-03 10:45' AS datetime))
, (1, CAST('2017-01-05 13:15' AS datetime))
, (1, CAST('2017-01-06 15:30' AS datetime))
, (1, CAST('2017-01-08 16:15' AS datetime))
, (1, CAST('2017-01-09 17:00' AS datetime))
, (2, CAST('2017-01-02 08:00' AS datetime))
, (2, CAST('2017-01-03 10:45' AS datetime))
, (2, CAST('2017-01-04 13:00' AS datetime))
, (2, CAST('2017-01-07 16:45' AS datetime))
, (3, CAST('2017-01-01 8:25' AS datetime))
, (3, CAST('2017-01-03 11:30' AS datetime))
, (3, CAST('2017-01-03 12:35' AS datetime))
, (3, CAST('2017-01-04 14:45' AS datetime));
SELECT
UA.[User]
,(
SELECT
'' + CONCAT( CONVERT(VARCHAR(10),SUA.ActivityTime,120)
,CHAR(32)
,ISNULL(CONVERT(VARCHAR(10),DATEDIFF(HOUR,SUA.ActivityTime,LEAD(SUA.ActivityTime,1) OVER
(
PARTITION BY SUA.[User]
ORDER BY SUA.ActivityTime
)),0) + CHAR(72),'')
,CHAR(32)
)
FROM #UserActivity SUA
WHERE SUA.[User] = UA.[User]
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(1000)') AS ActivityString
FROM #UserActivity UA
GROUP BY UA.[User]
DROP TABLE #UserActivity;
Output
User ActivityString
1 2017-01-01 48H 2017-01-03 51H 2017-01-05 26H 2017-01-06 49H 2017-01-08 25H 2017-01-09
2 2017-01-02 26H 2017-01-03 27H 2017-01-04 75H 2017-01-07
3 2017-01-01 51H 2017-01-03 1H 2017-01-03 26H 2017-01-04
January 30, 2018 at 2:37 am
Hi Eirikiur
This is a great solution but not quite what I was after.
Is it possible to have each of the date fields in spartae columns along wth the time differenec calculations rather than inone xml column?
BO
January 30, 2018 at 3:10 am
Will each user always have exactly four activity dates associated with him or her?
John
January 30, 2018 at 3:27 am
Hi John
No, they can have many diffrent dates.
BO
January 30, 2018 at 3:44 am
So you want a table with an indeterminate number of columns? That's going to be a horrible mess of dynamic SQL. I recommend you stick with Eirikur's solution.
John
January 30, 2018 at 3:56 am
Is it possible to limit it and just bring back the first 10 date columns with calculations then?
January 30, 2018 at 4:04 am
Yes, should be possible. Are you really on SQL Server 2008?
John
January 30, 2018 at 4:16 am
Yes, afraid so ;(
January 30, 2018 at 4:50 am
Here's how you can do it for the first four times for user. You'll be able to see how to extend it to ten or any other number. I've shown the first time and, for times after that, the difference in hours since the previous. You can tweak it to show all times and differences if you prefer.
WITH Partitioned AS (
SELECT
[User]
, ActivityTime
, ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY ActivityTime) AS RowNo
FROM #UserActivity
)
, NowandNext AS (
SELECT
p1.[User]
, p1.ActivityTime
, p1.RowNo
, DATEDIFF(HOUR,p2.ActivityTime,p1.ActivityTime) AS HoursSinceLast
FROM Partitioned p1
LEFT JOIN Partitioned p2 ON
p1.[User] = p2.[User] AND p1.RowNo = p2.RowNo + 1
)
SELECT
[User]
, MAX(CASE WHEN RowNo = 1 THEN ActivityTime ELSE NULL END) AS ActivityTime1
, MAX(CASE WHEN RowNo = 2 THEN HoursSinceLast ELSE NULL END) AS HoursSinceFirst
, MAX(CASE WHEN RowNo = 3 THEN HoursSinceLast ELSE NULL END) AS HoursSinceSecond
, MAX(CASE WHEN RowNo = 4 THEN HoursSinceLast ELSE NULL END) AS HoursSinceThird
FROM NowandNext
GROUP BY [User]
John
January 30, 2018 at 5:52 am
Many thanks John - really, really helpful.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply