Can’t figure out how to get duplicate values out of table in SQL redshift

I am trying to return the foretasted value per item, per warehouse, per day and then add them up for the week. I am pulling from two tables depending on the demand date, but the issue is that both tables have a “creation_date” column with timestamps, so it’s creating multiple raw_forecast entries per warehouse/item/day when I only want one. I tried to join on the creation dates, but because each table has different timestamps on the creation dates, SQL is returning both forecast quantities of that day. I just want whatever the largest forecast amount was for the day. Any help is so appreciated!

output columns: demand_date, item, fulfillment center, type quantity, raw_forecasts

there are multiple quantities and raw_forecast rows

SELECT
    DISTINCT d.demand_date,
    d.item,
    r.fulfillment_center_external_id,
    d.type,
    d.quantity,
    CASE WHEN d.type IN ('RAW') THEN MAX(DISTINCT d.quantity) ELSE 0 END as Raw_Forecast
FROM
    f3_rsc.fab_reporting_demand_forecasts d
Left join f3_rsc.runs r on d.output_id = r.output_id
    and TRUNC(d.creation_date) = TRUNC(r.creation_date)
where
    1 = 1
    and d.demand_date between to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD') + 11
    and to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD') + 17
    and d.type in ('RAW')
    and requester_id = 'SWF-PRODUCTION'
    and po_placement_status = 'SHOULD_CUT_PO'
    and TRUNC(d.creation_date) > to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD') -3
GROUp BY
    1,2,3,4,5

Answer

You are getting multiple rows because you are grouping on quantity and the quantities are different. Based on your description stop grouping on quantity (5 in your group by list) and take the MAX() of quantity in your select line. (You also don’t need DISTINCT if the column is in the group by list.)

SELECT
    d.demand_date,
    d.item,
    r.fulfillment_center_external_id,
    d.type,
    MAX(d.quantity),
    CASE WHEN d.type IN ('RAW') THEN MAX(DISTINCT d.quantity) ELSE 0 END as Raw_Forecast
FROM
    f3_rsc.fab_reporting_demand_forecasts d
Left join f3_rsc.runs r on d.output_id = r.output_id
    and TRUNC(d.creation_date) = TRUNC(r.creation_date)
where
    1 = 1
    and d.demand_date between to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD') + 11
    and to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD') + 17
    and d.type in ('RAW')
    and requester_id = 'SWF-PRODUCTION'
    and po_placement_status = 'SHOULD_CUT_PO'
    and TRUNC(d.creation_date) > to_date('{RUN_DATE_YYYY-MM-DD}', 'YYYY-MM-DD') -3
GROUp BY
    1,2,3,4

Let me know if I have misread your situation.