mercredi 9 septembre 2020

How to efficiently Produce Correct If/Else or CASE from Logic Grid Layout

How can I use the grid to come up with efficient concise code? If I don't use the grid are there better ways to approach conditional logic and am I using the correct terminology to describe my question?

When I'm presented with producing conditional logic output based on the values returned by multiple variables I often dive right in and begin coding however this is not the proper way to do it as far as I understand. When presented with complex logic paths how are the more experienced formerly trained people coming with the efficient conditional logic?

For example I have the below possible Status outcomes based on the values in the variables A, B, C, D. In my case this is a very simplified example of the request I am currently implementing. The status values are simply based on the request. For example to put the first row into words where all values = 1 it could read something like this "When the conditions A, B and C are TRUE... set the Status to 'T' if condition D is met otherwise set to 'A'".

Reading conditional logic in plain English can be painful and so I've compiled a grid laying out the possible conditions that produce the requested "Status".

enter image description here

How can I use the grid to come up with efficient concise code?

Below is my mock attempt but this is my simply diving right in which doesn't get me the most efficient route

WITH conditions AS
(
    SELECT 1 AS A, 1 AS B, 1 AS C, 1 AS D, 'T' AS desiredStatus UNION ALL
    SELECT 1, 1, 1, 0, 'A' UNION ALL
    SELECT 1, 1, 0, 0, 'A' UNION ALL
    SELECT 1, 0, 1, 1, 'H' UNION ALL
    SELECT 1, 0, 1, 0, 'H' UNION ALL
    SELECT 1, 0, 0, 0, 'A' UNION ALL
    SELECT 0, 1, 1, 1, 'H' UNION ALL
    SELECT 0, 1, 1, 0, 'H' UNION ALL
    SELECT 0, 0, 1, 1, 'H' UNION ALL
    SELECT 0, 0, 1, 0, 'H'
)
SELECT A, B, C, D, desiredStatus, 
   CASE WHEN A > 0 AND B > 0 THEN CASE WHEN C > 0 AND D > 0 THEN 'T' ELSE 'A' END 
        WHEN A > 0 AND B = 0 THEN 'H'
   END AS codedStatus
FROM conditions

Aucun commentaire:

Enregistrer un commentaire