vendredi 31 juillet 2015

Double increment where 2nd increment reflects 1st in sql for encounter data

I am building healthcare 837 encounters and need to set increments on the HL segments.

  • C1 based on what is set on Criteria1 and C2 based on Criteria2.
  • C2 will never have the same number as C1 and vice versa.
  • C1 I was able to pull using row_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