Creating record from multiple records pt 2

Question

Creating record from multiple records pt 2

ByronOne

SSCarpal Tunnel

Points: 4656
More actions
January 31, 2018 at 3:14 am

#343177

Hi
I have the following table which I need to do the following to:
1. Create a single record for each user.
2. For each user calculate the time difference between the [VisitStartDateTime] and the [ActivityStartDateTime], then the [ActivityStartDateTime] and the [ActivityEndDateTime], and finally the [ActivityEndDateTime] and the [VisitEndDateTime]. Each activity creates a new record in the system.
3. Some users will have multiple activities and each will need to have the times of each activity measured.
4. Some users will have visited but not taken part in any activities in which case they will only have one record and we only need to measure the time difference between the [VisitStartDateTime] and the [VisitEndDateTime].
Thanks in advance.
BO
DROP TABLE #UserActivity
GO
CREATE TABLE #UserActivity (
[User] int NOT NULL,
[VisitStartDateTime] datetime null,
[VisitEndDateTime] datetime null,
[ActivityStartDateTime] datetime null,
[ActivityEndDateTime] datetime null,
[Activity] varchar(10) null
)
GOINSERT #UserActivity ([User], [VisitStartDateTime], [VisitEndDateTime], [ActivityStartDateTime],
[ActivityEndDateTime], [Activity])
VALUES
(001, CAST('2017-01-01 08:30' AS datetime), CAST('2017-01-01 10:30' AS datetime), CAST('2017-01-01 08:45' AS datetime),CAST('2017-01-01 10:00' AS datetime), 'ACT003' )
, (001, CAST('2017-01-01 08:30' AS datetime), CAST('2017-01-01 10:30' AS datetime), CAST('2017-01-01 10:00' AS datetime),CAST('2017-01-01 10:30' AS datetime), 'ACT001' )
, (002, CAST('2017-04-01 10:30' AS datetime), CAST('2017-04-01 12:30' AS datetime), NULL , NULL , '' )
, (003, CAST('2017-05-01 10:30' AS datetime), CAST('2017-05-01 13:30' AS datetime), CAST('2017-05-01 10:30' AS datetime),CAST('2017-05-01 11:30' AS datetime), 'ACT001' )
, (003, CAST('2017-05-01 10:30' AS datetime), CAST('2017-05-01 13:30' AS datetime), CAST('2017-05-01 11:45' AS datetime),CAST('2017-05-01 12:30' AS datetime), 'ACT002' )
, (003, CAST('2017-05-01 10:30' AS datetime), CAST('2017-05-01 13:30' AS datetime), CAST('2017-05-01 12:30' AS datetime),CAST('2017-05-01 13:15' AS datetime), 'ACT010' )
, (004, CAST('2017-09-01 09:30' AS datetime), CAST('2017-09-01 10:30' AS datetime),NULL ,NULL ,'')
, (007, CAST('2017-09-01 10:30' AS datetime), CAST('2017-09-01 12:30' AS datetime), CAST('2017-09-01 10:50' AS datetime),CAST('2017-09-01 11:20' AS datetime), 'ACT001' )
, (007, CAST('2017-09-01 10:30' AS datetime), CAST('2017-09-01 12:30' AS datetime), CAST('2017-09-01 11:30' AS datetime),CAST('2017-09-01 12:14' AS datetime), 'ACT012' )
, (005, CAST('2017-07-01 10:30' AS datetime), CAST('2017-07-01 12:30' AS datetime), CAST('2017-07-01 10:30' AS datetime),CAST('2017-07-01 10:50' AS datetime), 'ACT001' )
, (005, CAST('2017-07-01 10:30' AS datetime), CAST('2017-07-01 12:30' AS datetime), CAST('2017-07-01 11:00' AS datetime),CAST('2017-07-01 11:45' AS datetime), 'ACT003' )
, (005, CAST('2017-07-01 10:30' AS datetime), CAST('2017-07-01 12:30' AS datetime), CAST('2017-07-01 11:53' AS datetime),CAST('2017-07-01 12:30' AS datetime), 'ACT010' )
, (006, CAST('2017-09-01 09:45' AS datetime), CAST('2017-09-01 11:30' AS datetime), CAST('2017-09-01 09:50' AS datetime),CAST('2017-09-01 10:30' AS datetime), 'ACT010' )
, (006, CAST('2017-09-01 09:45' AS datetime), CAST('2017-09-01 11:30' AS datetime), CAST('2017-09-01 10:33' AS datetime),CAST('2017-09-01 11:17' AS datetime), 'ACT004' )
select * from #UserActivity

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

What are you asking for help with here - calculating time differences, or pivoting information for each user into a single row? What have you already tried?

John

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 2

Hi john

It is actually both of those things. I need a single row for each user and columns measuring the time difference for each activity (if they have no activity then just the time difference between start of the visit and the end of the visit).

I do not want the result to be in a single column (like xml path) but a different column for each timestamp (visit start, activity start, activity end, visit end etc) and measurement of time difference for each of these 'events'.

The script you provided previously used only one date field where this query will need to look at different date fields (the activity fields can also be numerous but probably no more than 10).

BO

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

My previous script showed you how to pivot and how to do time differences, so what are you stuck on? It would also be helpful if you would show what results you expect from the sample data you provided.

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

ByronOne - Wednesday, January 31, 2018 4:35 AM
Hi john
It is actually both of those things. I need a single row for each user and columns measuring the time difference for each activity (if they have no activity then just the time difference between start of the visit and the end of the visit).
I do not want the result to be in a single column (like xml path) but a different column for each timestamp (visit start, activity start, activity end, visit end etc) and measurement of time difference for each of these 'events'.
The script you provided previously used only one date field where this query will need to look at different date fields (the activity fields can also be numerous but probably no more than 10).
BO

Please post expected results - *exactly* what you expect from your sample data set. This will reduce the ambiguities which exist in your problem description.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ByronOne SSCarpal Tunnel Points: 4656 More actions · Answer 5

Yes, you are of course right, tweaked and now does what I need.

Thanks guys.

BO

Joe Torre SSChampion Points: 10248 More actions · Answer 6

@ByronOne,
Post the working code for the benefit of others please.