marvo2010
asked on
Calculating derived data from base data - SQL server
Hello I have a datasets provided :
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Thanks
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
ASKER
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