Assignment has two requirements:1. Inventory_storage code returns totals for the month. I want to return the inventory on last of the month instead of the cumulative for the month. So for example, if each day of the month has 10 million inventory, then the result shows up as 300 million. (10 for each day of the month). I want to return the value for the last day of the month instead.2. I want to join the two queries in distributed_storage and Inventory_storage together.Note: The two queries have the exact same columns: date, dry_lbs, produce_lbs, frozen_lbs, ref_lbs, che_lbs, real_dry_lbs, and total_lbs.Data is coming from redshift, so I have to use postgresql
WITH ledger_rows as ( SELECT CASE WHEN {{ overallDateTypeSelect.value }} = 'Day' THEN Date_trunc('day', date)::date WHEN {{ overallDateTypeSelect.value }} = 'Week' THEN Date_trunc('week', date)::date WHEN {{ overallDateTypeSelect.value }} = 'Month' THEN Date_trunc('month', date)::date END AS date, storage_code, -- disaster_tracking, CASE WHEN {{ distributedMeasureSelect.value }} = 'Pounds' THEN (gross_weight*-1) WHEN {{ distributedMeasureSelect.value }} = 'Meals' THEN (gross_weight*-1) / 1.2 END as gross_weight FROM reporting.warehouse_master_ledger WHERE action_type = 'Shipped' AND agency_type != 'PDO' AND agency_type != 'Other Food Bank' AND date BETWEEN {{ moment(overallDatePicker.startValue).format("YYYY-MM-DD") }} AND {{ moment(overallDatePicker.endValue).format("YYYY-MM-DD") }} ) SELECT date, cast(ROUND(SUM(CASE WHEN storage_code = 'DRY' THEN gross_weight ELSE 0 END),0) AS INT) AS dry_lbs, ROUND(SUM(CASE WHEN storage_code = 'PRODUCE' THEN gross_weight ELSE 0 END),0) AS produce_lbs, ROUND(SUM(CASE WHEN storage_code = 'FROZEN' THEN gross_weight ELSE 0 END),0) AS frozen_lbs, ROUND(SUM(CASE WHEN storage_code = 'REF' THEN gross_weight ELSE 0 END),0) AS ref_lbs, ROUND(SUM(CASE WHEN storage_code = 'CHEM' THEN gross_weight ELSE 0 END),0) AS chem_lbs, (dry_lbs + chem_lbs) as real_dry_lbs, ROUND(SUM(gross_weight), 0) AS total_lbs FROM ledger_rows GROUP BY date ORDER BY date ASC; SELECT x.date1, CAST(ROUND(SUM(CASE WHEN storage_code = 'DRY' THEN lbs_on_hand ELSE 0 END),0) AS INT) AS dry_lbs, ROUND(SUM(CASE WHEN storage_code = 'FROZEN' THEN lbs_on_hand ELSE 0 END),0) AS frozen_lbs, ROUND(SUM(CASE WHEN storage_code = 'REF' THEN lbs_on_hand ELSE 0 END),0) AS ref_lbs, ROUND(SUM(CASE WHEN storage_code = 'PRODUCE' THEN lbs_on_hand ELSE 0 END),0) AS produce_lbs, ROUND(SUM(CASE WHEN storage_code = 'CHEM' THEN lbs_on_hand ELSE 0 END),0) AS cem_lbs, ROUND(SUM(lbs_on_hand), 0) AS total_lbs ,(dry_lbs + frozen_lbs + ref_lbs + produce_lbs + cem_lbs) as total_lbs ,(dry_lbs + cem_lbs) as real_dry_lbs FROM ( SELECT CASE WHEN {{ overallDateTypeSelect.value }} = 'Day' THEN Date_trunc('day', date)::date WHEN {{ overallDateTypeSelect.value }} = 'Week' THEN Date_trunc('week', date)::date WHEN {{ overallDateTypeSelect.value }} = 'Month' THEN Date_trunc('month', date)::date WHEN {{ overallDateTypeSelect.value }} = 'Quarter' THEN Date_trunc('quarter', date)::date end as date1, * FROM reporting.warehouse_inventory_on_hand_by_storage WHERE date BETWEEN {{ moment(overallDatePicker.startValue).format("YYYY-MM-DD") }} AND {{ moment(overallDatePicker.endValue).format("YYYY-MM-DD") }} ) x GROUP BY x.date1 --SELECT * FROM day_norm ORDER BY x.date1 ASC;