October 24, 2014 at 7:16 am
Hello, the problem is I want to end up with the max/min of agent working on an account. To that end I looked at DenseRank, however it does not give me what I want. My CIO gave me the code and said fix it. Since he used a while loop I thought I would try something else.
IF OBJECT_ID (N'tempdb..#tmp_extract', N'U') IS NOT NULL DROP TABLE #tmp_extract;
CREATE TABLE #tmp_extract(
UserId VARCHAR(50),
Workstation VARCHAR(100),
TableName VARCHAR(50),
App VARCHAR(10),
RefNum VARCHAR(10),
CreateDate DATETIME
);
INSERT INTO #tmp_extract (UserId,Workstation,TableName,App,RefNum,CreateDate) VALUES
('ach','20248','btnGet_Click','P1K','105447','10-20-2014 15:30:28'),
('ach','na', 'na', 'P1K','105447','10-20-2014 15:30:29'),
('ach','20248','btnGet_Click','P1K','105477','10-20-2014 15:30:41'),
('ach','na', 'na', 'P1K','105477','10-20-2014 15:30:42'),
('ach','20248','HouseKeeping','P1K','229110','10-21-2014 10:58:02'),
('ach','na', 'na', 'P1K','229110','10-21-2014 10:58:02'),
('ach','20248','LaunchDoc', 'P1K','229110','10-21-2014 10:59:27'),
('alf','20512','HouseKeeping','P1K','236994','10-20-2014 09:04:13'),
('alf','20512','btnGet_Click','P1K','161400','10-20-2014 09:04:18'),
('alf','20512','HouseKeeping','P1K','236994','10-20-2014 09:07:34'),
('alf','20512','HouseKeeping','P1K','236994','10-20-2014 09:09:36'),
('alf','20512','btnGet_Click','P1K','161400','10-20-2014 09:09:39'),
('alf','20512','HouseKeeping','P1K','236994','10-20-2014 09:12:38'),
('alf','na', 'na', 'P1K','236994','10-20-2014 09:12:38'),
('alf','na', 'na', 'P1K','236994','10-20-2014 09:13:47'),
('ALF','20512','CallHistory', 'P1K','236994','10-20-2014 09:13:48');
IF OBJECT_ID (N'tempdb..#tmp_details', N'U') IS NOT NULL DROP TABLE #tmp_details;
CREATE TABLE #tmp_details(
idx BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
UserId VARCHAR(50),
Workstation VARCHAR(100),
TableName VARCHAR(50),
App VARCHAR(10),
RefNum VARCHAR(10),
ProcessDate DATE,
ProcessTime DATETIME,
Tick BIGINT,
RankTick BIGINT
);
INSERT #tmp_details
(UserId,Workstation,TableName,App,RefNum,ProcessDate,ProcessTime,Tick,RankTick)
SELECT
UserId
, Workstation
, TableName
, App
, RefNum
, CreateDate AS ProcessDate
, CreateDate AS ProcessTime
, 0 AS Tick
, dense_rank() OVER (ORDER BY UserId,RefNum,CreateDate) AS RankTick
FROM #tmp_extract
ORDER BY App,UserId,ProcessDate,Workstation,TableName;
UPDATE #tmp_details SET Tick=1 WHERE idx=1;
DECLARE @maxIDX BIGINT, @updtIDX BIGINT = 2;
SET @MaxIDX = (SELECT MAX(idx) FROM #tmp_details);
WHILE @updtIDX <= @MaxIDX
BEGIN
-----------------------------------------------------------------------------------------
update d1
set d1.Tick =
case when (d2.UserId=d1.UserId and d2.RefNum=d1.RefNum)
then d2.Tick else d1.idx end
from #tmp_details d1
join #tmp_details d2 on d2.idx = (d1.idx-1)
where d1.idx = @updtIDX;
-----------------------------------------------------------------------------------------
SET @updtIDX += 1;
END
SELECT * FROM #tmp_details;
/*
idx UserId Workstation TableName App RefNum ProcessDate ProcessTime Tick RankTick
8 alf 20512 HouseKeeping P1K 236994 2014-10-20 2014-10-20 09:04:13.000 8 5
9 alf 20512 btnGet_Click P1K 161400 2014-10-20 2014-10-20 09:04:18.000 9 4
10 alf 20512 HouseKeeping P1K 236994 2014-10-20 2014-10-20 09:07:34.000 10 5
13 alf 20512 HouseKeeping P1K 236994 2014-10-20 2014-10-20 09:12:38.000 13 5
14 alf na na P1K 236994 2014-10-20 2014-10-20 09:12:38.000 13 5
15 alf na na P1K 236994 2014-10-20 2014-10-20 09:13:47.000 13 5
16 ALF 20512 CallHistory P1K 236994 2014-10-20 2014-10-20 09:13:48.000 13 5
*/
Notice that IDX 8 and 10 have the same RankTick but have different Tick values. This is due to the RefNum/ProcessTime, we want it split because there is a different RefNum between.
So is there a way to get a the rank to work like this? Or is there a way to remove the while loop?
I hope this makes sense....
Thank you
October 24, 2014 at 7:32 am
Using your data, I get different results:
idxUserIdWorkstationTableNameAppRefNum ProcessDate ProcessTime Tick RankTick
8alf20512HouseKeepingP1K2369942014-10-202014-10-20 09:04:13.000 8 9
9alf20512btnGet_ClickP1K1614002014-10-202014-10-20 09:04:18.000 9 7
Gerald Britton, Pluralsight courses
October 24, 2014 at 7:39 am
Sorry, I was trying different things. Should cast the create date as date
, dense_rank() OVER (ORDER BY UserId,RefNum,CAST(CreateDate AS DATE)) AS RankTick
-- , dense_rank() OVER (ORDER BY UserId,RefNum,CreateDate ) AS RankTick
October 24, 2014 at 7:52 am
This looks like a gaps and islands problem. Have you checked out:
https://www.simple-talk.com/sql/t-sql-programming/the-sql-of-gaps-and-islands-in-sequences/
Gerald Britton, Pluralsight courses
October 24, 2014 at 11:04 am
I think I eliminated the loop. Have a look:
--WHILE @updtIDX <= 3
--BEGIN
-----------------------------------------------------------------------------------------
update d1
set d1.Tick =
case when (d2.UserId=d1.UserId and d2.RefNum=d1.RefNum and d2.tick > 0)
then d2.Tick else d1.idx end
from #tmp_details d1
join #tmp_details d2 on d2.idx = (d1.idx-1)
--where d1.idx = @updtIDX;
-----------------------------------------------------------------------------------------
-- SET @updtIDX += 1;
--END
Gerald Britton, Pluralsight courses
October 24, 2014 at 11:07 am
Thank you g.britton, I got pulled off to another task and am just getting back to this, so I will look at the two posts and see what happens.
October 24, 2014 at 12:46 pm
Got an initial working resolution. Some guy by the name of Jeff 😀 has an article: Solving the Running Total and Ordinal Rank Problems (Rewritten)http://www.sqlservercentral.com/articles/T-SQL/68467/
Thanks Jeff!
I used the "Quirky Update" and got:
DECLARE @PrevAccountID VARCHAR(10)
DECLARE @AccountRunningTotal BIGINT = 0
UPDATE #tmp_details
SET @AccountRunningTotal = RankTick = CASE
WHEN RefNum = @PrevAccountID THEN @AccountRunningTotal
ELSE @AccountRunningTotal + 1
END,
@PrevAccountID = RefNum
FROM #tmp_details WITH (TABLOCKX)
OPTION (MAXDOP 1)which gave me what I was looking for using the test data. Now to try it on the actual data.
Thanks g.britton for your help. You got me thinking about other similar code (i.e. running total).
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply