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...

1 answer below »
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;
Answered Same DayJul 17, 2022

Answer To: Assignment has two requirements:1. Inventory_storage code returns totals for the month. I want to...

Jahir Abbas answered on Jul 17 2022
69 Votes
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...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here