I have a Customer table with dataset like below:

I have a Customer table with dataset like below:

I need to calculate the new column (Adj_Value) based on OrderDate for the customer.

case 1) when OrderDate is different for a customer, Adj_Value is same as Orig_Value Eg: CustID = A (in above data set)

case 2) when OrderDate is same for a customer in a group of records, Adj_Value has to be calculated based on OrderType = Bulk, when Orig_Value is not null for OrderType = Bulk Eg: CustID = B (in above data set)

case 3) when OrderDate is same for a customer in a group of records, Adj_Value has to be calculated based on OrderType = Individual, when Orig_Value is null for OrderType = Bulk Eg: CustID = C (in above data set)

Note: OrderType = "Bulk" gets preference when it is not null then "Individual" then "Other".

Here is the expected output with new filed - "Adj_Value":

I have tried with sql window functions, but it's not giving me the expected result:

I am not able to add the sql query here: I have attached the screenshot of the query. Please check here

Please check screenshot of SQL query here

• sql
• snowflake-cloud-data-platform
• case
• ranking

• Is there anything stopping multiple records for a single customer-orderdate-ordertype? Could there be two 'bulk' records for customer B? You don't show any in your examples, but if so, I wonder how you choose the "right" one for Orig_Value? – Chris Maurer Commented Mar 30 at 17:05
• @ChrisMaurer, customer-orderdate-ordertype record will be unique. There will be only one Orig_Value for each Ordertype. – serena55 Commented Mar 30 at 17:22
• Orig_Value is null for OrderType = Bulk Eg: CustID = C (in above data set): your data doesn't have any null values, Orig_Value is zero for custID:C – tinazmu Commented Mar 30 at 23:14

3 Answers 3

You can calculate by window function values bulk_orig_value, individual_orig_value, other_orig_value
with (partition by CustID, OrderDate).

Then use coalesce with rule OrderType = "Bulk" gets preference when it is not null then "Individual" then "Other"
as coalesce(Bulk_Orig_Value, Individual_Orig_value, Other_Orig_Value) Adj_Value

To ignore 0 values use nullif(Orig_Value,0) - null if Orig_Value=0.

select  custid,orderdate,ordertype,orig_value
  ,coalesce(Bulk_orig_Value,Individual_Orig_value,Other_Orig_Value) Adj_Value
from(
select *
  ,max(case when OrderType='Bulk' then nullif(Orig_Value,0) end)
       over(partition by CustID, OrderDate) Bulk_Orig_Value
  ,max(case when OrderType='Individual' then nullif(Orig_Value,0) end)
       over(partition by CustID, OrderDate) Individual_Orig_Value
  ,max(case when OrderType not in('Bulk','Individual') then nullif(Orig_Value,0) end)
       over(partition by CustID, OrderDate) Other_Orig_Value
from customer
)a

fiddle

With test data as

Just use the min/max window aggregate functions.

select
 MyTbl.*,
 case when min(OrderDate) over (partition by CustID) 
           <> max(OrderDate) over (partition by CustID) then Orig_Value 
      else coalesce( 
         min(case when OrderType='Bulk' then nullif(Orig_Value,0) end) over (partition by CustId), 
         min(case when OrderType='Individual' then nullif(Orig_Value,0) end) over (partition by CustId), 
         min(case when OrderType='Other' then nullif(Orig_Value,0) end) over (partition by CustId)) 
       end as adj_value
from MyTbl 

Your data doesn't have any null values, Orig_Value is zero for custID:C; so I assumed you meant zeroes to be treated as null?

edit to fix case D to be correct..

much like others answers, but showing the working deconstructed, and using NULLIFZERO to be more explicit

using a CTE just for the example data.

with data(custid, orderdate, ordertype, orig_value) as (
    select * from values
    ('A', '2025-01-01'::date,'Bulk', 10),
    ('A', '2025-01-02'::date,'Individual', 20),
    ('B', '2025-01-03'::date,'Bulk', 30),
    ('B', '2025-01-03'::date,'Individual', 10),
    ('C', '2025-01-04'::date,'Bulk', 0),
    ('C', '2025-01-04'::date,'Individual', 5),
    ('C', '2025-01-04'::date,'Other', 8),
    ('D', '2025-01-03'::date,'Bulk', 30),
    ('D', '2025-01-03'::date,'Individual', 40)
)
select 
    max(iff(ordertype='Bulk', orig_value,null)) over (partition by custid, orderdate) as p1,
    max(iff(ordertype='Individual', orig_value,null)) over (partition by custid, orderdate) as p2,
    nullifzero(p1) as np1,
    nvl(np1,p2) as adj_value,
    *
from data
order by custid, orderdate, ordertype

gives:

and can then be compressed to:

select 
    *,
    nvl(
        nullifzero(max(iff(ordertype='Bulk', orig_value,null)) over (partition by custid, orderdate)),
        max(iff(ordertype='Individual', orig_value,null)) over (partition by custid, orderdate)
    ) as adj_value,
from data
order by custid, orderdate, ordertype