query based on a Matrix table

• yahoo80

  SSC Enthusiast

  Points: 172

  More actions

  July 31, 2019 at 5:07 pm

  #3667647

  Hey

  I'm  looking for a smart idea on how to populate a field Priority with values H/M/L based on Column 1 (Activity type) and criticality

  So e.g. for Activity type '207-recertify' and criticality 'F' i would populate 'M' in the Priority field. Similarly for '206-overhaul' and criticality 'H' i need to assign priority 'L'

  I could build a complicated "case query" but im hoping there is a better and faster solution...

  

• jcelko212 32090

  SSCrazy Eights

  Points: 9304

  More actions

  July 31, 2019 at 5:37 pm

  #3667656

  >> I'm looking for a smart idea on how to populate a field [sic: colums are not fields]]]] Priority with values H/M/L based on Column 1 (Activity type) and criticality <<

  Why did you fail to post DDL? You can do this with a simple Lookup table:

  CREATE TABLE Priority_Lookup

  (activity_type CHAR (???) NOT NULL,

  criticality_code CHAR(1) NOT NULL,

  priority_code CHAR(1) NOT NULL

  CHECK (priority_code IN ('L', 'M', 'H'),

  PRIMARY KEY ((activity_type, criticality_code)

  );

  >> I could build a complicated "CASE query" but I'm hoping there is a better and faster solution... <<

  Notice that I've assumed simple Lookup will work with this. However, CASE expressions can be nested and made very complicated. I'm assuming you don't need that. You can also put check constraints on the activity type and the criticality code. The advantage here is that you can get data by activity type or by criticality code for other reports. It's also more flexible since it's very easy to update a lookup table. Notice I haven't made any guesses about the speed; getting it correct is more important than getting it fast.

  Please post DDL and follow ANSI/ISO standards when asking for help. 

• drew.allen

  SSC Guru

  Points: 77013

  More actions

  July 31, 2019 at 6:39 pm

  #3667667

  yahoo80 wrote:

  Hey

  I'm  looking for a smart idea on how to populate a field Priority with values H/M/L based on Column 1 (Activity type) and criticality

  So e.g. for Activity type '207-recertify' and criticality 'F' i would populate 'M' in the Priority field. Similarly for '206-overhaul' and criticality 'H' i need to assign priority 'L'

  I could build a complicated "case query" but im hoping there is a better and faster solution...

  

  The problem here is that your table is not properly normalized.  It should have three columns: Activity_Type, Criticality, and Priority.  Probably the easiest way is to "UNPIVOT" your data.  I prefer the CROSS APPLY method.

  SELECT *
  FROM YourTable
  CROSS APPLY(VALUES(Activity_Type, 'A', [A,B,C,D]), (Activity_Type, 'B', [A,B,C,D]), ...) v(Activity_Type, Criticality, Priority)

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA