Min and Max Change without Lead and Lag

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Min and Max Change without Lead and Lag

Author

Topic

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-02 : 18:03:50

I'm finding difficulty to get the MAX and MIN change without using LEAD and LAG. The result should come out with the record anytime the CallStatus change from "Unavailable" to any other status with the Previous or Next CallAgent name.Sometimes the status can start with "Unavailable" in that case the last change needs to recorded sometimes it can change multiple times then the last time it changed should be recorded.In some cases the last few status could "Unavailable" then the Min record needs to be recorded to identify the change.

Create table CustomerCallTrack

(

CustomerID varchar(20),
CallDate Date,
CallStatus Varchar(20),
CallAgent Varchar(20)
)

INSERT INTO CustomerCallTrack

select 'C175690','2014-06-19','Listed','Raj1' UNION

select 'C175690','2014-06-20','Listed', 'Raj2' UNION

select 'C175690','2014-06-21','Listed', 'Raj3' UNION

select 'C175690','2014-06-22','Listed', 'Raj4' UNION

select 'C175690','2014-06-23','Unavailable', 'Raj5' UNION

select 'C175690','2014-06-24', 'Unavailable', 'Raj6' UNION

select 'C175690','2014-06-25', 'Unavailable', 'Raj7' UNION

select 'C175690','2014-06-26','Listed', 'Raj8' UNION

select 'C175690','2014-06-27','Listed', 'Raj9' UNION

select 'C175690','2014-06-28','Contacted', 'Raj10' UNION

select 'C175692','2014-06-19','Listed','Saj1' UNION

select 'C175692','2014-06-20','Listed', 'Saj2' UNION

select 'C175692','2014-06-21','Listed', 'Saj3' UNION

select 'C175692','2014-06-22','Listed', 'Saj4' UNION

select 'C175692','2014-06-23','Unavailable', 'Saj5' UNION

select 'C175692','2014-06-24', 'Unavailable', 'Saj6' UNION

select 'C175692','2014-06-25', 'Unavailable', 'Saj7' UNION

select 'C175692','2014-06-26', 'Unavailable', 'Saj8' UNION

select 'C175692','2014-06-27', 'Unavailable', 'Saj9' UNION

select 'C175692','2014-06-28', 'Unavailable', 'Saj10' UNION

select 'C175699','2014-07-19', 'Unavailable','Taj1' UNION

select 'C175699','2014-07-20', 'Unavailable', 'Taj2' UNION

select 'C175699','2014-07-21','Listed', 'Taj3' UNION

select 'C175699','2014-07-22','Listed', 'Taj4' UNION

select 'C175699','2014-07-23','Unavailable', 'Taj5' UNION

select 'C175699','2014-07-24', 'Contacted', 'Taj6' UNION

select 'C175699','2014-07-25', 'Contacted', 'Taj7' UNION

select 'C175699','2014-07-26', 'Unavailable', 'Taj8' UNION

select 'C175699','2014-07-27', 'Unavailable', 'Taj9' UNION

select 'C175699','2014-07-28', 'Unavailable', 'Taj10'

-----------------------------------------------
I tried the below query :

WITH s AS
(
select
customerid, CallDate, CallStatus, CallAgent, ROW_NUMBER () OVER (PARTITION BY CustomerId order by CallDate ) as row_num
from CustomerCallTrack
) , d AS
(

select s.CustomerId, s.CallDate, s.CallStatus, s.CallAgent,
sld.CallAgent as NextCallAgent,
sld.CallStatus AS NextCallStatus,
slg.CallAgent as PreviousCallAgent,
slg.CallStatus AS PreviousCallStatus
from s
left outer join s as sld on s.CustomerId =sld.CustomerId and s.row_num + 1 = sld.row_num
left outer join s as slg on s.CustomerId =slg.CustomerId and s.row_num -1 = slg.row_num
INNER JOIN
(
SELECT CustomerId, MIN(CallDate) as MinCallDate, MAX(CallDate)as MxCallDate
FROM s
WHERE CallStatus = 'Unavailable'
GROUP BY CustomerId
) x ON s.CustomerId =x.CustomerId and s.CallDate IN (MinCallDate,MxCallDate)
) select * from d

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-02 : 18:26:58

Adding few more records to cover few additional cases :

INSERT INTO CustomerCallTrack

select 'C175660','2014-06-19','Unavailable','Raj1' UNION

select 'C175660','2014-06-20','Unavailable', 'Raj2' UNION

select 'C175660','2014-06-21','Unavailable', 'Raj3' UNION

select 'C175660','2014-06-22','Unavailable', 'Raj4' UNION

select 'C175660','2014-06-23','Unavailable', 'Raj5' UNION

select 'C175660','2014-06-24', 'Unavailable', 'Raj6' UNION

select 'C175660','2014-06-25', 'Unavailable', 'Raj7' UNION

select 'C175660','2014-06-26','Unavailable', 'Raj8' UNION

select 'C175660','2014-06-27','Unavailable', 'Raj9' UNION

select 'C175660','2014-06-28','Unavailable', 'Raj10' UNION

select 'C175645','2014-06-19','Unavailable','Saj1' UNION

select 'C175645','2014-06-20','Unavailable', 'Saj2' UNION

select 'C175645','2014-06-21','Unavailable', 'Saj3' UNION

select 'C175645','2014-06-22','Unavailable', 'Saj4' UNION

select 'C175645','2014-06-23','Unavailable', 'Saj5' UNION

select 'C175645','2014-06-24', 'Unavailable', 'Saj6' UNION

select 'C175645','2014-06-25', 'Unavailable', 'Saj7' UNION

select 'C175645','2014-06-26', 'Contacted', 'Saj8' UNION

select 'C175645','2014-06-27', 'Contacted', 'Saj9' UNION

select 'C175645','2014-06-28', 'Contacted', 'Saj10' UNION

select 'C175672','2014-07-19', 'Contacted','Taj1' UNION

select 'C175672','2014-07-20', 'Contacted', 'Taj2' UNION

select 'C175672','2014-07-21','Contacted', 'Taj3' UNION

select 'C175672','2014-07-22','Contacted', 'Taj4' UNION

select 'C175672','2014-07-23','Unavailable', 'Taj5' UNION

select 'C175672','2014-07-24', 'Unavailable', 'Taj6' UNION

select 'C175672','2014-07-25', 'Contacted', 'Taj7' UNION

select 'C175672','2014-07-26', 'Contacted', 'Taj8' UNION

select 'C175672','2014-07-27', 'Unavailable', 'Taj9' UNION

select 'C175672','2014-07-28', 'Unavailable', 'Taj10'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-02 : 18:50:01

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-02 : 20:13:02

The name of the Previous CallAgent when the last time the CallStatus Changed from 'Unavailable' to another status or the last time any other status changed to 'Unavailable' .

Trying the below query got me the Min and Max the last time the CallStatus changed from 'Unavailable' OR to 'Unavailable' …The result should be

-- For C175645 It should be Saj7 as the last status change happend for Unavailable
- For C175660 - 0 Records as no change
- For C175672 - Taj8
- For C175690 - Raj7
- For C175692 - Saj4
- For C175699 - Taj7

WITH s AS
(
select
customerid, CallDate, CallStatus, CallAgent, ROW_NUMBER () OVER (PARTITION BY CustomerId order by CallDate ) as row_num
from CustomerCallTrack
) , d AS
(

select s.CustomerId, s.CallDate, s.CallStatus, s.CallAgent,
sld.CallAgent as NextCallAgent,
sld.CallStatus AS NextCallStatus,
slg.CallAgent as PreviousCallAgent,
slg.CallStatus AS PreviousCallStatus
from s
left outer join s as sld on s.CustomerId =sld.CustomerId and s.row_num + 1 = sld.row_num
left outer join s as slg on s.CustomerId =slg.CustomerId and s.row_num -1 = slg.row_num
INNER JOIN
(
SELECT CustomerId, MIN(CallDate) as MinCallDate, MAX(CallDate)as MxCallDate
FROM s
WHERE CallStatus = 'Unavailable'
GROUP BY CustomerId
) x ON s.CustomerId =x.CustomerId and s.CallDate IN (MinCallDate,MxCallDate)
) select * from d

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-02 : 20:33:21

Hope this helps…The Agent who made the last change to 'Unavailable' or the last agent who changed from 'unavailable' to another status. If both condition is available then the latest record needs to be selected.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-11-02 : 20:52:55

[code]; with cte as
(
select *,
status = case when CallStatus = 'Unavailable' then 0 else 1 end,
rn = row_number() over (partition by CustomerID order by CallDate desc)
from CustomerCallTrack
)
select CustomerID, p.CallAgent
from cte c
outer apply
(
select top 1 CallAgent
from cte x
where x.CustomerID = c.CustomerID
and x.status <> c.status
order by CallDate desc
) p
where rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-03 : 00:07:54

Thanks a lot KH for the help.The result for the query looks good I'm validating other cases.Can I get the CallDate along with CustomerID and CallAgent as part of the result.Thanks a lot

sql_chaser
Starting Member

33 Posts

Posted - 2014-11-03 : 00:23:13

I just added the date and looks good.I'm validating all the cases.

with cte as
(
select *,
status = case when CallStatus = 'Unavailable' then 0 else 1 end,
rn = row_number() over (partition by CustomerID order by CallDate desc)
from CustomerCallTrack
)
select CustomerID, p.CallAgent,p. CallDate
from cte c
outer apply
(
select top 1 CallAgent, CallDate
from cte x
where x.CustomerID = c.CustomerID
and x.status <> c.status
order by CallDate desc
) p
where rn = 1

-- Can I do the something using the Lead and Lag (Without using function)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources