SQL Server - Query logic

Question

Note the result below:

This is the result of the following query:

select *,
isnull(h,isnull(f,isnull(d,isnull(b,isnull(a,0))))) as y1,
isnull(h,isnull(g,isnull(f,isnull(e,isnull(d,isnull(c,isnull(c,0))))))) as y2 from valor

---

My difficulty is: whenever y2 present a value of a column, y1 should always display the previous one, as is happening in line 2 of the above image.

But looking at line 1 is a case, line 3 another case and wrong cases than I wish to do.

Will anyone help me to assemble this query?

Answer 1

First, the COALESCE is your friend. Use it. With it, your query is simplified for this:

SELECT *, COALESCE(h, f, d, b, a, 0) AS y1, COALESCE(h, g, f, e, d, c, c, 0) AS y2
FROM valor

Note that there is still something wrong. No y1, there are no fields g, e or c. On the field y2, there is the b and the a, and the c appears twice.

The COALESCE simplifies your problem and provides you with the y2 promptly, but not yet resolved to the y1. To structure CASE can help you by identifying the first non-zero field and then bringing the previous field.

The result is this:

SELECT
    *,
    CASE
        WHEN h IS NOT NULL THEN g
        WHEN g IS NOT NULL THEN f
        WHEN f IS NOT NULL THEN e
        WHEN e IS NOT NULL THEN d
        WHEN d IS NOT NULL THEN c
        WHEN c IS NOT NULL THEN b
        WHEN b IS NOT NULL THEN a
        ELSE 0
    END AS y1,
    COALESCE(h, g, f, e, d, c, b, a, 0) AS y2
FROM valor

Answer 2

Victor Stafusa, was show his response! I no longer remember the function COALESCE.

But yesterday, just after posting this doubt, I had managed to solve the problem in a different way than yours, but with the same logic and reasoning using the function IIF in place of CASE. Getting the query like this:

select *,

iif(h is not null, g, iif(g is not null, f, iif(f is not null, e, iif(e is not null, d, iif(d is not null, c, iif(c is not null, b, iif(b is not null, a, '0'))))))) as y1,
isnull(h,isnull(g,isnull(f,isnull(e,isnull(d,isnull(c,isnull(b,0))))))) as y2
from valor

Now I can put the best of both.

Answer 3

By my answer, you may already suspect that you have a database architecture problem...

WITH q (valor, ordem, id) AS
(
    SELECT a valor, 1 ordem, id
    FROM tabela
    WHERE a IS NOT NULL

    UNION

    SELECT b, 2, id
    FROM tabela
    WHERE b IS NOT NULL

    UNION

    ....

    UNION

    SELECT h, 8, id
    FROM tabela
    WHERE h IS NOT NULL
)
SELECT valor, id, ordem
FROM q
JOIN (SELECT id, MAX(valor) maximo FROM q GROUP by id) r
    ON (q.valor = r.maximo OR q.valor = (r.maximo - 1))
    AND q.id = r.id
ORDER BY id, ordem DESC