SQL : loop with condition

Question

I have to create a report that sums up days someone one has a case assigned to them, the only issue is that there are multiple rows. I need to calculate the difference when it gets assigned to someone else. there will be numerous cases and i wont know exactly how many times each case has been shifted to a different person.

example of the data

CaseId,CaseCounter,OldValue,NewValue,ChangeDate
399547,1, Brad, Eric, 9/18/2016
399547,2, Meghan, Joni, 9/19/2016
399547,3, Eric, Pam, 9/20/2016
399547,4, Pam, Meghan, 9/22/2016
399547,5, Joni, Eric, 9/25/2016
399547,6, Andrea, Team, 9/30/2016
399547,7, Team, Admin, 10/3/2016
399547,8, Admin, Brad, 10/19/2016
399547,9, Eric, Joni, 10/21/2016 - since this is the last record it should compare this to current date
460771,1, Meghan, Cheryl, 10/1/2016
460771,2, Admin, Meghan, 10/6/2016
460771,3, Admin, Cheryl, 10/6/2016
460771,4, Cheryl, Meghan ,10/9/2016
460771,5, Meghan, Admin, 10/11/2016

CREATE TABLE #Records (CaseID INT, caseCounter INT, oldValue VARCHAR(50),NewValue VARCHAR(50), changeDate DATETIME)
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,1,'Brad','Eric','19/18/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,2,'Meghan','Joni','9/19/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,3,'Eric','Pam','9/20/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,4,'Pam','Meghan','9/22/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,5,'Joni','Eric','9/25/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,6,'Andrea','Team','9/30/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,7,'Team','Admin','10/3/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,8,'Admin','Brad','10/19/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (399547,9,'Eric','Joni','10/21/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (460771,1,'Meghan','Cheryl','10/1/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (460771,2,'Admin','Meghan','10/6/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (460771,3,'Admin','Cheryl','10/6/2016')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (460771,4,'Cheryl','Meghan','42652')
INSERT INTO #Records  ( CaseID ,caseCounter ,oldValue ,NewValue , changeDate)VALUES  (460771,5,'Meghan','Admin','10/11/2016')

the expected outcome is

CaseID UseID DayCount
399547 Eric  6
399547 Pam   2
399547 Joni  13 (1 + diff (10/21-today) =12)

TJB · Accepted Answer

Here you go. The code first creates a temporary view of the #Records table via use of a common table expression (CTE) to allow the table to be compared to itself. The CTE is joined to #Records on the CaseID and on the caseCounter, less 1 (i.e. caseCounter 2 from the CTE is joined to caseCounter 1 of the #Records table), thus allowing the next date to be populated on the older record. Then a simple datediff is performed to get the days it was held, and then a summary is calculated.

IF object_id('tempdb..#Records') is not null 
drop table #records

CREATE TABLE #Records (CaseID INT, caseCounter INT, oldValue VARCHAR(50),NewValue VARCHAR(50), changeDate DATE)
INSERT INTO #Records 
VALUES 
(399547,1,'Brad','Eric','9/18/2016'),
(399547,2,'Meghan','Joni','9/19/2016'),
(399547,3,'Eric','Pam','9/20/2016'),
(399547,4,'Pam','Meghan','9/22/2016'),
(399547,5,'Joni','Eric','9/25/2016'),
(399547,6,'Andrea','Team','9/30/2016'),
(399547,7,'Team','Admin','10/3/2016'),
(399547,8,'Admin','Brad','10/19/2016'),
(399547,9,'Eric','Joni','10/21/2016'),
(460771,1,'Meghan','Cheryl','10/1/2016'),
(460771,2,'Admin','Meghan','10/6/2016'),
(460771,3,'Admin','Cheryl','10/6/2016'),
(460771,4,'Cheryl','Meghan','10/9/2016'),
(460771,5,'Meghan','Admin','10/11/2016');


IF object_id('tempdb..#temp') is not null 
drop table #temp; -- drops temp table if already exists, improves ability to re-run code.

-- CTE created, whic his just a copy of the table to compare against itself

WITH CTE
AS (
SELECT 
CaseID
,caseCounter 
,oldValue 
,NewValue
,changeDate AS NewDate
FROM #Records
)

-- CTE Is left joined because eventually you run out of case counters to join on
SELECT 
R.CaseID
,R.caseCounter 
,R.oldValue  
,R.NewValue AS Name
,changeDate 
,CASE WHEN newDate IS NULL THEN CAST(GETDATE() AS DATE) ELSE NewDate END AS HeldUntil  -- case statement puts today in where you would have a null new date from the left join
,DATEDIFF(DD,changeDate,CASE WHEN newDate IS NULL THEN CAST(GETDATE() AS DATE) ELSE NewDate END) AS DaysHeld -- date diff between change (i.e. start date) and the changeover date
INTO #temp -- place this into a temp table
FROM #Records AS R
LEFT JOIN cte    AS C
    ON r.CaseID = C.CaseID
        AND r.caseCounter = C.caseCounter - 1;



-- simple summary result set from the above
SELECT
CaseID
,Name
,SUM(DaysHeld) AS DaysHeld
FROM #temp
GROUP BY CaseID
,Name
ORDER BY CaseID, Name ASC;

SQL : loop with condition

Answers (2)

Related Questions