SQL Server - update one table with first and last rows from another table
I have a couple of tables that are used to log user activity for an application. The tables look something like this (the pseudocode from memory may not be syntactically correct):
create table activity (
sessionid uniqueidentifier not null,
created smalldatetime not null default getutcdate()
);
create table activity_details (
sessionid uniqueidentifier not null,
activity_description varchar(100) not null,
created smalldatetime not null default getutcdate()
);
My goal is to populate a pivot table for reporting purposes that looks something like this:
create table activity_summary (
sessionid uniqueidentifier not null,
first_activity_desc varchar(100) not null,
last_activity_desc varchar(100) not null
);
The first and last activity descriptions will be listed in chronological order. My initial thought is to update the pivot table like this:
truncate table activity_summary;
insert into activity_summary (sessionid)
select sessionid from activity;
update table activity_summary set
first_activity_desc = (select top 1 activity_desc from activity_detail where sessionid = as.sessionid order by created asc),
last_activity_summary = (select top 1 activity_desc from activity_detail where sessionid = as.sessionid order by created desc)
from activity_summary as;
However, this seems incredibly verbose and unnecessary to me. I just don't know how to reduce it. My gut is that I can do this somehow everything in the insert statement, but I'm stumped. Any suggestions?
source to share
There are probably more efficient ways to do this, but this is the closest thing to your original:
truncate table activity_summary;
insert into activity_summary (sessionid, first_activity_desc, last_activity_summary)
select a.sessionid
,(select top 1 ad.activity_desc from activity_detail AS ad where ad.sessionid = a.sessionid order by ad.created asc) AS first_activity_desc
,(select top 1 ad.activity_desc from activity_detail AS ad where ad.sessionid = a.sessionid order by ad.created desc) AS last_activity_summary
from activity AS a;
Something like this might be more efficient:
truncate table activity_summary;
WITH firsts AS (
SELECT ad.sessionid
,ad.activity_desc
,ROW_NUMBER() OVER (ORDER BY ad.created ASC) as RowNumber
FROM activity_detail AS ad
)
,lasts AS (
SELECT ad.sessionid
,ad.activity_desc
,ROW_NUMBER() OVER (ORDER BY ad.created DESC) as RowNumber
FROM activity_detail AS ad
)
insert into activity_summary (sessionid, first_activity_desc, last_activity_summary)
select a.sessionid
,firsts.activity_desc
,lasts.activity_desc
from activity AS a
INNER JOIN firsts ON firsts.sessionid = a.sessionid AND firsts.RowNumber = 1
INNER JOIN lasts ON lasts.sessionid = a.sessionid AND lasts.RowNumber = 1
source to share
insert into activity_summary
(sessionid, first_activity_desc, last_activity_desc)
select
agg.sessionid,
adf.activity_description,
adl.activity_description
from
(SELECT
sessionid, MIN(created) as firstcreated, MAX(created) as lastcreated
from
activity_detail group by sessionid
) agg
JOIN
activity_details adf ON agg.sessionid = adf.sessionid AND agg.firstcreated = adf.created
JOIN
activity_details adl ON agg.sessionid = adl.sessionid AND agg.lastcreated = adl.created
source to share
Roughly speaking,
INSERT, etc.
SELECT a.sessionid, d1.activity_description, d2.activity_description
FROM activity a
JOIN detail d1 ON a.sessionid = d1.sessionid JOIN detail d2 ON a.sessionid = d2.sessionid
WHERE DOES NOT EXIST
(SELECT 1 FROM detail WHERE sessionid = a.sessionid AND created <d1.created)
AND DOES NOT EXIST
(SELECT 1 FROM detail WHERE sessionid = a.sessionid AND created> d2.created)
source to share
Or
INSERT, etc.
(SELECT description FROM detail d1 WHERE d1.sessionid = a.sessionid
AND DOES NOT EXIST (SELECT 1 FROM detail WHERE created <d1.created)) AS desc1,
(SELECT description FROM detail d2 WHERE d2.sessionid = a.sessionid
AND DOES NOT EXIST (SELECT 1 FROM detail d2 WHERE created> d1.created)) AS desc2
FROM activity a
(I prefer this myself).
source to share