CODEWITHSUNDEEP
sqloracle-databasenetsuite
Andrew F
Andrew F

Reputation: 3

Netsuite nested case with different Transaction types

I am completely stumped by what I'm pretty sure is a nested CASE WHEN statement issue, in which I need it to reference transaction dates for one transaction Type while returning the Quantity from another for that resultant timeframe.

Here's the plain language version, running in a Transaction saved search:

  1. Find MIN 'Item Receipt' Type transaction date, within the last 90 days
  2. When Type is 'Sales Order', return the sum of the Quantity since that MIN Item Receipt date

Here's what I have so far, which I can at least get to run, but I know it's not exactly following the above logic, and aside from that it returns nonsense data regardless. And anytime I attempt to put in a MIN on any of the date calcs, the field outcome errors out.

----------

Sample Data:
| Type         |     Transaction Date    |     Name    |     Quantity    |
|--------------|:-----------------------:|-------------|:---------------:|
| Item Receipt |        2/25/2021        | Vendor   A  |        20       |
| Item Receipt |         2/5/2021        | Vendor   A  |        21       |
| Item Receipt |         2/3/2021        | Vendor   A  |        4        |
| Sales Order  |        3/18/2021        | Customer A  |        1        |
| Sales Order  |        3/13/2021        | Customer B  |        1        |
| Sales Order  |        3/13/2021        | Customer B  |        1        |
| Sales Order  |         3/2/2021        | Customer B  |        1        |

----------

Results line:

Field: Formula (Numeric), Summary Type: Sum

case when {type} = 'Item Receipt' AND {trandate} < ({today}-90) THEN (case when {type} = 'Sales Order' AND {trandate} > ({today}-90) then {quantity} else 0 end) else 1 end

Desired Outcome: 4 (the sum of all the Sales Order quantities since the earliest Item Receipt date of 2/3/2021)

Actual Outcome: 28 (this is coming from the last ELSE 1 bit, but I have no idea why it's summing to 28)

I feel like I'm either this close to figuring it out, or I'm so far off-base that I don't even have a clue....

Upvotes: 0

Views: 1904

Answers (1)

user3075978
user3075978

Reputation: 845

I don't quite understand what you are trying to do, but a single transaction can't be both transaction types. It is either an Item Receipt or a Sales Order.

Here is some sample SQL that might help:

CASE {type}
    WHEN 'Item Receipt' THEN
        CASE
            WHEN ({trandate} < ({today} - 90))
            THEN {quantity} 
            ELSE 0
        END
    WHEN 'Sales Order' THEN
        CASE 
            WHEN ({trandate} < ({today} - 90))
            THEN {quantity} 
            ELSE 0
        END
    ELSE 1
END

Upvotes: 0

Related Questions