SQL Server Trying to get "approvers" from query not working

I have this table: InvoiceDisputesApprovalResult and it contains workflow. Its columns are: disputeId, approverEmail, status, approvalLevel and claimId

For example, claimid = 3423 and disputeId = 3123123 has this:

disputeId  |approverEmail           |status  |approvalLevel|claimId
3123123    |approver1@mailinator.com|waiting |1            |3423 
3123123    |approver2@mailinator.com|waiting |2            |3423 
3123123    |approver3@mailinator.com|waiting |3            |3423

So disputeId 3123123 has 3 approval levels.

DESIRED JSON OUTPUT: The desired result is to get this JSON (for claimId 3423 it should be)

{   "claimId":  3423 ,  "disputeId":    3123123 ,   "disputeType": "CREAR_NC",  "status": "OK",  "description" : "Aprobador",   "approvers": [  {   "mail": "approver1@mailinator.com", "approvalLevel":    1   },{ "mail": "approver2@mailinator.com", "approvalLevel":    2   },{ "mail": "approver3@mailinator.com", "approvalLevel":    3   }], "pvp":  null    ,"salePrice":   null    ,"saleMKUP":    null    ,"claimMKUP":   null    ,"mktope":  null    ,"mkcliete":    null    }

Note:

Values: "disputeType": "CREAR_NC",  "status": "OK",  "description" : "Aprobador", "pvp":    null    ,"salePrice":   null    ,"saleMKUP":    null    ,"claimMKUP":   null    ,"mktope":  null    ,"mkcliete":    null are harcoded. do you get me?

Another example is that exists disputeId = 221322

disputeId |approverEmail           |status  |approvalLevel|claimId
221322    |approver1@mailinator.com|waiting |1            |2323
221322    |approver2@mailinator.com|waiting |2            |2323

but in this case it has only 2 approvalLevels.

The desired result is to get this JSON (for claimId 2323 it should be)

{   "claimId":  2323,   "disputeId":    221322, "disputeType": "CREAR_NC",  "status": "OK",  "description" : "Aprobador",   "approvers": [  {   "mail": "approver1@mailinator.com", "approvalLevel":    1   },{ "mail": "approver2@mailinator.com", "approvalLevel":    2   }], "pvp":  null    ,"salePrice":   null    ,"saleMKUP":    null    ,"claimMKUP":   null    ,"mktope":  null    ,"mkcliete":    null    }

Again, values: "disputeType": "CREAR_NC", "status": "OK", "description" : "Aprobador", "pvp": null ,"salePrice": null ,"saleMKUP": null ,"claimMKUP": null ,"mktope": null ,"mkcliete": null are harcoded.

do you get me?

I tried something like this:

SELECT
    main.claimId,
    main.disputeId,
    'CREAR_NC' AS disputeType,
    'OK' AS status,
    'Aprobador' AS description,
    (
        SELECT
            '[' + STUFF(
                (
                    SELECT
                        ', ' + (
                            SELECT '{"mail": "' + sub.approverEmail + '", "approvalLevel": ' + CAST(sub.approvalLevel AS VARCHAR) + '}'
                            FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)')
                    FROM InvoiceDisputesApprovalResult AS sub
                    WHERE sub.claimId = main.claimId
                    and main.disputeId = 28258
                    FOR XML PATH('')
                ), 1, 2, ''
            ) + ']'
    ) AS approvers,
    NULL AS pvp,
    NULL AS salePrice,
    NULL AS saleMKUP,
    NULL AS claimMKUP,
    NULL AS mktope,
    NULL AS mkcliete
FROM InvoiceDisputesApprovalResult AS main
WHERE main.status = 'WAITING'
  AND main.disputeId = 28258 
GROUP BY main.claimId, main.disputeId;

The problem is I'm getting A LOT OF APPROVERS that do not belong to the given disputeId

claimId              disputeId            disputeType status description approvers                                                                                                                                                                                                                                                        pvp         salePrice   saleMKUP    claimMKUP   mktope      mkcliete

(1 row affected)

This is a better way to do it without the need of passing disputeId to the "STUFF" function :

SELECT
    main.claimId,
    main.disputeId,
    'CREAR_NC' AS disputeType,
    'OK' AS status,
    'Aprobador' AS description,
    (
        SELECT
            '[' + STUFF(
                (
                    SELECT
                        ', ' + (
                            SELECT '{"mail": "' + sub.approverEmail + '", "approvalLevel": ' + CAST(sub.approvalLevel AS VARCHAR) + '}'
                            FOR XML PATH(''), TYPE
                        ).value('.', 'VARCHAR(MAX)')
                    FROM InvoiceDisputesApprovalResult AS sub
                    WHERE sub.claimId = main.claimId 
                          and sub.disputeId = main.disputeId 
                    FOR XML PATH('')
                ), 1, 2, ''
            ) + ']'
    ) AS approvers,
    NULL AS pvp,
    NULL AS salePrice,
    NULL AS saleMKUP,
    NULL AS claimMKUP,
    NULL AS mktope,
    NULL AS mkcliete
FROM InvoiceDisputesApprovalResult AS main
WHERE main.status = 'waiting' 
      AND main.disputeId = 3123123 
GROUP BY main.claimId, main.disputeId;

Demo here

SQL Server Trying to get "approvers" from query not working

1条答案

相关问题

热门标签

最新问答