February 24, 2017 at 6:41 am
Hi,
I have a table that is holding data like the following...
when agent Agent Name category
2017-02-20 09:07:26.953 79433 G Burns Wait
2017-02-20 09:08:00.217 79433 G Burns Logout
2017-02-20 09:08:17.583 79433 G Burns Call
2017-02-20 09:08:20.990 79433 G Burns Wrap
2017-02-20 09:08:29.533 79433 G Burns Logout
I need to gather if the agent has gone from a status wait to logout to show as failed in a New column (Status) stating failed and if they have gone from wrap to logout show in that new column as success, which the final result will look something like this
when agent Agent Name actions Status
2017-02-20 09:07:26.953 79433 G Burns Wait (09:07:26) ; Logout(09:08:00) Failed
2017-02-20 09:08:20.990 79433 G Burns Wrap(09:08:20); Logout( 09:08:29) Success
This is the schema for the table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[StatusLog](
[id] [INT] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[uid] [BIGINT] NOT NULL,
[when] [DATETIME] NOT NULL,
[agent] [INT] NULL,
[category] [VARCHAR](50) NULL
CONSTRAINT [PK_StatusLog] PRIMARY KEY NONCLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[StatusLog] ADD CONSTRAINT [DF_StatusLog_created] DEFAULT (GETDATE()) FOR [created]
GO
help is appriecated
February 24, 2017 at 6:47 am
I think you're looking for the LAG and LEAD functions. Read about and experiment with them, and please post back if there's anything you don't understand.
John
February 24, 2017 at 8:19 am
I have taken a look at the Lead and Lag function they look useful but I'm having a issue to try an implement this is my situation
The problem is that you have to specify the row to look at, with my situation the values will vary in where they come in the table , they could be the 1st row 100th row. It will always be the row before the row that 'Logout' is mentioned in the category column.
So i am a bit stuck on how i would go about writing it.
February 24, 2017 at 8:26 am
does this help you in getting closer to your requirements?
CREATE TABLE #yourtable(
thedate DATETIME NOT NULL
,agent INT NOT NULL
,AgentName VARCHAR(11) NOT NULL
,category VARCHAR(7) NOT NULL
);
INSERT INTO #yourtable(thedate,agent,AgentName,category) VALUES
('2017-02-20 09:07:26.953',79433,'G Burns','Wait')
,('2017-02-20 09:08:00.217',79433,'G Burns','Logout')
,('2017-02-20 09:08:17.583',79433,'G Burns','Call')
,('2017-02-20 09:08:20.990',79433,'G Burns','Wrap')
,('2017-02-20 09:08:29.533',79433,'G Burns','Logout');
SELECT thedate,
AgentName,
category,
CASE
WHEN category = 'WAIT'
AND LEAD(category, 1) OVER(PARTITION BY agent ORDER BY thedate) = 'LOGOUT'
THEN 'FAILED'
WHEN category = 'WRAP'
AND LEAD(category, 1) OVER(PARTITION BY agent ORDER BY thedate) = 'LOGOUT'
THEN 'SUCCESS'
ELSE ''
END
FROM #yourtable;
DROP TABLE #yourtable;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 24, 2017 at 8:33 am
If I understand your requirement, you want all rows that have a Wait or Wrap Category that are followed by a row with a Logout category. Is that right? Does the Logout have to come in the next row, or anywhere after the Wait or Wrap?
Start with a CTE that gets the next row for all rows, partitoned by Agent and ordered by When. Then just select from that CTE where Category is Wait or Wrap, and NextRowCategory is Logout.
John
February 24, 2017 at 8:40 am
J Livingston SQL - Friday, February 24, 2017 8:26 AMdoes this help you in getting closer to your requirements?
CREATE TABLE #yourtable(
thedate DATETIME NOT NULL
,agent INT NOT NULL
,AgentName VARCHAR(11) NOT NULL
,category VARCHAR(7) NOT NULL
);
INSERT INTO #yourtable(thedate,agent,AgentName,category) VALUES
('2017-02-20 09:07:26.953',79433,'G Burns','Wait')
,('2017-02-20 09:08:00.217',79433,'G Burns','Logout')
,('2017-02-20 09:08:17.583',79433,'G Burns','Call')
,('2017-02-20 09:08:20.990',79433,'G Burns','Wrap')
,('2017-02-20 09:08:29.533',79433,'G Burns','Logout');SELECT thedate,
AgentName,
category,
CASE
WHEN category = 'WAIT'
AND LEAD(category, 1) OVER(PARTITION BY agent ORDER BY thedate) = 'LOGOUT'
THEN 'FAILED'
WHEN category = 'WRAP'
AND LEAD(category, 1) OVER(PARTITION BY agent ORDER BY thedate) = 'LOGOUT'
THEN 'SUCCESS'
ELSE ''
END
FROM #yourtable;DROP TABLE #yourtable;
It does thank you, now how would i go about getting rid of those blank records that are appearing in the that new column that the case is doing ?
February 24, 2017 at 8:43 am
Suth - Friday, February 24, 2017 8:40 AMJ Livingston SQL - Friday, February 24, 2017 8:26 AMdoes this help you in getting closer to your requirements?
CREATE TABLE #yourtable(
thedate DATETIME NOT NULL
,agent INT NOT NULL
,AgentName VARCHAR(11) NOT NULL
,category VARCHAR(7) NOT NULL
);
INSERT INTO #yourtable(thedate,agent,AgentName,category) VALUES
('2017-02-20 09:07:26.953',79433,'G Burns','Wait')
,('2017-02-20 09:08:00.217',79433,'G Burns','Logout')
,('2017-02-20 09:08:17.583',79433,'G Burns','Call')
,('2017-02-20 09:08:20.990',79433,'G Burns','Wrap')
,('2017-02-20 09:08:29.533',79433,'G Burns','Logout');SELECT thedate,
AgentName,
category,
CASE
WHEN category = 'WAIT'
AND LEAD(category, 1) OVER(PARTITION BY agent ORDER BY thedate) = 'LOGOUT'
THEN 'FAILED'
WHEN category = 'WRAP'
AND LEAD(category, 1) OVER(PARTITION BY agent ORDER BY thedate) = 'LOGOUT'
THEN 'SUCCESS'
ELSE ''
END
FROM #yourtable;DROP TABLE #yourtable;
It does thank you, now how would i go about getting rid of those blank records that are appearing in the that new column that the case is doing ?
do what John suggest above "Start with a CTE that gets the next row for all rows, partitoned by Agent and ordered by When. Then just select from that CTE where Category is Wait or Wrap, and NextRowCategory is Logout."
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 24, 2017 at 8:44 am
John Mitchell-245523 - Friday, February 24, 2017 8:33 AMIf I understand your requirement, you want all rows that have a Wait or Wrap Category that are followed by a row with a Logout category. Is that right? Does the Logout have to come in the next row, or anywhere after the Wait or Wrap?Start with a CTE that gets the next row for all rows, partitoned by Agent and ordered by When. Then just select from that CTE where Category is Wait or Wrap, and NextRowCategory is Logout.
John
correct, ideally all i want is the category value which is before the Logout status. as the status could vary.
February 24, 2017 at 8:50 am
Suth - Friday, February 24, 2017 8:44 AMJohn Mitchell-245523 - Friday, February 24, 2017 8:33 AMIf I understand your requirement, you want all rows that have a Wait or Wrap Category that are followed by a row with a Logout category. Is that right? Does the Logout have to come in the next row, or anywhere after the Wait or Wrap?Start with a CTE that gets the next row for all rows, partitoned by Agent and ordered by When. Then just select from that CTE where Category is Wait or Wrap, and NextRowCategory is Logout.
John
correct, ideally all i want is the category value which is before the Logout status. as the status could vary.
"before the Logout status" ...in which case use LAG
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 24, 2017 at 11:38 am
Here's the LAG version:
CREATE TABLE #yourtable (
thedate datetime NOT NULL,
agent int NOT NULL,
AgentName varchar(11) NOT NULL,
category varchar(7) NOT NULL
);
INSERT INTO #yourtable (thedate, agent, AgentName, category)
VALUES ('2017-02-20 09:07:26.953',79433,'G Burns','Wait'),
('2017-02-20 09:08:00.217',79433,'G Burns','Logout'),
('2017-02-20 09:08:17.583',79433,'G Burns','Call'),
('2017-02-20 09:08:20.990',79433,'G Burns','Wrap'),
('2017-02-20 09:08:29.533',79433,'G Burns','Logout');
WITH ALL_DATA AS (
SELECT T.thedate,
T.AgentName,
T.category,
LAG(T.category, 1) OVER(PARTITION BY T.agent ORDER BY T.thedate) AS previousCategory
FROM #yourtable AS T
)
SELECT D.*,
CASE
WHEN D.category = 'Logout' AND D.previousCategory = 'Wait' THEN 'FAILED'
WHEN D.category = 'Logout' AND D.previousCategory = 'Wrap' THEN 'SUCCESS'
ELSE NULL
END AS [Status]
FROM ALL_DATA AS D
WHERE CASE
WHEN D.category = 'Logout' AND D.previousCategory = 'Wait' THEN 'FAILED'
WHEN D.category = 'Logout' AND D.previousCategory = 'Wrap' THEN 'SUCCESS'
ELSE NULL
END IS NOT NULL;
DROP TABLE #yourtable;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply