clueless83 clueless83 - 2 days ago 4
SQL Question

SQL display more than one row based on value changing in another row

I was wondering if it's possible in SQL to only display one row then when a value in that row changes also display another row. For example, I have a Fees table. Fees can be added to an Order. When a fee that has already been added, has the fee quantity changed, the original Fee row is marked as deleted and a new Fee row added with the new quantity. When this amendment is done, the old quantity is marked as DeletedUnverified (StatusId 20) and the new fee quantity row goes in with a status of Paid (StatusId 7). A supervisor then has to verify these fee changes, so once they do that the status of the 'deletedUnverified' row will be changed to Deleted (StatusId 18) and the new fee quantity row will remain the same as Paid.

enter image description here

The image above shows the fees the first time they were changed (the top two rows) and after the supervisor has verified the changes (the bottom two rows). The two top rows are the data before it has been verified (so statusId = 20 is DeletedUnverified). Once the supervisor ok's it, the status changes to 18 (Deleted). What I'm trying to do is when a fee hasn't been verified, only show that one. So in the image above, the top rows, I only want to display row ID 111 (StatusId 20). Once the fee has been verified, I want to display both rows, i.e. the bottom two rows above.

I had code that showed the statusID 20 row and then when it was updated to statusID 18, it only showed the StatusId 7 row but I can't get what I want. I hope that made sense.

SELECT
*
FROM
FeeItem sub1
INNER JOIN (
SELECT
ISNULL(b.feeitemid, a.itemid) AS pharmacyformfeeitemid
FROM
(
SELECT
*
FROM
FormFeeItem
WHERE
OrderId IN (1234)
AND StatusId = 7
) a
LEFT JOIN (
SELECT
*
FROM
FormFeeItem
WHERE
OrderId IN (1234)
AND StatusId = 20
) b ON a.OrderId = b.OrderId
) sub2 ON sub1.FeeItemId = sub2.feeitemid


Any help is appreciated...thanks in advance :)

Answer
DECLARE @r AS TABLE
(
     ID INT
    ,StatusId INT
    ,OrderId INT
    ,Cost MONEY
    ,NoOfFees INT
)

INSERT INTO @r
VALUES
     (111, 20, 1234, -1.2, 3)
    ,(123, 7,  1234, 2.4, 6)

SELECT r1.*
FROM @r AS r1
LEFT JOIN @r AS r2
    ON r1.OrderId = r2.OrderId
    AND r2.StatusId = 18
WHERE (r1.StatusId = 7 AND r2.ID IS NOT NULL)
      OR
      r1.StatusId IN (18, 20)

DELETE @r

INSERT INTO @r
VALUES
     (111, 18, 1234, -1.2, 3)
    ,(123, 7,  1234, 2.4, 6)

SELECT r1.* 
FROM @r AS r1
LEFT JOIN @r AS r2
    ON r1.OrderId = r2.OrderId
    AND r2.StatusId = 18
WHERE (r1.StatusId = 7 AND r2.ID IS NOT NULL)
      OR
      r1.StatusId IN (18, 20)
Comments