January 4, 2019 at 10:37 am
Run the code below
I need the PCP_CHANGE_INDEX to be 1 when the LINE_NUMBER = 5
Each time the member changes the VALUE in the PCP field, it should start with a 1.
If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t(BeneficiaryID VARCHAR(10), EligYear INT, EligMonth INT, PCP VARCHAR(10) );
INSERT INTO #t(BeneficiaryID, EligYear, EligMonth, PCP )
Select '12345678',2016,6, 100 UNION
Select '12345678',2016,7, 100 UNION
Select '12345678',2016,8, 200 UNION
Select '12345678',2016,9, 200 UNION
Select '12345678',2016,10, 100 UNION
Select '12345678',2017,2, 100 UNION
Select '12345678',2017,3, 100 UNION
Select '12345678',2017,4, 100
;
With START_POINT as
(
Select
BeneficiaryID, PCP,
(CAST( EligYear as VARCHAR) + RIGHT( ('0' + CAST(EligMonth as VARCHAR)),2) ) as MON
FROM #t
)
,
b as
(
Select
BeneficiaryID, PCP,MON,
ROW_NUMBER() OVER (PARTITION BY BeneficiaryID, PCP ORDER BY MON) as PCP_CHANGE_INDEX,
ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY MON) as Line_Number
FROM START_POINT
)
Select
Line_Number, BeneficiaryID,PCP,MON, PCP_CHANGE_INDEX
FROM b
ORDER BY MON
January 4, 2019 at 11:03 am
mw_sql_developer - Friday, January 4, 2019 10:37 AMRun the code below
I need the PCP_CHANGE_INDEX to be 1 when the LINE_NUMBER = 5Each time the member changes the VALUE in the PCP field, it should start with a 1.
If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t(BeneficiaryID VARCHAR(10), EligYear INT, EligMonth INT, PCP VARCHAR(10) );INSERT INTO #t(BeneficiaryID, EligYear, EligMonth, PCP )
Select '12345678',2016,6, 100 UNION
Select '12345678',2016,7, 100 UNION
Select '12345678',2016,8, 200 UNION
Select '12345678',2016,9, 200 UNION
Select '12345678',2016,10, 100 UNION
Select '12345678',2017,2, 100 UNION
Select '12345678',2017,3, 100 UNION
Select '12345678',2017,4, 100;
With START_POINT as
(
Select
BeneficiaryID, PCP,
(CAST( EligYear as VARCHAR) + RIGHT( ('0' + CAST(EligMonth as VARCHAR)),2) ) as MON
FROM #t
)
,
b as
(
Select
BeneficiaryID, PCP,MON,
ROW_NUMBER() OVER (PARTITION BY BeneficiaryID, PCP ORDER BY MON) as PCP_CHANGE_INDEX,
ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY MON) as Line_Number
FROM START_POINT)
Select
Line_Number, BeneficiaryID,PCP,MON, PCP_CHANGE_INDEX
FROM b
ORDER BY MON
How about you show us what your expected results should be based on the sample data.
January 4, 2019 at 11:32 am
Lynn Pettis - Friday, January 4, 2019 11:03 AMmw_sql_developer - Friday, January 4, 2019 10:37 AMRun the code below
I need the PCP_CHANGE_INDEX to be 1 when the LINE_NUMBER = 5Each time the member changes the VALUE in the PCP field, it should start with a 1.
If object_id('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t(BeneficiaryID VARCHAR(10), EligYear INT, EligMonth INT, PCP VARCHAR(10) );INSERT INTO #t(BeneficiaryID, EligYear, EligMonth, PCP )
Select '12345678',2016,6, 100 UNION
Select '12345678',2016,7, 100 UNION
Select '12345678',2016,8, 200 UNION
Select '12345678',2016,9, 200 UNION
Select '12345678',2016,10, 100 UNION
Select '12345678',2017,2, 100 UNION
Select '12345678',2017,3, 100 UNION
Select '12345678',2017,4, 100;
With START_POINT as
(
Select
BeneficiaryID, PCP,
(CAST( EligYear as VARCHAR) + RIGHT( ('0' + CAST(EligMonth as VARCHAR)),2) ) as MON
FROM #t
)
,
b as
(
Select
BeneficiaryID, PCP,MON,
ROW_NUMBER() OVER (PARTITION BY BeneficiaryID, PCP ORDER BY MON) as PCP_CHANGE_INDEX,
ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY MON) as Line_Number
FROM START_POINT)
Select
Line_Number, BeneficiaryID,PCP,MON, PCP_CHANGE_INDEX
FROM b
ORDER BY MONHow about you show us what your expected results should be based on the sample data.
Expected Output
1 , 12345678 , 100 , 201606 , 1
2 , 12345678 , 100 , 201607 , 2
3 , 12345678 , 200 , 201608 , 1
4 , 12345678 , 200 , 201609 , 2
5 , 12345678 , 100 , 201610 , 1
6 , 12345678 , 100 , 201702 , 2
7 , 12345678 , 100 , 201703 , 3
8 , 12345678 , 100 , 201704 , 4
January 4, 2019 at 12:40 pm
;
With START_POINT as
(
Select
BeneficiaryID, PCP,
(CAST( EligYear as VARCHAR) + RIGHT( ('0' + CAST(EligMonth as VARCHAR)),2) ) as MON
FROM #t
)
,
b as
(
Select
BeneficiaryID, PCP,MON,
(ROW_NUMBER() OVER (PARTITION BY BeneficiaryID, PCP ORDER BY MON)) as PCP_CHANGE_INDEX,
ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY MON) as Line_Number
FROM START_POINT
)
Select
Line_Number, BeneficiaryID,PCP,MON, PCP_CHANGE_INDEX,
(PCP_CHANGE_INDEX-1)%4+1 NewPCP_CHANGE_INDEX
FROM b
ORDER BY MON
January 4, 2019 at 1:20 pm
I don't have a solution, but I'm curious as to how to get the desired results. I don't think Jonathan will work with different data.
I modified the data and SQL a little.CREATE TABLE #t(BeneficiaryID VARCHAR(10), EligYear INT, EligMonth INT, PCP VARCHAR(10) );
INSERT INTO #t(BeneficiaryID, EligYear, EligMonth, PCP )
Select '12345678',2016,6, 100 UNION
Select '12345678',2016,7, 100 UNION
Select '12345678',2016,8, 100 UNION
Select '12345678',2016,9, 200 UNION
Select '12345678',2016,10, 100 UNION
Select '12345678',2017,2, 300 UNION
Select '12345678',2017,3, 100 UNION
Select '12345678',2017,4, 100
;
With START_POINT as
(
Select
BeneficiaryID, PCP,
(CAST( EligYear as VARCHAR) + RIGHT( ('0' + CAST(EligMonth as VARCHAR)),2) ) as MON
FROM #t
)
,
b as
(
Select
BeneficiaryID, PCP, MON,
DENSE_RANK() OVER (ORDER BY PCP) AS DRank,
RANK() OVER (PARTITION BY BeneficiaryID, PCP ORDER BY MON) as PCP_CHANGE_INDEX,
ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY MON) as Line_Number
FROM START_POINT
)
Select
Line_Number, BeneficiaryID, PCP, MON, PCP_CHANGE_INDEX, DRank,
(PCP_CHANGE_INDEX-1)%4+1 NewPCP_CHANGE_INDEX
FROM b
ORDER BY MON
My results:Line_Number BeneficiaryID PCP MON PCP_CHANGE_INDEX DRank NewPCP_CHANGE_INDEX
1 12345678 100 201606 1 1 1
2 12345678 100 201607 2 1 2
3 12345678 100 201608 3 1 3
4 12345678 200 201609 1 2 1
5 12345678 100 201610 4 1 4
6 12345678 300 201702 1 3 1
7 12345678 100 201703 5 1 1
8 12345678 100 201704 6 1 2
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply