October 24, 2016 at 11:10 pm
Hello from Melbourne, Australia
I need help please with a query to identify customers who have changed levels of service.
I have a table that lists the customer, the level of service and the date of that change of service level. I would like to identify the date and level when there is a change of level.
Levels do not change that often if at all, they can go up or down and can repeat. I am using SQL Server 2008.
From the following example I would expect records 5, 8, 9, 12 and 15 in my results.
IDClient Level Change Date
11000 2 01/07/2016
21000 2 03/08/2016
31000 2 10/09/2016
42000 2 01/05/2016
52000 3 01/08/2016
62000 3 01/09/2016
73000 2 01/07/2016
83000 3 15/08/2016
93000 4 31/08/2016
101000 2 15/09/2016
112000 3 01/10/2016
123000 3 10/09/2016
134000 2 01/07/2016
145000 4 01/07/2016
155000 3 01/09/2016
Any help is greatly appreciated.
Thank you
Michael
PS, why can't I insert a table in to my post, but I can a friggin' emoji?
October 25, 2016 at 12:09 am
Since you posted in a 2008 forum, here is a simple self-join using a cte that gives you your output. If you had access to 2012 or greater you could do this with lead.
declare @t table
(
ID int,
Client int,
Level int,
ChangeDate date
);
Insert @t (ID, Client, Level, ChangeDate) Values
(1,1000, 2, '01/07/2016'),
(2,1000, 2, '03/08/2016'),
(3,1000, 2, '10/09/2016'),
(4,2000, 2, '01/05/2016'),
(5,2000, 3, '01/08/2016'),
(6,2000, 3, '01/09/2016'),
(7,3000, 2, '01/07/2016'),
(8,3000, 3, '08/15/2016'),
(9,3000, 4, '08/31/2016'),
(10,1000, 2, '09/15/2016'),
(11,2000, 3, '01/10/2016'),
(12,3000, 3, '10/09/2016'),
(13,4000, 2, '01/07/2016'),
(14,5000, 4, '01/07/2016'),
(15,5000, 3, '01/09/2016');
--From the following example I would expect records 5, 8, 9, 12 and 15 in my results.
with cte as
(
Select ID, Client, Level, ChangeDate,
Row_Number() over(Partition by Client order by ID) RowNum
from @t
)
select nxt.ID, nxt.Client, nxt.Level, nxt.ChangeDate, nxt.RowNum
from cte curr
left outer join cte nxt on curr.Client = nxt.Client
and curr.RowNum = nxt.RowNum - 1
where curr.level <> nxt.level
Check out the post in my signature line to learn how to post DDL, data and expected results.
P.S. It was easier to change the dates to MDY rather than go looking for the SET command to change it to DMY!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 25, 2016 at 12:59 am
Almost identical to LinksUp's solution, does the same thing;-)
😎
USE TEEST;
GO
SET NOCOUNT ON;
;WITH SAMPLE_DATA(ID,Client,Level,[Change Date]) AS
( SELECT ID,Client,Level,CONVERT(DATE,[Change Date],103)
FROM (VALUES
( 1,1000,2,'01/07/2016')
,( 2,1000,2,'03/08/2016')
,( 3,1000,2,'10/09/2016')
,( 4,2000,2,'01/05/2016')
,( 5,2000,3,'01/08/2016')
,( 6,2000,3,'01/09/2016')
,( 7,3000,2,'01/07/2016')
,( 8,3000,3,'15/08/2016')
,( 9,3000,4,'31/08/2016')
,(10,1000,2,'15/09/2016')
,(11,2000,3,'01/10/2016')
,(12,3000,3,'10/09/2016')
,(13,4000,2,'01/07/2016')
,(14,5000,4,'01/07/2016')
,(15,5000,3,'01/09/2016')
)X(ID,Client,Level,[Change Date])
)
,BASE_DATA AS
(
SELECT
SD.ID
,SD.Client
,SD.Level
,SD.[Change Date]
,ROW_NUMBER() OVER
(
PARTITION BY SD.Client
ORDER BY SD.[Change Date] ASC
) AS CLI_RID
FROM SAMPLE_DATA SD
)
SELECT
BD1.ID
,BD2.Client
,BD1.Level
,BD1.[Change Date]
FROM BASE_DATA BD1
INNER JOIN BASE_DATA BD2
ON BD1.Client = BD2.Client
AND BD1.CLI_RID = BD2.CLI_RID + 1
AND BD1.Level <> BD2.Level;
Output
ID Client Level Change Date
---- ------- ------ -----------
5 2000 3 2016-08-01
8 3000 3 2016-08-15
9 3000 4 2016-08-31
12 3000 3 2016-09-10
15 5000 3 2016-09-01
October 25, 2016 at 1:21 am
LinksUp (10/25/2016)
Since you posted in a 2008 forum, here is a simple self-join using a cte that gives you your output. If you had access to 2012 or greater you could do this with lead.
declare @t table(
ID int,
Client int,
Level int,
ChangeDate date
);
Insert @t (ID, Client, Level, ChangeDate) Values
(1,1000, 2, '01/07/2016'),
(2,1000, 2, '03/08/2016'),
(3,1000, 2, '10/09/2016'),
(4,2000, 2, '01/05/2016'),
(5,2000, 3, '01/08/2016'),
(6,2000, 3, '01/09/2016'),
(7,3000, 2, '01/07/2016'),
(8,3000, 3, '08/15/2016'),
(9,3000, 4, '08/31/2016'),
(10,1000, 2, '09/15/2016'),
(11,2000, 3, '01/10/2016'),
(12,3000, 3, '10/09/2016'),
(13,4000, 2, '01/07/2016'),
(14,5000, 4, '01/07/2016'),
(15,5000, 3, '01/09/2016');
--From the following example I would expect records 5, 8, 9, 12 and 15 in my results.
with cte as
(
Select ID, Client, Level, ChangeDate,
Row_Number() over(Partition by Client order by ID) RowNum
from @t
)
select nxt.ID, nxt.Client, nxt.Level, nxt.ChangeDate, nxt.RowNum
from cte curr
left outer join cte nxt on curr.Client = nxt.Client
and curr.RowNum = nxt.RowNum - 1
where curr.level <> nxt.level
Check out the post in my signature line to learn how to post DDL, data and expected results.
P.S. It was easier to change the dates to MDY rather than go looking for the SET command to change it to DMY!
Careful LinksUp, don't depend on the ID column as there is no guarantee that it is the correct order.
😎
October 25, 2016 at 9:18 am
Here's another 2008-friendly way that only accesses the table once. That comes at the cost of a couple extra sorts, and whether the extra IO or the extra sorting is more expensive will depend on a bunch of factors.
It just comes down to testing 🙂
WITH client_level_groups AS
(
SELECT ID,
Client,
Level,
ChangeDate,
group_number=ROW_NUMBER() OVER (PARTITION BY Client ORDER BY ChangeDate)
-
ROW_NUMBER() OVER (PARTITION BY Client,Level ORDER BY ChangeDate)
FROM @t
),
groups_ordered AS
(
SELECT *,
sequence_number=ROW_NUMBER() OVER (PARTITION BY Client,group_number,Level ORDER BY ChangeDate)
FROM client_level_groups
)
SELECT ID,
Client,
Level,
ChangeDate
FROM groups_ordered
WHERE group_number>0
AND
sequence_number=1;
Cheers!
October 25, 2016 at 9:20 am
Careful LinksUp, don't depend on the ID column as there is no guarantee that it is the correct order.
😎
I had fully intended to use the ChangeDate as ordering, but then I had to change some of the dates to get them in MDY format. Like I said before, it was easier to change the date them look up the proper set command!
Your use of Convert was clever!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
October 26, 2016 at 12:01 am
Thank you
October 26, 2016 at 12:02 am
Thank you
October 26, 2016 at 12:03 am
Thank you
October 26, 2016 at 12:05 am
Thank you very much
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply