January 30, 2012 at 3:27 pm
I have data from CMS that is a standard Medicaid grouping mechanism called BETOS i think its called Barenson Eggers Type Of Service and is a classification mechanism for procedure codes. I understood from the docs that it was a hierarchy composed of seven top levels with 113 sub categories, looks like this, but what bothers me about it is that i have this prejudice that the hierarchy has to be composed of an id thats used once at the most granular level, and again for the 'works for ' level, so that the same id plays two different roles, once as the employee and once as a manager, as in adventure works, but here, it is not the case because the domain for lookup id is not the same as the one for keyid.
there is some bastardization of the datatypes here in that i think keyid was really supposed to be int, but this is NOT a lenght of the char column problem, its that the headers are identified by numbers and they are not represented in the child collection.
So, is this example inapplicable to trying to work with hierarchyid, and if so, can it be adapted?
I tried jamming in a distinct list of the header titles with casted ids into the child list, but it didnt buy me anything.
thanks very much
drew
CREATE TABLE [dbo].[Berenson_Eggers](
[KeyID] [char](1) NULL,
[TypeOfService] [char](25) NULL,
[LookupID] [char](3) NULL,
[SubCategory] [char](60) NULL
) ON [PRIMARY]
GO
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M1A],[Office visits - new ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M1B],[Office visits - established ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M2A],[Hospital visit - initial ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M2B],[Hospital visit - subsequent ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M2C],[Hospital visit - critical care ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M3 ],[Emergency room visit ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M4A],[Home visit ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M4B],[Nursing home visit ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M5A],[Specialist - pathology (HCPCS moved to T1G in 2003) ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M5B],[Specialist - psychiatry ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M5C],[Specialist - opthamology ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M5D],[Specialist - other ])
insert Berenson_Eggers values ( [1],[EVALUATION AND MANAGEMENT],[M6 ],[Consultations ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P0 ],[Anesthesia ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1A],[Major procedure - breast ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1B],[Major procedure - colectomy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1C],[Major procedure - cholecystectomy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1D],[Major procedure - turp ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1E],[Major procedure - hysterctomy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1F],[Major procedure - explor/decompr/excis disc ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P1G],[Major procedure - Other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P2A],[Major procedure, cardiovascular-CABG ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P2B],[Major procedure, cardiovascular-Aneurysm repair ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P2C],[Major Procedure, cardiovascular-Thromboendarterectomy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P2D],[Major procedure, cardiovascualr-Coronary angioplasty (PTCA) ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P2E],[Major procedure, cardiovascular-Pacemaker insertion ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P2F],[Major procedure, cardiovascular-Other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P3A],[Major procedure, orthopedic - Hip fracture repair ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P3B],[Major procedure, orthopedic - Hip replacement ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P3C],[Major procedure, orthopedic - Knee replacement ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P3D],[Major procedure, orthopedic - other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P4A],[Eye procedure - corneal transplant ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P4B],[Eye procedure - cataract removal/lens insertion ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P4C],[Eye procedure - retinal detachment ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P4D],[Eye procedure - treatment of retinal lesions ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P4E],[Eye procedure - other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P5A],[Ambulatory procedures - skin ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P5B],[Ambulatory procedures - musculoskeletal ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P5C],[Ambulatory procedures - groin hernia repair ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P5D],[Ambulatory procedures - lithotripsy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P5E],[Ambulatory procedures - other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P6A],[Minor procedures - skin ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P6B],[Minor procedures - musculoskeletal ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P6C],[Minor procedures - other (Medicare fee schedule) ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P6D],[Minor procedures - other (non-Medicare fee schedule) ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P7A],[Oncology - radiation therapy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P7B],[Oncology - other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8A],[Endoscopy - arthroscopy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8B],[Endoscopy - upper gastrointestinal ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8C],[Endoscopy - sigmoidoscopy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8D],[Endoscopy - colonoscopy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8E],[Endoscopy - cystoscopy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8F],[Endoscopy - bronchoscopy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8G],[Endoscopy - laparoscopic cholecystectomy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8H],[Endoscopy - laryngoscopy ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P8I],[Endoscopy - other ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P9A],[Dialysis services (Medicare Fee Schedule) ])
insert Berenson_Eggers values ( [2],[PROCEDURES ],[P9B],[Dialysis services (Non-Medicare Fee Schedule) ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I1A],[Standard imaging - chest ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I1B],[Standard imaging - musculoskeletal ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I1C],[Standard imaging - breast ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I1D],[Standard imaging - contrast gastrointestinal ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I1E],[Standard imaging - nuclear medicine ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I1F],[Standard imaging - other ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I2A],[Advanced imaging - CAT/CT/CTA: brain/head/neck ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I2B],[Advanced imaging - CAT/CT/CTA: other ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I2C],[Advanced imaging - MRI/MRA: brain/head/neck ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I2D],[Advanced imaging - MRI/MRA: other ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I3A],[Echography/ultrasonography - eye ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I3B],[Echography/ultrasonography - abdomen/pelvis ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I3C],[Echography/ultrasonography - heart ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I3D],[Echography/ultrasonography - carotid arteries ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I3E],[Echography/ultrasonography - prostate, transrectal ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I3F],[Echography/ultrasonography - other ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I4A],[Imaging/procedure - heart including cardiac catheter ])
insert Berenson_Eggers values ( [3],[IMAGING ],[I4B],[Imaging/procedure - other ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1A],[Lab tests - routine venipuncture (non Medicare fee schedule)])
insert Berenson_Eggers values ( [4],[TESTS ],[T1B],[Lab tests - automated general profiles ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1C],[Lab tests - urinalysis ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1D],[Lab tests - blood counts ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1E],[Lab tests - glucose ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1F],[Lab tests - bacterial cultures ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1G],[Lab tests - other (Medicare fee schedule) ])
insert Berenson_Eggers values ( [4],[TESTS ],[T1H],[Lab tests - other (non-Medicare fee schedule) ])
insert Berenson_Eggers values ( [4],[TESTS ],[T2A],[Other tests - electrocardiograms ])
insert Berenson_Eggers values ( [4],[TESTS ],[T2B],[Other tests - cardiovascular stress tests ])
insert Berenson_Eggers values ( [4],[TESTS ],[T2C],[Other tests - EKG monitoring ])
insert Berenson_Eggers values ( [4],[TESTS ],[T2D],[Other tests - other ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1A],[Medical/surgical supplies ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1B],[Hospital beds ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1C],[Oxygen and supplies ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1D],[Wheelchairs ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1E],[Other DME ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1F],[Prosthestic/Orthotic devices ])
insert Berenson_Eggers values ( [5],[DURABLE MEDICAL EQUIPMENT],[D1G],[Drugs Administered through DME ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1A],[Ambulance ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1B],[Chiropractic ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1C],[Enteral and parenteral ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1D],[Chemotherapy ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1E],[Other drugs ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1F],[Hearing and speech services ])
insert Berenson_Eggers values ( [6],[OTHER ],[O1G],[Immunizations/Vaccinations ])
insert Berenson_Eggers values ( [7],[EXCEPTIONS_UNCLASSIFIED ],[Y1 ],[Other - Medicare fee schedule ])
insert Berenson_Eggers values ( [7],[EXCEPTIONS_UNCLASSIFIED ],[Y2 ],[Other - non-Medicare fee schedule ])
insert Berenson_Eggers values ( [7],[EXCEPTIONS_UNCLASSIFIED ],[Z1 ],[Local codes ])
insert Berenson_Eggers values ( [7],[EXCEPTIONS_UNCLASSIFIED ],[Z2 ],[Undefined codes ])
January 31, 2012 at 7:23 am
:hehe:
Hello i can't understand your wards
So, is this example inapplicable to trying to work with hierarchyid, and if so, can it be adapted?
I tried jamming in a distinct list of the header titles with casted ids into the child list, but it didnt buy me anything.
thanks very much
drew
Could you explain that in simplist way
thanks
January 31, 2012 at 8:32 am
I don't see a data hierarchy in your code. What am I missing?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 9:46 am
i mean that the values in lookup id should also be used in key id, as analogous to employee id being used as manager id.
by stuffing the values, i mean i just made type of service a subcategory, and its key id a lookup id.
thanks very much
January 31, 2012 at 9:57 am
my entry point is the part of the BOL hierarchyid data type tutorial where you copy five or six columns out of the employee table that exercise the reflexive relationship 'works for' between manager/employee by reusing employee id as a pk and fk in the same table.
i mistakenly thought the BETOS table had the same relationship.
by hierarchy, my assumption was that
the subcategories collected under a type of service constituted the same relationship as
the employees collected under a manager in the 'works for' relationship in adventure works tutorial.
except i was stumped by if BETOS is a hierarchy, how come there are two different identification schemes being used, ints for the type of service as in KeyID (analagous to manager in the tutorial) but LookUpID for subcategories (or the employees in the tutorial) whereas in the tutorial, the parent and child are represented by the same identification scheme?
thanks for helping me understand where i fell off the truck
drew
January 31, 2012 at 10:19 am
For a HierarchyID structure on this, you'd end up with something like 1.M1A as the value for the first one. The nodes in a HierarchyID datatype can be strings just as easily as integers.
I don't know that I'd bother with HiearchyID for a 2-level hierarchy. It works best when you have variable depth.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 2:25 pm
After this level, there exist groups of CPT codes that are gathered under each lookupid...these are varying in number, e.g. there are 20 tests but 44 surgical procedures, so the breadth varies but not the depth.
thanks very much for your illustration and your time
drew
January 31, 2012 at 2:44 pm
If depth is non-variable, you're better off building the table explicitly for that, and avoiding the more complex hierarchy solutions. All of those are designed to handle variable depth, and in fixed-depth situations, they just eat performance without a real benefit.
If you want to do it as a hierarchy, I think it would be safe to assume this data doesn't get updated very often, and that means your best bet would be a "nested sets" hierarchy. Bing/Google "nested sets hierarchy" and you'll get a lot of info on it. Faster than just about any other hierarchy structure in a database when it comes to querying, just slow to update. But slow to update probably doesn't matter here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 31, 2012 at 5:25 pm
thanks very much
i just did, and it seems to be exactly the right model for this; as you observed, updates are rare, the levels are static, and the overhead of numbering their lefts and rights seems to be repaid by query simplicity.
I appreciate your help.
drew
February 1, 2012 at 5:38 am
drew.georgopulos (1/31/2012)
thanks very muchi just did, and it seems to be exactly the right model for this; as you observed, updates are rare, the levels are static, and the overhead of numbering their lefts and rights seems to be repaid by query simplicity.
I appreciate your help.
drew
Can you post what your structure looks like now? I ask because, in the quick look I took, I didn't see a hierarchy in the data itself and this is an interesting problem. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2012 at 6:44 am
Jeff Moden (2/1/2012)
drew.georgopulos (1/31/2012)
thanks very muchi just did, and it seems to be exactly the right model for this; as you observed, updates are rare, the levels are static, and the overhead of numbering their lefts and rights seems to be repaid by query simplicity.
I appreciate your help.
drew
Can you post what your structure looks like now? I ask because, in the quick look I took, I didn't see a hierarchy in the data itself and this is an interesting problem. Thanks.
Jeff: I had the same question and he answered it a few posts ago. It's a 2-level hierarchy is all.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 11:19 am
We decided that because there was no containership expressed by the BETOS scheme, it was not worth the effort of coding the rows to show their lefts and rights.
Instead, we are stopping with the tutorial's example here, because the data expresses our understanding of the coding scheme's author's documentation, one procedure code belongs to only one BETOS subcategory, so there are 357 procs in the testing category, and the first two of 38 evaluation and management procedures.
D1AT4540356
D1AT4543357
D1BE02501
D1BE02512
.
.
.
D1BE094037
D1BD1B 38
D1CD1C 1
Notwithstanding the doc's slightly aggressive description of the structure as a hierarchy...as you can see, its just two steps, so the hierarchyid data type is inapplicable as it represents the atomic bomb where a fly swatter would do...talk about the long way around the barn eh?
thank you both Messers GSquared and Moden
drew
February 1, 2012 at 3:02 pm
GSquared (2/1/2012)
Jeff Moden (2/1/2012)
drew.georgopulos (1/31/2012)
thanks very muchi just did, and it seems to be exactly the right model for this; as you observed, updates are rare, the levels are static, and the overhead of numbering their lefts and rights seems to be repaid by query simplicity.
I appreciate your help.
drew
Can you post what your structure looks like now? I ask because, in the quick look I took, I didn't see a hierarchy in the data itself and this is an interesting problem. Thanks.
Jeff: I had the same question and he answered it a few posts ago. It's a 2-level hierarchy is all.
Yep... saw that and it left me hungry because I didn't see how P0 fit into any of that.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 1, 2012 at 3:03 pm
drew.georgopulos (2/1/2012)
We decided that because there was no containership expressed by the BETOS scheme, it was not worth the effort of coding the rows to show their lefts and rights.Instead, we are stopping with the tutorial's example here, because the data expresses our understanding of the coding scheme's author's documentation, one procedure code belongs to only one BETOS subcategory, so there are 357 procs in the testing category, and the first two of 38 evaluation and management procedures.
D1AT4540356
D1AT4543357
D1BE02501
D1BE02512
.
.
.
D1BE094037
D1BD1B 38
D1CD1C 1
Notwithstanding the doc's slightly aggressive description of the structure as a hierarchy...as you can see, its just two steps, so the hierarchyid data type is inapplicable as it represents the atomic bomb where a fly swatter would do...talk about the long way around the barn eh?
thank you both Messers GSquared and Moden
drew
I LOVE A-Bombs... just not to kill flys with. Thanks for taking the time, Drew.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply