I am building healthcare 837 encounters and need to set increments on the HL segments.
C1based on what is set onCriteria1andC2based onCriteria2.C2will never have the same number asC1and vice versa.C1I was able to pull usingrow_number() over(order by (select Criteria1))
It's the C2 I am having a problem with.
C1 | C2 | Criteria1 | Criteria2
1 | 2 | ID1 | NID1
1 | 3 | ID1 | NID2
1 | 4 | ID1 | NID3
5 | 6 | ID2 | NID4
5 | 7 | ID2 | NID5
5 | 8 | ID2 | NID6
9 |10 | ID3 | NID7
Simplified query:
SELECT cm.Criteria1, cm.Criteria2, cj.C1
FROM [dbo].[TBL1] cm
JOIN (
SELECT cm.Criteria1,
row_number() over(order by (select Criteria1)) as C1
FROM [dbo].[TBL1] cm
GROUP BY cm.Criteria1) cj on cj.Criteria1 = cm.Criteria1
GROUP BY cm.Criteria1, cm.Criteria2, cj.C1 Order by cj.C1
Aucun commentaire:
Enregistrer un commentaire