derek kenney derek kenney - 5 months ago 19
SQL Question

How to conditionally select records from SQL Server

Friends,

I have a SQL problem I could use help on. I'm working with SQL Server 2008.

The use case is the following. We have a system where users watch videos, and each time a user watches a video, we record that activity. We capture three properties each time; assetid (an asset is a video), customerid, and status.

A record can have three different statuses; 'completion', 'playing', and 'start'.

The person who wrote this part of the system is not a developer, and instead of of updating the status of an existing record, inserts a new duplicate record each time a user watches a video. Therefore we have thousands of duplicate records. Here is a sample dataset

enter image description here

The problem I need to solve is to select a record by assetid, customerid, and status. I need to choose a record that has a status of 'completion' if it exists.

If a record has a status of 'playing', but no record with the same assetid and customerid with a status of 'completion' exists, then choose that record.

If a record has a status of 'start', but no record with the same assetid and customerid with a status of either 'completion' or 'playing' exists, then choose that record.

Here is sample code where I tried to use a CASE statement to solve the problem. I also tried another case statement with a NOT IN subquery, but without success.

INSERT INTO #ViewTime (AssetID, CustomerID, ViewTime)
SELECT
tt.customerid, tt.assetId, tt.assetstatus,
CASE WHEN
tt.AssetStatus = 'COMPLETION'
AND
ISNUMERIC(timeposition) = 1
THEN
CONVERT(Numeric(18,3), timePosition)
WHEN
tt.AssetStatus = 'PLAYING'
AND
ISNUMERIC(timeposition) = 1
THEN
CONVERT(Numeric(18,3), timePosition)
WHEN
tt.AssetStatus = 'START'
AND
ISNUMERIC(timeposition) = 1
THEN
CONVERT(Numeric(18,3), timePosition)
ELSE null
END AS ViewTime
FROM
TableAssetTracking tt
inner join TableAssets ta
on tt.AssetID = ta.AssetID
WHERE
tt.timePosition is not null
AND
AssetBuffering is null


Any suggestions would be greatly appreciated. Thanks, Derek

Answer

The problem I need to solve is to select a record by assetid, customerid, and status.

I need to choose a record that has a status of 'completion' if it exists.

select distinct assetID, CustomerID  
  from table 
 where status = 'complete' 

If a record has a status of 'playing', but no record with the same assetid and customerid with a status of 'completion' exists, then choose that record.

select assetID, CustomerID  
  from table 
 where status = 'playing'
except
select assetID, CustomerID  
  from table 
 where status = 'complete'

If a record has a status of 'start', but no record with the same assetid and customerid with a status of either 'completion' or 'playing' exists, then choose that record.

select assetID, CustomerID  
  from table 
 where status = 'start'
except
select assetID, CustomerID  
  from table 
 where status in ('complete', 'playing') 

The above is not going to give you ViewTime that I see in the example
It was not in the requirements statement

select * 
from 
(
select assetID, CustomerID, status, ViewTime 
     , row_number() over (partition by assetID, CustomerID order by status, ViewTime desc) as rn
  from table 
  where status in ('complete', 'playing', 'start') 
) tt 
where tt.rn = 1