Hi ,
Need help getting the sql right for the below logic .
I have a table which has Order # , Invoice # and last transaction date of each of these invoices. I'm trying to rank these invoices within an order to further process it for a different logic.
There are various scenarios .
Possible data scenarios - One Order can have multiple invoice or one Invoice or No Invoices or multiple records where some lines(say 5 lines) within an order have invoice #(say 3) while remaining 2 records have no invoice number.
The reason I'm checking Invoice count in below query is only if in scenarios where there is no invoice number across that order we need to force the Invoice_sequence to 0 or something else as the Dense_rank portion of the case is working correctly for orders with invoice.
count(distinct "Invoice #") Over(partition by Order # ) as "Invoice_count",
current logic -
Only -
DENSE_RANK() OVER(PARTITION BY Order# ORDER BY CASE WHEN NULLIF("Invoice Number",0) Is Not NULL Then TRY_TO_DATE(TO_VARCHAR("last txn date"),'YYYYMMDD') ELSE CAST('31-Dec-9999' AS DATE) END, "Invoice #" ASC)
end AS "Invoice_Seq".
I want something like this -
case when Invoice_count = 1 and nullif(InvoiceNumber,0) = 0 then 0
else
DENSE_RANK() OVER(PARTITION BY Order# ORDER BY CASE WHEN NULLIF("Invoice Number",0) Is Not NULL Then TRY_TO_DATE(TO_VARCHAR("last txn date"),'YYYYMMDD') ELSE CAST('31-Dec-9999' AS DATE) END, "Invoice #" ASC)
end AS "Invoice_Seq".
I'm taking the count of invoices within an Order beacuse there are orders with No invoice and no last transaction date. In this scenario The Invoice Sequence still ranks it as 1 in the current logic which is causing an issue in the next stage of processing.
In scenarios where Invoice and last transaction date is blank/null I want the invoice sequence to show as 0 so that it doesnt get picked for further processing . In every other scenario where Invoice count and Invoice number is not equal to 1 and 0 respectively
it shld process the else part of the above case.
The query above is giving me 'NULL' for an invoice which has an invoice count of 1 and invoice number as blank/Null .
Quick response is very much appreciated.
Thank you.