Calculating derived data from base data - SQL server

Hello I have a datasets provided :

Product    Diagnosis   Specialty   PackCount  ProdcutCount  DiagnosisCount   SpecialtyCount  Product By Diagnosis. . 
359126	E88.9 	API	3                         
359126	H20.9 	RHE	3
359126	I10.9 	API	4
359126	I51.6 	API	1
359126	I84.6 	API	3
359126	J01.9 	API	2
359126	J06.9 	API	2
359126	J44.90	RHE	12
359126	K50.0 	API	94
359126	K50.0 	GAS	2
359126	K50.1 	API	9
359126	K50.1 	RHE	16
359126	K50.8 	API	5
359126	K50.9 	API	53
359126	K50.9 	GAS	9
359126	K50.9 	PAE	4
359126	K51.0 	API	7
359126	K51.8 	GAS	2
359126	K51.9 	API	81
359126	K51.9 	GAS	18
359126	K52.9 	API	3
359126	K60.3 	GAS	3
359126	K76.0 	GAS	1
359126	K85.00	API	3
359126	L40.0 	DER	69
359126	L40.0 	DIA	40
359126	L40.5 	API	6
359126	L40.5 	DER	1
359126	L40.5 	RHE	139
359126	L40.9 	API	4
359126	L40.9 	DER	43
359126	M05.30	RHE	1
359126	M05.8 	RHE	2
359126	M05.80	RHE	148
359126	M05.90	RHE	97
359126	M06.0 	DIA	2
359126	M06.0 	RHE	7
359126	M06.00	RHE	40
359126	M06.90	RHE	81
359126	M06.99	API	40
359126	M06.99	RHE	74
359126	M07.00	RHE	66
359126	M07.30	RHE	6
359126	M13.0 	ORT	8
359126	M15.9 	ORT	1
359126	M17.9 	ORT	2
359126	M45.00	RHE	174
359126	M45.05	API	2
359126	M45.07	RHE	30
359126	M45.08	RHE	2
359126	M45.09	API	6
359126	M45.09	RHE	278
359126	M46.00	RHE	5
359126	M46.1 	RHE	14
359126	M46.8 	RHE	34
359126	M46.88	RHE	62
359126	M76.6 	RHE	6
359126	Z03.9 	RHE	6
359126	Z51.2 	RHE	1
359128	D50.8 	RHE	2
359128	E04.2 	RHE	1
359128	E66.9 	RHE	1
359128	E66.99	RHE	16
359128	H20.9 	RHE	2
359128	I10.9 	API	4
359128	I33.0 	RHE	1
359128	I51.6 	API	1
359128	I84.6 	API	2
359128	J01.0 	API	2
359128	J01.9 	API	2
359128	J03.9 	API	2
359128	J06.9 	API	6
359128	J20.6 	API	2
359128	J44.90	RHE	12
359128	J45.0 	API	1
359128	K29.5 	GAS	6
359128	K50.0 	API	242
359128	K50.0 	GAS	165
359128	K50.0 	RHE	34
359128	K50.1 	API	1
359128	K50.1 	GAS	48
359128	K50.9 	API	165
359128	K50.9 	DIA	3
359128	K50.9 	GAS	125
359128	K50.9 	PAE	3
359128	K50.9 	RHE	1
359128	K51.0 	API	10
359128	K51.2 	GAS	4
359128	K51.8 	GAS	28
359128	K51.9 	API	155
359128	K51.9 	GAS	280
359128	K52.9 	API	5
359128	K56.6 	GAS	1
359128	K60.3 	GAS	3
359128	K76.0 	GAS	1
359128	K85.00	API	6
359128	L02.9 	API	9
359128	L40.0 	DER	66
359128	L40.0 	DIA	52
359128	L40.0 	RHE	9
359128	L40.5 	API	10
359128	L40.5 	DER	3
359128	L40.5 	RHE	101
359128	L40.8 	DER	11
359128	L40.9 	API	12
359128	L40.9 	DER	67
359128	L98.8 	API	2
359128	M05.80	RHE	86
359128	M05.90	RHE	191
359128	M05.99	RHE	28
359128	M06.00	RHE	140
359128	M06.90	RHE	56
359128	M06.99	API	11
359128	M06.99	RHE	41
359128	M07.0 	RHE	9
359128	M07.00	RHE	2
359128	M07.30	RHE	8
359128	M35.0 	RHE	2
359128	M35.2 	RHE	7
359128	M35.3 	RHE	3
359128	M45.0 	RHE	68
359128	M45.00	RHE	325
359128	M45.04	RHE	9
359128	M45.07	RHE	37
359128	M45.08	RHE	2
359128	M45.09	API	66
359128	M45.09	RHE	61
359128	M46.00	RHE	16
359128	M46.1 	RHE	57
359128	M46.80	RHE	3
359128	M46.89	RHE	8
359128	M46.9 	RHE	38
359128	M46.90	RHE	17
359128	M76.6 	RHE	6
359128	M81.50	RHE	1
359128	N13.5 	API	2
359128	N28.9 	API	2
359128	T74.1 	API	4
359128	Z03.9 	RHE	12
359128	Z51.2 	RHE	5
359128	Z90.4 	RHE	1
466748	H04.1 	RHE	1
466748	H26.9 	RHE	4
466748	I50.9 	RHE	5
466748	K29.7 	ORT	2
466748	K50.9 	API	2
466748	K50.9 	GAS	5
466748	K50.9 	PAE	4
466748	K50.9 	RHE	2
466748	K51.0 	GAS	1
466748	K51.9 	GAS	1
466748	K57.90	RHE	1
466748	L23.9 	RHE	1
466748	L40.0 	DER	8
466748	L40.0 	RHE	7
466748	L40.4 	DER	1
466748	L40.5 	API	2
466748	L40.5 	DER	25
466748	L40.5 	ORT	1
466748	L40.5 	RHE	34
466748	L40.9 	DER	1
466748	M05.80	ORT	1
466748	M05.80	RHE	89
466748	M05.89	API	1
466748	M05.9 	RHE	2
466748	M05.90	DIA	6
466748	M05.90	RHE	104
466748	M05.99	API	1
466748	M06.0 	ORT	1
466748	M06.0 	RHE	8
466748	M06.00	ORT	3
466748	M06.00	RHE	25
466748	M06.90	API	7
466748	M06.90	ORT	17
466748	M06.90	RHE	42
466748	M06.99	API	8
466748	M06.99	ORT	16
466748	M06.99	RHE	37
466748	M08.99	API	1
466748	M08.99	RHE	1
466748	M13.0 	API	1
466748	M25.56	API	2
466748	M32.1 	RHE	3
466748	M35.0 	RHE	1
466748	M45.0 	RHE	3
466748	M45.00	API	3
466748	M45.00	ORT	7
466748	M45.00	RHE	35
466748	M45.08	RHE	1
466748	M45.09	API	8
466748	M45.09	RHE	27
466748	M46.1 	ORT	2
466748	M46.1 	RHE	1
466748	M46.80	RHE	1
466748	M46.9 	RHE	9
466748	M54.12	ORT	6
466748	M54.5 	API	2
466748	M54.6 	RHE	2
466748	M71.2 	RHE	1
466748	M75.4 	ORT	1
466748	M79.70	API	1
466748	M80.4 	RHE	1
466748	M81.9 	RHE	3
466748	M81.99	RHE	1
466748	T92.2 	ORT	1
490387	L40.0 	DER	4
490387	L40.3 	DER	1
490387	L40.5 	RHE	2
490387	M05.09	RHE	2
490387	M05.89	RHE	16
490387	M05.99	API	9
490387	M05.99	RHE	1
490387	M06.09	RHE	15
490387	M06.90	PAE	4
490387	M06.90	RHE	2
490387	M45.0 	DIA	12
490387	M45.00	API	3
490387	M45.00	RHE	1
490387	M45.09	API	2
490387	M45.09	RHE	19
490387	M46.1 	RHE	9
490387	M46.90	RHE	1
490387	M46.99	RHE	15
490387	M62.8 	ORT	1
490387	M79.09	RHE	2
494215	M06.90	API	1
494215	M06.99	API	2
494808	M05.81	API	9
503711	D50.9 	API	2
503711	D72.8 	RHE	8
503711	E10.9 	API	42
503711	L40.0 	DER	13
503711	L40.0 	RHE	1
503711	L40.5 	API	3
503711	L40.5 	DER	3
503711	L40.5 	RHE	12
503711	L40.8 	DER	1
503711	M05.80	RHE	5
503711	M05.9 	RHE	7
503711	M05.90	RHE	10
503711	M05.99	RHE	2
503711	M06.0 	RHE	9
503711	M06.00	ORT	5
503711	M06.00	RHE	10
503711	M06.8 	API	2
503711	M06.9 	API	37
503711	M06.90	RHE	33
503711	M06.99	API	8
503711	M06.99	RHE	66
503711	M08.04	PAE	5
503711	M08.1 	RHE	2
503711	M08.29	PAE	4
503711	M08.40	PAE	19
503711	M08.99	PAE	5
503711	M13.96	ORT	4
503711	M16.9 	RHE	19
503711	M45.00	RHE	32
503711	M45.09	API	2
503711	M45.09	RHE	30
503711	M81.49	API	2
503712	F62.80	ORT	1
503712	J18.0 	API	1
503712	K29.5 	RHE	3
503712	K76.9 	RHE	2
503712	L40.0 	DER	5
503712	L40.5 	DER	1
503712	L40.5 	RHE	22
503712	M05.80	RHE	11
503712	M05.89	API	1
503712	M05.9 	RHE	19
503712	M05.90	RHE	8
503712	M05.98	RHE	4
503712	M05.99	RHE	7
503712	M06.0 	RHE	4
503712	M06.00	RHE	5
503712	M06.90	RHE	12
503712	M06.99	API	7
503712	M06.99	RHE	1
503712	M08.00	RHE	5
503712	M08.1 	RHE	7
503712	M08.4 	RHE	9
503712	M08.45	PAE	3
503712	M08.49	RHE	9
503712	M08.99	RHE	1
503712	M13.0 	PAE	2
503712	M20.5 	RHE	1
503712	M35.3 	API	10
503712	M45.0 	RHE	10
503712	M45.00	API	3
503712	M45.00	ORT	1
503712	M45.00	RHE	10
503712	M45.06	API	1
503712	M45.07	ORT	2
503712	M45.09	API	7
503712	M45.09	RHE	43
503712	R20.2 	API	1
503712	Z51.2 	RHE	1
506931	M06.99	API	2
506931	M07.3 	API	1
506931	N99.3 	API	7
506932	M05.89	API	1
506932	M05.90	RHE	3
506933	M05.90	RHE	6
506934	K50.1 	API	2
506934	M46.9 	RHE	1
508928	L40.8 	API	12
508928	M06.99	RHE	1
508929	L40.0 	DER	1
508929	M13.0 	API	1
514923	J44.99	API	1
514923	K92.1 	API	4
514923	L40.0 	DER	5
514923	M05.80	ORT	1
514923	M06.00	ORT	5
514923	M06.90	ORT	3
517739	M08.06	PAE	2
521210	M06.90	API	2
521211	L40.8 	DER	1
521211	M05.9 	RHE	1
521211	M05.90	RHE	2
521211	M05.99	API	1
521211	M06.9 	API	1
521211	M06.90	API	7
521211	M06.90	RHE	1
521211	M65.1 	ORT	1
521211	M81.99	API	1
522235	M05.90	RHE	4
522235	M06.99	API	1
522235	M06.99	DIA	5
522235	M07.39	RHE	16
522235	M13.0 	API	54
522235	M45.09	RHE	32
523416	M08.49	PAE	1
524555	C92.7 	DER	1
524555	D72.8 	RHE	3
524555	E11.8 	RHE	1
524555	E14.40	ORT	2
524555	E78.0 	RHE	1
524555	E79.0 	RHE	3
524555	F41.0 	DER	2
524555	I10.90	API	6
524555	I10.90	RHE	7
524555	I51.9 	RHE	2
524555	I82.9 	RHE	2
524555	I95.1 	RHE	2
524555	J20.9 	RHE	1
524555	J45.9 	PAE	1
524555	K25.7 	RHE	2
524555	K50.0 	API	3
524555	K50.0 	GAS	68
524555	K50.1 	GAS	2
524555	K50.8 	API	14
524555	K50.8 	RHE	4
524555	K50.9 	API	9
524555	K50.9 	GAS	263
524555	K50.9 	RHE	1
524555	K51.8 	GAS	2
524555	K51.9 	GAS	6
524555	K71.7 	RHE	2
524555	K74.3 	RHE	56
524555	L02.4 	RHE	1
524555	L40.0 	DER	49
524555	L40.0 	RHE	27
524555	L40.5 	API	66
524555	L40.5 	DER	30
524555	L40.5 	ORT	6
524555	L40.5 	RHE	311
524555	L40.8 	DER	9
524555	L40.9 	DER	15
524555	L93.2 	API	6
524555	M02.9 	PAE	2
524555	M05.80	ORT	4
524555	M05.80	RHE	327
524555	M05.9 	RHE	7
524555	M05.90	ORT	1
524555	M05.90	RHE	309
524555	M05.98	RHE	18
524555	M05.99	API	16
524555	M05.99	RHE	166
524555	M06.0 	RHE	3
524555	M06.00	ORT	2
524555	M06.00	RHE	95
524555	M06.09	RHE	6
524555	M06.10	RHE	4
524555	M06.8 	RHE	1
524555	M06.9 	API	8
524555	M06.9 	DIA	3
524555	M06.9 	ORT	4
524555	M06.9 	RHE	2
524555	M06.90	API	4
524555	M06.90	ORT	51
524555	M06.90	RHE	148
524555	M06.99	API	65
524555	M06.99	ORT	16
524555	M06.99	RHE	297
524555	M07.00	RHE	14
524555	M07.2 	RHE	3
524555	M07.30	RHE	21
524555	M07.39	RHE	1
524555	M08.09	PAE	1
524555	M08.45	PAE	2
524555	M08.49	RHE	20
524555	M08.99	RHE	3
524555	M13.0 	API	1
524555	M13.0 	DIA	4
524555	M13.0 	ORT	10
524555	M13.1 	ORT	6
524555	M13.11	ORT	2
524555	M13.15	ORT	1
524555	M13.92	ORT	3
524555	M16.1 	ORT	3
524555	M16.9 	ORT	1
524555	M17.9 	ORT	3
524555	M19.9 	ORT	1
524555	M19.91	ORT	2
524555	M24.9 	ORT	1
524555	M25.40	ORT	4
524555	M35.3 	RHE	1
524555	M45.0 	API	3
524555	M45.0 	DIA	7
524555	M45.0 	RHE	157
524555	M45.00	ORT	4
524555	M45.00	RHE	138
524555	M45.06	ORT	16
524555	M45.07	RHE	5
524555	M45.08	RHE	4
524555	M45.09	API	62
524555	M45.09	ORT	8
524555	M45.09	RHE	205
524555	M46.1 	ORT	5
524555	M46.1 	RHE	14
524555	M46.80	RHE	4
524555	M46.9 	RHE	13
524555	M46.90	RHE	1
524555	M47.8 	ORT	1
524555	M53.80	RHE	3
524555	M54.10	ORT	7
524555	M54.12	ORT	2
524555	M54.16	RHE	9
524555	M54.17	ORT	7
524555	M54.5 	ORT	3
524555	M65.99	ORT	3
524555	M71.2 	RHE	5
524555	M77.4 	ORT	1
524555	M77.9 	ORT	1
524555	M79.09	RHE	1
524555	M79.19	RHE	1
524555	M80.4 	RHE	1
524555	M80.90	ORT	7
524555	M80.90	RHE	1
524555	M80.99	RHE	2
524555	M81.9 	RHE	25
524555	M81.90	ORT	2
524555	N32.1 	GAS	2
524555	R52.2 	ORT	2
524555	R52.2 	RHE	4
524555	R63.5 	API	1
524555	S91.3 	ORT	1
524555	T84.9 	ORT	3
524555	Z23.8 	RHE	4
524555	Z25.1 	RHE	2
524555	Z51.2 	RHE	1
525736	J20.9 	PAE	24
525736	K52.9 	API	3
525736	L40.0 	DER	1
525736	L40.1 	API	3
525736	L40.3 	API	2
525736	L40.5 	API	51
525736	L40.9 	DER	4
525736	M05.89	RHE	34
525736	M05.90	RHE	4
525736	M05.99	API	26
525736	M06.00	RHE	1
525736	M06.09	RHE	14
525736	M06.88	API	2
525736	M06.90	RHE	10
525736	M06.99	API	38
525736	M06.99	DIA	1
525736	M07.39	RHE	1
525736	M08.45	PAE	1
525736	M45.09	API	5
525736	M46.1 	RHE	2
525736	M46.80	RHE	1
525736	M79.09	API	3
526853	B00.1 	API	1
526853	J32.0 	DER	5
526853	K50.9 	DIA	1
526853	K80.2 	DIA	1
526853	K80.80	DIA	1
526853	L40.0 	DER	28
526853	L40.5 	DER	1
526853	L40.5 	RHE	3
526853	L81.0 	DER	5
526853	M05.80	RHE	6
526853	M05.89	API	1
526853	M05.90	RHE	1
526853	M06.00	ORT	1
526853	M06.00	RHE	4
526853	M06.99	ORT	1
526853	M08.00	ORT	2
526853	M25.57	API	1
526853	M45.00	RHE	6
526853	M45.07	ORT	1
526853	M75.4 	ORT	1
530627	L40.0 	DER	1
530627	M05.90	API	1
530627	M06.99	API	22
531995	L40.0 	DER	2
531997	M05.90	RHE	3
531997	M06.9 	API	2
531997	M06.99	API	4
531997	M45.0 	RHE	1
534200	K50.0 	GAS	2
534200	L40.0 	RHE	1
534200	L40.5 	RHE	1
534200	M05.84	API	3
534200	M05.9 	RHE	1
534200	M05.90	RHE	60
534200	M06.00	RHE	4
534200	M06.10	RHE	13
534200	M06.9 	API	14
534200	M15.3 	RHE	3
534200	M32.1 	API	1
534200	M45.00	RHE	4
534200	M45.07	API	4
534200	M45.09	API	9
534200	M45.09	RHE	8
534200	M46.90	RHE	2
539409	E79.0 	ORT	1
539409	E79.9 	ORT	6
539409	L40.5 	ORT	5
539409	L40.5 	PAE	2
539409	L40.8 	DER	15
539409	L40.9 	DER	3
539409	M05.80	ORT	7
539409	M05.80	RHE	1
539409	M06.00	ORT	44
539409	M45.00	ORT	17
539409	M45.00	RHE	2
539409	M45.07	ORT	7
539409	Z00.0 	RHE	1
542533	M05.90	RHE	2
542533	M06.99	ORT	1
542533	M13.0 	PAE	1
542533	R52.1 	ORT	2
542536	L40.5 	DER	2
542536	M05.89	RHE	23
542536	M06.09	RHE	3
542536	M06.99	API	5
542536	M06.99	DIA	1
542536	M06.99	RHE	11
547211	K50.8 	API	10
547211	L40.5 	GAS	1
547211	M05.80	RHE	7
547211	M05.89	API	1
547211	M06.00	RHE	12
547211	M06.90	API	5
547211	M06.90	ORT	6
547211	M81.99	API	1
547211	Z92.6 	API	10
554310	M45.0 	RHE	6
558334	M45.00	RHE	1
558471	K50.9 	GAS	4
558471	K51.0 	API	4
558471	L40.5 	RHE	19
558471	M06.99	RHE	9
558471	M45.00	RHE	3
558471	M45.09	API	4
558471	M45.09	RHE	12
558471	M46.9 	RHE	9
558472	K51.0 	API	1
558472	L40.5 	RHE	1
558472	M05.9 	RHE	11
558472	M05.90	RHE	4
558472	M45.09	RHE	32
561801	K50.1 	API	2
561801	K50.8 	API	2
561801	K52.8 	RHE	11
561801	L40.0 	DER	1
561801	L40.5 	RHE	1
561801	M05.9 	RHE	4
561801	M06.00	RHE	5
561801	M06.4 	API	1
561801	M06.90	RHE	1
561801	M45.09	RHE	6
561804	D84.9 	GAS	4
561804	K50.0 	GAS	5
561804	K50.8 	API	7
561804	K50.82	API	16
561804	K50.9 	GAS	10
561804	K51.3 	API	4
561804	K51.3 	GAS	4
561804	L40.0 	DER	1
561804	L40.5 	RHE	10
561804	M06.0 	RHE	1
561804	M06.00	RHE	2
561804	M06.90	RHE	2
561804	M07.00	RHE	1
561804	M45.00	RHE	8
561804	M45.09	RHE	5
561804	M46.9 	RHE	3
561804	Z23.8 	RHE	2
562088	L40.8 	DER	10
563047	K51.0 	API	2
563047	M05.90	RHE	13
563047	M06.00	RHE	4
563047	M45.09	RHE	12
563047	M46.1 	RHE	2
564047	L40.9 	API	1
568196	L28.2 	PAE	2
568196	M08.09	PAE	14
568196	M08.45	PAE	31
568196	M08.46	API	1
568196	M08.9 	PAE	6
568196	M20.5 	RHE	1
568196	M35.6 	PAE	2
568196	M79.00	API	27
568196	M87.8 	API	3
568199	I45.1 	RHE	6
568199	J32.9 	API	3
568199	L40.0 	DER	24
568199	L40.4 	DER	48
568199	L40.5 	API	1
568199	L40.5 	DIA	1
568199	L40.5 	PAE	1
568199	L40.5 	RHE	18
568199	L40.8 	DER	4
568199	L40.9 	API	1
568199	L40.9 	DER	20
568199	M05.09	RHE	18
568199	M05.80	ORT	2
568199	M05.80	RHE	4
568199	M05.89	RHE	18
568199	M05.90	RHE	13
568199	M05.99	RHE	2
568199	M06.00	RHE	2
568199	M06.09	RHE	1
568199	M06.90	API	1
568199	M06.90	RHE	7
568199	M06.99	API	4
568199	M06.99	PAE	1
568199	M06.99	RHE	2
568199	M08.00	RHE	1
568199	M08.3 	RHE	10
568199	M25.50	ORT	1
568199	M45.00	ORT	3
568199	M45.00	RHE	3
568199	M45.09	API	7
568199	M45.09	RHE	22
568199	M53.0 	RHE	1
568199	M54.3 	ORT	2
568199	M79.09	API	1
568199	M79.70	ORT	4
568199	M80.99	RHE	2
568199	S93.4 	ORT	1
568200	B17.1 	RHE	14
568200	D22.9 	DER	17
568200	D64.9 	RHE	1
568200	D72.8 	RHE	3
568200	E04.0 	API	1
568200	E04.9 	RHE	2
568200	E14.90	RHE	3
568200	F32.1 	RHE	1
568200	G25.9 	API	2
568200	G47.9 	API	1
568200	I10.90	API	15
568200	I10.90	RHE	26
568200	I95.1 	RHE	15
568200	K42.9 	RHE	2
568200	L40.0 	DER	85
568200	L40.0 	RHE	14
568200	L40.4 	DER	1
568200	L40.5 	API	5
568200	L40.5 	DER	8
568200	L40.5 	ORT	3
568200	L40.5 	RHE	48
568200	L40.8 	DER	1
568200	L40.9 	API	5
568200	L40.9 	DER	12
568200	L98.4 	API	2
568200	M05.8 	RHE	10
568200	M05.80	RHE	121
568200	M05.89	API	1
568200	M05.89	RHE	2
568200	M05.9 	RHE	33
568200	M05.90	RHE	148
568200	M05.98	RHE	1
568200	M05.99	API	1
568200	M05.99	RHE	4
568200	M06.0 	API	3
568200	M06.0 	RHE	7
568200	M06.00	RHE	46
568200	M06.09	API	13
568200	M06.89	API	1
568200	M06.9 	DIA	8
568200	M06.9 	RHE	7
568200	M06.90	API	28
568200	M06.90	ORT	5
568200	M06.90	RHE	91
568200	M06.99	API	26
568200	M06.99	ORT	34
568200	M06.99	RHE	3
568200	M07.00	RHE	1
568200	M07.30	RHE	4
568200	M07.39	RHE	3
568200	M08.00	RHE	3
568200	M08.29	RHE	3
568200	M13.0 	DIA	13
568200	M13.0 	RHE	2
568200	M16.9 	RHE	1
568200	M19.94	API	1
568200	M35.3 	API	1
568200	M35.3 	RHE	1
568200	M45.00	RHE	92
568200	M45.09	API	4
568200	M45.09	RHE	5
568200	M46.06	RHE	1
568200	M46.1 	RHE	1
568200	M46.80	RHE	12
568200	M46.9 	RHE	2
568200	M46.90	RHE	8
568200	M53.9 	API	4
568200	M54.14	ORT	24
568200	M54.17	ORT	4
568200	M70.6 	ORT	1
568200	M80.4 	RHE	11
568200	M81.9 	RHE	7
568200	M81.99	DER	2
568200	R52.2 	RHE	1
568200	T78.9 	RHE	3
568200	Z12.9 	RHE	1
568610	F48.0 	RHE	2
568610	I77.6 	API	4
568610	K76.9 	RHE	2
568610	L40.0 	DER	26
568610	L40.0 	RHE	3
568610	L40.3 	DER	2
568610	L40.5 	API	17
568610	L40.5 	DER	8
568610	L40.5 	RHE	53
568610	L40.8 	DER	1
568610	L40.9 	DER	1
568610	M05.80	ORT	3
568610	M05.80	RHE	42
568610	M05.9 	RHE	1
568610	M05.90	RHE	27
568610	M05.98	RHE	3
568610	M05.99	RHE	10
568610	M06.00	ORT	2
568610	M06.00	RHE	20
568610	M06.9 	API	7
568610	M06.90	API	2
568610	M06.90	RHE	47
568610	M06.99	API	9
568610	M06.99	RHE	45
568610	M07.00	RHE	6
568610	M07.2 	RHE	3
568610	M07.39	RHE	3
568610	M08.09	PAE	6
568610	M08.45	PAE	2
568610	M08.46	RHE	5
568610	M08.90	RHE	2
568610	M22.4 	ORT	1
568610	M35.3 	RHE	6
568610	M42.99	ORT	1
568610	M45.0 	ORT	8
568610	M45.00	ORT	1
568610	M45.00	RHE	46
568610	M45.07	RHE	3
568610	M45.09	RHE	142
568610	M46.80	RHE	1
568610	M53.1 	ORT	1
568610	M79.70	API	1
568610	M80.99	RHE	5
568610	M81.99	ORT	1
568610	Q76.4 	ORT	1
568610	R52.2 	ORT	1
568610	R52.2 	RHE	1
568610	Z51.2 	RHE	1
568611	I50.9 	RHE	6
568611	K51.9 	RHE	1
568611	K76.9 	RHE	2
568611	L40.0 	DER	32
568611	L40.5 	API	36
568611	L40.5 	DER	6
568611	L40.5 	ORT	6
568611	L40.5 	RHE	50
568611	L40.9 	API	2
568611	L40.9 	DER	5
568611	M05.8 	API	2
568611	M05.8 	RHE	3
568611	M05.80	ORT	26
568611	M05.80	RHE	37
568611	M05.9 	RHE	4
568611	M05.90	RHE	51
568611	M05.99	API	3
568611	M05.99	RHE	5
568611	M06.0 	RHE	6
568611	M06.00	API	1
568611	M06.00	ORT	8
568611	M06.00	RHE	76
568611	M06.9 	API	4
568611	M06.90	API	3
568611	M06.90	ORT	2
568611	M06.90	RHE	66
568611	M06.99	API	32
568611	M06.99	RHE	55
568611	M08.00	RHE	1
568611	M08.29	PAE	3
568611	M08.46	RHE	7
568611	M08.99	RHE	5
568611	M13.0 	PAE	1
568611	M13.16	API	1
568611	M13.99	API	1
568611	M15.9 	ORT	2
568611	M16.9 	ORT	6
568611	M35.3 	RHE	2
568611	M45.00	ORT	1
568611	M45.00	RHE	32
568611	M45.07	ORT	3
568611	M45.09	RHE	28
568611	M48.49	API	18
568611	M54.5 	RHE	1
568611	M79.0 	API	1
568611	M81.9 	RHE	37
568611	M81.99	DER	1
568611	M81.99	RHE	7
568611	R52.9 	API	1
568611	T14.9 	DER	2
568611	Z27.3 	RHE	10
568612	E06.3 	RHE	2
568612	E10.70	RHE	1
568612	E73.9 	DER	4
568612	F17.1 	API	1
568612	G62.9 	RHE	4
568612	I25.9 	API	1
568612	I34.1 	DIA	2
568612	I48.19	RHE	2
568612	K50.9 	RHE	1
568612	K60.3 	RHE	1
568612	L40.0 	DER	171
568612	L40.0 	RHE	19
568612	L40.5 	API	25
568612	L40.5 	DER	76
568612	L40.5 	ORT	7
568612	L40.5 	RHE	351
568612	L40.8 	DER	30
568612	L40.9 	DER	32
568612	L40.9 	DIA	1
568612	M05.8 	RHE	1
568612	M05.80	RHE	318
568612	M05.90	RHE	165
568612	M05.98	RHE	15
568612	M05.99	API	53
568612	M05.99	RHE	223
568612	M06.0 	RHE	24
568612	M06.00	RHE	100
568612	M06.09	RHE	5
568612	M06.80	RHE	1
568612	M06.90	API	11
568612	M06.90	ORT	4
568612	M06.90	RHE	65
568612	M06.99	API	129
568612	M06.99	GAS	4
568612	M06.99	RHE	208
568612	M07.00	RHE	15
568612	M07.2 	RHE	2
568612	M07.30	RHE	11
568612	M07.31	RHE	3
568612	M07.34	RHE	2
568612	M07.39	RHE	7
568612	M08.00	RHE	2
568612	M08.09	API	13
568612	M08.4 	RHE	4
568612	M13.0 	API	8
568612	M13.0 	DIA	3
568612	M13.0 	ORT	3
568612	M13.0 	RHE	9
568612	M13.80	RHE	3
568612	M13.99	RHE	3
568612	M18.9 	ORT	3
568612	M21.60	RHE	3
568612	M25.5 	ORT	1
568612	M25.99	RHE	4
568612	M35.1 	RHE	4
568612	M35.3 	RHE	5
568612	M45.0 	RHE	107
568612	M45.00	API	1
568612	M45.00	ORT	3
568612	M45.00	RHE	179
568612	M45.06	ORT	1
568612	M45.09	API	95
568612	M45.09	ORT	10
568612	M45.09	RHE	62
568612	M46.1 	RHE	2
568612	M46.80	RHE	1
568612	M47.80	RHE	1
568612	M53.1 	RHE	3
568612	M54.16	RHE	3
568612	M79.00	RHE	1
568612	M79.01	RHE	3
568612	M79.09	ORT	11
568612	M79.81	RHE	2
568612	M80.90	ORT	1
568612	M80.90	RHE	3
568612	M81.9 	RHE	14
568612	M81.99	API	1
568612	M81.99	DER	1
568612	M81.99	RHE	3
568612	Q66.8 	ORT	2
568612	R25.1 	ORT	1
568612	R52.1 	ORT	1
568612	R52.2 	ORT	1
568612	R52.2 	RHE	4
568612	Z51.2 	RHE	10
571420	L40.0 	DER	2
571420	M46.90	RHE	11
571425	K50.0 	GAS	1
571425	L40.0 	DER	2
571425	M46.90	RHE	11
574538	L40.9 	DER	1
574538	M05.9 	RHE	1
574544	M05.90	RHE	4
574544	M06.00	RHE	19
574544	M08.45	PAE	2
574544	M13.0 	PAE	1
574546	L40.0 	DER	3
574546	M05.90	RHE	2
574546	M06.00	RHE	3
574546	M06.09	RHE	5
574546	M06.99	API	1
574546	M45.00	RHE	1
574546	M45.09	API	1
574546	Z51.88	API	14
574547	L40.0 	DER	2
574547	L40.5 	RHE	8
574547	M05.90	RHE	4
574547	M06.00	RHE	19
574547	M06.99	RHE	25
574547	M45.0 	RHE	1
574547	M45.09	RHE	6
574547	M46.99	RHE	10
574770	L40.0 	DER	3
574770	M79.09	API	1
574814	M05.80	RHE	1
574814	M06.99	RHE	1
574814	M45.09	API	1
574816	L40.0 	DER	8
574818	M06.00	API	1
574820	L71.0 	DER	2
574820	M05.90	RHE	5
574820	M06.00	API	4
574820	M06.99	API	2
574821	M08.46	API	4
578056	D84.9 	GAS	1
578056	E88.9 	RHE	2
578056	I45.1 	RHE	4
578056	J45.9 	API	1
578056	K50.0 	API	2
578056	K50.0 	GAS	2
578056	K50.1 	GAS	1
578056	K50.9 	API	4
578056	K50.9 	GAS	28
578056	K56.6 	GAS	1
578056	K76.0 	GAS	1
578056	L40.0 	DER	57
578056	L40.0 	RHE	1
578056	L40.4 	DER	8
578056	L40.5 	DER	4
578056	L40.5 	ORT	3
578056	L40.5 	RHE	98
578056	L40.8 	DER	1
578056	L40.9 	DER	2
578056	L71.0 	DER	2
578056	M05.38	API	2
578056	M05.8 	RHE	2
578056	M05.80	RHE	55
578056	M05.90	RHE	73
578056	M05.99	RHE	1
578056	M06.0 	RHE	2
578056	M06.00	RHE	41
578056	M06.90	RHE	20
578056	M06.99	API	12
578056	M06.99	DIA	1
578056	M06.99	ORT	2
578056	M06.99	RHE	6
578056	M07.00	RHE	5
578056	M07.30	RHE	2
578056	M07.39	RHE	4
578056	M07.40	GAS	1
578056	M08.96	RHE	2
578056	M10.0 	ORT	1
578056	M17.0 	RHE	2
578056	M21.60	RHE	2
578056	M32.9 	RHE	2
578056	M35.3 	RHE	1
578056	M45.0 	RHE	5
578056	M45.00	ORT	1
578056	M45.00	RHE	59
578056	M45.09	RHE	13
578056	M46.1 	RHE	2
578056	M46.80	RHE	4
578056	M46.9 	RHE	2
578056	M80.4 	RHE	2
578056	S32.1 	RHE	2
578056	Z23.8 	RHE	1
578056	Z25.1 	RHE	1
578056	Z51.2 	RHE	2
578056	Z90.4 	RHE	2
578057	D64.9 	API	2
578057	D84.9 	GAS	10
578057	E03.9 	RHE	5
578057	E66.00	RHE	4
578057	I10.90	API	5
578057	K29.1 	GAS	2
578057	K29.3 	RHE	1
578057	K50.0 	API	112
578057	K50.0 	GAS	95
578057	K50.1 	API	1
578057	K50.1 	GAS	43
578057	K50.8 	API	8
578057	K50.88	API	1
578057	K50.9 	API	113
578057	K50.9 	GAS	70
578057	K50.9 	RHE	3
578057	K51.0 	API	3
578057	K51.9 	API	22
578057	K51.9 	GAS	13
578057	K52.9 	API	2
578057	K56.6 	GAS	7
578057	K60.3 	GAS	19
578057	K62.4 	API	2
578057	K74.3 	API	2
578057	K74.3 	RHE	1
578057	K74.6 	DER	2
578057	K76.0 	API	1
578057	K80.01	RHE	1
578057	L20.9 	DER	1
578057	L40.0 	DER	61
578057	L40.4 	DER	1
578057	L40.5 	API	8
578057	L40.5 	DER	43
578057	L40.5 	ORT	3
578057	L40.5 	RHE	211
578057	L40.8 	DER	22
578057	L40.8 	RHE	1
578057	L40.9 	API	4
578057	L40.9 	DER	34
578057	L93.0 	API	2
578057	L98.8 	API	1
578057	M05.00	RHE	1
578057	M05.8 	RHE	8
578057	M05.80	ORT	3
578057	M05.80	RHE	189
578057	M05.90	RHE	126
578057	M05.99	API	5
578057	M05.99	RHE	39
578057	M06.0 	ORT	1
578057	M06.0 	RHE	5
578057	M06.00	RHE	80
578057	M06.9 	API	18
578057	M06.9 	ORT	2
578057	M06.90	API	2
578057	M06.90	ORT	5
578057	M06.90	RHE	98
578057	M06.99	API	7
578057	M06.99	ORT	1
578057	M06.99	RHE	177
578057	M07.00	RHE	5
578057	M07.30	RHE	1
578057	M07.39	RHE	52
578057	M08.29	RHE	4
578057	M08.4 	RHE	2
578057	M08.45	PAE	3
578057	M08.96	RHE	3
578057	M13.0 	API	1
578057	M13.0 	ORT	2
578057	M13.8 	RHE	4
578057	M13.90	RHE	3
578057	M15.1 	RHE	1
578057	M20.5 	RHE	6
578057	M24.09	API	1
578057	M25.99	RHE	5
578057	M32.9 	RHE	3
578057	M35.0 	RHE	1
578057	M35.3 	RHE	1
578057	M45.0 	RHE	27
578057	M45.00	ORT	1
578057	M45.00	RHE	110
578057	M45.07	ORT	1
578057	M45.09	ORT	3
578057	M45.09	RHE	137
578057	M46.1 	ORT	1
578057	M46.1 	RHE	21
578057	M46.80	RHE	7
578057	M46.9 	RHE	17
578057	M54.16	API	1
578057	M54.17	ORT	3
578057	M65.8 	ORT	4
578057	M67.43	RHE	2
578057	M79.09	API	1
578057	M81.99	API	3
578057	M92.2 	RHE	1
578057	N18.82	RHE	2
578057	R52.2 	RHE	4
578057	R79.9 	GAS	1
578057	S32.1 	RHE	2
578057	Y57.9 	RHE	2
578057	Z12.9 	RHE	2
578057	Z23.8 	RHE	10
578057	Z51.2 	RHE	14
578057	Z99.3 	RHE	1
578300	L40.5 	API	3
578300	M06.99	API	10
578300	M79.09	API	1
578358	M13.0 	PAE	1
578435	L40.9 	DER	3
578435	M06.95	API	2
585519	L40.0 	DER	3
585519	M05.90	RHE	4
586484	L40.0 	DER	1
586484	L40.8 	DER	1
586484	M05.99	API	5
586484	M06.99	API	37
586484	M07.39	RHE	5
586484	M45.00	RHE	2
586484	M45.09	API	28
586484	M46.1 	API	1
586484	M46.1 	RHE	2
586612	D84.9 	GAS	2
586612	K50.9 	API	3
586612	K83.0 	GAS	5
586612	L40.5 	API	10
586612	L40.5 	RHE	3
586612	M45.09	API	4
586612	M45.09	RHE	10
586612	M46.1 	API	19
586668	K51.0 	API	2
586668	L40.5 	RHE	2
586668	M05.90	RHE	4
586668	M06.00	RHE	1
586668	M45.09	RHE	12
586668	M46.1 	RHE	2
588517	L40.5 	API	1
590385	M05.90	RHE	1
592112	M05.90	RHE	5
592112	M05.99	API	1
592113	M05.9 	API	2
592114	M08.45	PAE	1
595903	L40.5 	DER	18
595903	L40.5 	RHE	3
595903	L40.9 	DER	2
595903	M05.90	RHE	1
595903	M06.0 	RHE	2
595903	M06.00	RHE	1
595903	M06.90	RHE	1
595903	M45.09	RHE	1
595904	I10.90	RHE	3
595904	L40.0 	DER	1
595904	L40.9 	DER	3
595904	M80.90	ORT	6
595905	F45.9 	ORT	1
595905	L40.0 	DER	1
595905	L40.0 	RHE	1
595905	L40.5 	DER	2
595905	L40.5 	RHE	12
595905	L40.9 	RHE	1
595905	M05.99	RHE	2
595905	M06.9 	ORT	1
595905	M06.90	ORT	2
595905	M06.90	RHE	1
595905	M06.99	RHE	9
595905	M13.11	ORT	3
595905	M43.1 	ORT	5
595905	M46.1 	RHE	16
595905	M80.90	ORT	2
597797	M07.39	RHE	1
597802	L40.0 	DER	2
597802	L40.9 	API	1
597802	M05.80	RHE	1
597802	M45.09	RHE	1
597803	L40.0 	DER	4
600471	K50.1 	GAS	1
600471	L40.0 	DER	2
600471	L40.5 	DER	2
600471	L40.5 	RHE	1
600471	L40.9 	DER	1
600471	M05.90	RHE	2
600471	M45.00	RHE	1
600472	K50.0 	GAS	19
600472	K50.1 	GAS	5
600472	L40.0 	DER	14
600472	L40.5 	DER	2
600472	M05.30	API	5
600472	M05.9 	DER	1
600472	M05.90	RHE	11
600472	M45.00	RHE	7
600472	M45.09	API	1
600472	M45.09	RHE	2
602194	M45.09	API	2
602195	M05.90	RHE	2
607662	L40.0 	DER	1
607662	L40.8 	DER	1
607662	M05.80	ORT	10
607662	M05.80	RHE	1
607664	L28.0 	API	2
607664	L40.0 	DER	2
607664	L40.5 	RHE	2
607664	L40.9 	DER	2
607664	M05.09	RHE	2
607664	M05.89	RHE	13
607664	M05.9 	API	2
607664	M05.90	RHE	8
607664	M06.00	RHE	2
607664	M06.09	RHE	1
607664	M06.90	DIA	11
607664	M08.29	RHE	1
607664	M13.99	RHE	4
607664	M45.00	RHE	5
607664	M45.09	API	2
607664	M46.1 	RHE	3
607664	M79.09	RHE	5
609323	L40.0 	DER	1
609323	L40.5 	API	1
609323	M05.80	RHE	1
609323	M06.90	API	1
609323	M06.99	API	7
609323	M06.99	RHE	1
609323	M08.2 	PAE	3
609323	M45.09	API	9
609323	M45.09	RHE	3
609323	M54.19	ORT	1
610226	L40.0 	DER	15
610226	L40.5 	DER	2
610226	L40.5 	PAE	1
610226	L40.5 	RHE	2
610226	M05.90	RHE	10
610226	M07.39	RHE	2
610226	M08.99	PAE	3
610226	M45.00	RHE	3
612337	I80.1 	API	1
612337	M05.8 	API	2
612337	M05.90	RHE	5
612339	M06.00	RHE	2
612435	L40.5 	DER	1
612435	L40.8 	DER	3
612435	L40.9 	DER	3
612467	K50.8 	API	1
612467	K50.82	API	1
612467	L40.0 	DER	2
612467	L40.5 	API	3
612467	L40.5 	RHE	12
612467	M05.90	RHE	16
614046	L40.0 	DER	2
614046	M05.90	RHE	1
614046	M08.99	PAE	1
614046	M13.8 	RHE	2
614046	M48.06	API	4
617927	L40.0 	DER	3
621041	L40.0 	DER	5
621041	L40.8 	DER	6
621041	M05.90	RHE	2
621041	M06.00	API	1
621041	M06.00	RHE	2
621041	M06.99	API	3
621041	M07.39	RHE	1
621041	M13.99	API	1
621041	M45.09	DIA	2
621041	M46.90	RHE	2
621041	M47.89	API	1
621753	E66.00	API	1
621753	M05.90	RHE	5
624265	L40.5 	DER	4
624265	M05.9 	RHE	1
624265	M05.90	RHE	3
624265	M06.00	RHE	1
624265	M06.90	API	1
624265	M06.99	API	4
624265	M08.20	RHE	1
624265	M08.49	PAE	8
624265	M08.99	RHE	2
624265	M35.3 	API	6
624265	M46.90	RHE	1
624265	M79.01	API	1
624265	M79.09	API	1
624265	M81.99	API	2
624332	M05.8 	API	1
624332	M05.80	API	1
624333	E79.0 	ORT	4
624333	M05.90	RHE	3
624333	M06.00	RHE	3
624333	M06.99	API	2
624333	M46.90	RHE	1
624427	K50.0 	GAS	7
624427	L40.0 	DER	1
624427	L40.5 	API	1
624427	L40.5 	RHE	1
624427	M05.90	RHE	2
624427	M06.00	RHE	10
624427	M06.90	RHE	1
625464	M45.09	API	1
625464	M79.09	API	1
627305	D51.9 	API	6
627305	K50.1 	GAS	4
627305	K50.9 	API	4
627305	M02.9 	PAE	3
627305	M05.9 	RHE	8
627305	M05.90	RHE	1
627305	M06.00	RHE	5
627305	M06.9 	API	2
627305	M08.20	RHE	9
627305	M45.00	RHE	4
627305	M46.9 	RHE	2
630736	I83.9 	API	1
630736	I89.1 	DIA	2
630736	K50.9 	API	25
630736	K50.9 	GAS	7
630736	L40.0 	DER	2
630736	M02.3 	API	4
630736	M05.80	RHE	1
630736	M05.9 	RHE	4
630736	M06.00	RHE	1
630736	M06.90	API	2
630736	M07.3 	API	1
630736	M13.80	RHE	1
630736	M41.9 	DIA	1
630736	M45.09	API	3
630736	M45.09	RHE	6
630736	M79.09	API	1
639147	L40.5 	RHE	1
639147	M05.80	RHE	11
639147	M45.00	RHE	1
640890	L40.0 	API	1
643619	L40.0 	DER	2
643619	L40.5 	DER	3
643619	L40.5 	RHE	1
643619	M06.90	API	1
643619	M45.00	RHE	1
648115	L40.5 	RHE	1
648115	M06.00	RHE	1
648115	M08.29	RHE	2
648115	M45.00	RHE	7
658377	M06.99	ORT	1
659951	L40.0 	DER	1
659951	L40.9 	DER	1
659951	M05.84	API	1
664573	I80.3 	DER	2
664573	M05.90	RHE	1
664573	M13.9 	API	1
664573	M45.00	RHE	1
664573	M45.09	DIA	2
668189	G56.0 	RHE	1
668189	K50.0 	API	2
668189	K50.9 	GAS	18
668189	K51.0 	API	2
668189	K51.8 	GAS	3
668189	K51.9 	GAS	13
668189	L30.9 	DER	6
668189	L40.0 	DER	10
668189	L40.5 	RHE	2
668189	L40.8 	DER	3
668189	L50.9 	DER	2
668189	M05.80	RHE	3
668189	M05.90	RHE	12
668189	M06.00	RHE	5
668189	M07.00	RHE	2
668189	M07.39	RHE	4
668189	M45.00	RHE	12
668189	M45.07	RHE	1
668189	M46.80	RHE	1
668189	Z03.9 	RHE	1
668191	K50.0 	GAS	7
668191	K50.1 	GAS	3
668191	K50.9 	API	1
668191	K51.9 	API	1
668191	K51.9 	GAS	6
668191	L40.0 	DER	14
668191	L40.5 	RHE	1
668191	L40.9 	DER	5
668191	M05.80	RHE	7
668191	M06.99	RHE	1
668191	M07.00	RHE	1
668191	M45.00	RHE	14
668191	Z03.9 	RHE	3
668990	L40.0 	DER	5
668990	L40.5 	API	1
668990	L40.5 	RHE	16
668990	L40.8 	DER	1
668990	L40.9 	DER	2
668990	M05.80	RHE	13
668990	M05.90	RHE	2
668990	M05.99	RHE	7
668990	M06.0 	RHE	7
668990	M06.00	RHE	4
668990	M07.39	RHE	1
668990	M10.0 	ORT	1
668990	M45.00	RHE	2
668990	M45.09	RHE	23
668990	Z51.2 	RHE	1
668991	F32.1 	RHE	1
668991	K29.3 	RHE	1
668991	L40.0 	DER	6
668991	L40.5 	API	1
668991	L40.5 	DER	14
668991	L40.5 	RHE	35
668991	L40.8 	DER	2
668991	L40.9 	DER	1
668991	M05.80	RHE	29
668991	M05.90	RHE	5
668991	M05.99	API	3
668991	M05.99	RHE	11
668991	M06.00	RHE	13
668991	M06.99	API	1
668991	M06.99	RHE	13
668991	M07.30	RHE	1
668991	M18.0 	RHE	1
668991	M45.0 	RHE	9
668991	M45.00	ORT	1
668991	M45.00	RHE	6
668991	M45.09	API	6
668991	M45.09	RHE	10
668991	M46.80	RHE	1
668991	R52.2 	RHE	1
668991	Z51.2 	RHE	3
669039	K50.0 	API	3
669039	K50.1 	API	2
669039	K50.8 	API	5
669039	K50.82	API	6
669039	K51.0 	API	4
669039	L40.0 	DER	2
669039	M46.90	RHE	2
669039	M81.99	API	3
676381	L40.9 	DER	1
676384	K50.82	API	2
676384	L40.9 	DER	2
678063	K50.0 	GAS	1
679293	L40.5 	API	2
679293	L40.5 	RHE	1
679293	M06.00	RHE	1
679293	M45.00	RHE	1
679293	M45.09	API	3
679293	M45.09	RHE	1
679293	M79.00	API	1
679294	L40.5 	API	1
679294	L40.5 	RHE	4
679294	M05.90	RHE	3
679294	M06.99	ORT	1
679294	M06.99	RHE	2
679294	M45.00	RHE	6
679294	M46.1 	ORT	2
679296	L40.0 	RHE	4
679296	L40.5 	RHE	4
679296	M02.30	RHE	2
679296	M05.99	RHE	2
679296	M07.00	RHE	3
679296	M45.0 	RHE	1
679296	M45.00	RHE	7
679296	M46.1 	RHE	1
679296	Z51.2 	RHE	1
679298	M05.80	RHE	2
679298	M07.00	RHE	2
679298	M08.1 	RHE	1
679298	M45.00	RHE	4
679298	M45.09	API	1
679299	M06.00	RHE	9
679300	M05.80	RHE	6
679300	M05.99	API	1
679300	M05.99	RHE	2
679300	M06.00	RHE	8
679300	M06.99	API	2
679300	M06.99	RHE	5
682268	M05.90	RHE	2
682272	M05.9 	RHE	1
682770	M05.90	RHE	1
695323	K50.82	API	2

Open in new window

I need to generate the following coulmns from this dataset :

1 ProductCount   i.e the number of PackCount attributed to each product
2 DiagnosisCount  i.e the number of PackCount attributed to each Diagnosis
3 SpecialtyCount    i.e the number of PackCount attributed to each Specialty
4 Product by Diagosis - ie The number of PackCounts  that were prescribed of Product for each Diagonsis  e.g Number of Product "359126" for Diagnosis "E88.9".
5 Product By Specialty
6 Diagnosis by Specialty

And I need to display them as columns alongside the rows as shown above. All the required columns could not fit in , so thats why I explained each column data above

Any ideas of how I can do this using T-SQL or SSIS will be greatly apprecited

Thanks

marvo2010Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you can do like this, I showed the first 2 columns ...
the last 3 will have an additional condition in the subquery, but for the rest the same technique: correlated subquery.
select t.*
    , ( select count(*) from yourtable c where c.product = t.Product   ) product_count
     , ( select count(*) from yourtable c where c.diagnosis= t.diagnosis) DiagnosisCount  _count
   ... etc .
  from yourtable t

Open in new window

0
 
marvo2010Author Commented:
Hello angelIII,

I am bit here and there or how to complete this.

Pls can you throw more insight in the details pls .

Your help is deeply appreciated.

Thanks

0
 
marvo2010Author Commented:
The count to be done is by PackCount .Pls look at the dataset above . The column headings are a little justaposed. The numeric values belong to the PackCount Column.

Thanks

0
 
Scott PletcherSenior DBACommented:
Please give code below a try.  If dataset is not too large, hopefully that will do it.
;WITH
ProdTots AS (
    SELECT
        Product, SUM(PackCount) AS ProductCount
    FROM dataset
    GROUP BY
        Product
),
DiagProdTots AS (
    SELECT
	    Diagnosis, Product, SUM(PackCount) AS ProductByDiagnosis
    FROM dataset
    GROUP BY
	    Diagnosis, Product WITH ROLLUP
),
SpecProdTots AS (
    SELECT
	    Specialty, Product, SUM(PackCount) AS ProductBySpecialty
    FROM dbo.dataset
    GROUP BY
	    Specialty, Product WITH ROLLUP
),
SpecDiagTots AS (
    SELECT
	    Specialty, Diagnosis, SUM(PackCount) AS DiagnosisBySpecialty
    FROM dataset
    GROUP BY
	    Specialty, Diagnosis WITH ROLLUP
)
SELECT
    d.Product, d.Diagnosis, d.Specialty, d.PackCount,
    pt.ProductCount, dpt1.ProductByDiagnosis AS DiagnosisCount, spt1.ProductBySpecialty AS SpecialtyCount,
    dpt1.ProductByDiagnosis, spt1.ProductBySpecialty,
    sdt.DiagnosisBySpecialty
FROM dbo.dataset d
INNER JOIN ProdTots pt ON
    d.Product = pt.Product
INNER JOIN DiagProdTots dpt1 ON
    d.Diagnosis = dpt1.Diagnosis AND
    d.Product IS NULL
INNER JOIN SpecProdTots spt1 ON
    d.Specialty = spt1.Specialty AND
    d.Product IS NULL
INNER JOIN DiagProdTots dpt2 ON
    d.Diagnosis = dpt1.Diagnosis AND
    d.Product = dpt1.Product
INNER JOIN SpecProdTots spt2 ON
    d.Specialty = spt2.Specialty AND
    d.Product = spt2.Product
INNER JOIN SpecDiagTots sdt ON
    d.Specialty = sdt.Specialty AND
    d.Diagnosis = dst.Diagnosis

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.