SELECT b.[count] FROM b WHERE b.timestamp > a.timestamp For Each Unique Value?-CodePudding

I have two tables: one with records of manual inventory counts, the other with inventory receiving counts.

Inventory Count Table:

timestamp	item_id	units_counted
23/06/22	352465	137
27/06/22	761697	65
29/06/22	352465	102

Inventory Received Table:

timestamp	item_id	units_received
24/06/22	352465	60
26/06/22	352465	72
28/06/22	352465	24
29/06/22	761697	21
30/06/22	352465	96
02/07/22	352465	36

I am trying to create a view that will show the latest record for each item_id from the 'Count' table.

I am then trying to UNION these with records from the 'Received' table WHERE their timestamp is greater than the latest 'Count' record of the same item_id.

Desired Output:

timestamp	item_id	units
29/06/22	352465	102
30/06/22	352465	96
02/07/22	352465	36
27/06/22	761697	65
29/06/22	761697	21

At the moment, I've put together the following query:

SELECT
  a.[timestamp], a.sales_item_id, a.sales_item_name, a.unit_cost, a.unit_count, a.unit_cost_total, b.sales_subcat_name, b.sales_category_name, b.sales_department_name
FROM
  [DB].[dbo].[inventory_count] as a
  LEFT JOIN [DB].[dbo].[Inventory_Categorized] as b
  ON a.sales_item_id = b.sales_item_id
WHERE
  [timestamp] = (SELECT MAX([timestamp]) FROM inventory_count i WHERE i.sales_item_id = a.sales_item_id)

UNION ALL

SELECT
  a.[timestamp], a.sales_item_id, a.sales_item_name, a.unit_cost, a.units_received, a.unit_cost_total, b.sales_subcat_name, b.sales_category_name, b.sales_department_name
FROM
  inventory_count as c,
  inventory_received as a
  LEFT JOIN [DB].[dbo].[Inventory_Categorized] as b
  ON a.sales_item_id = b.sales_item_id
WHERE
  a.[timestamp] > (SELECT MAX([timestamp]) FROM inventory_count i WHERE i.sales_item_id = a.sales_item_id)

Which takes the single newest timestamp from count, rather than the newest for each item_id. I am hoping the community can point me in the right direction.

Thanks everyone.

CodePudding user response：

A little ugly, and I'm sure a more efficient way is possible, but pretty sure this would get you the results desired (might need extra handling for timestamps that match between your counts and received tables to resolve collisions):

select
    timestamp,
    item_id,
    units_received as units
from
    inventory_count
where
    (item_id, timestamo) in (
        select
            combined.item_id,
            max(combined.timestamp)
        from
            (
                select
                    item_id,
                    timestamp
                from
                    inventory_count
                union
                select
                    item_id,
                from
                    inventory_received
            ) combined
        group by
            combined.item_id
    )
union
select
    timestamp,
    item_id,
    units_received as units
from
    inventory_received
where
    (item_id, timestamp) in (
        select
            combined.item_id,
            max(combined.timestamp)
        from
            (
                select
                    item_id,
                    timestamp
                from
                    inventory_count
                union
                select
                    item_id,
                from
                    inventory_received
            ) combined
        group by
            combined.item_id
    )
    order by item_id, timestamp;

CodePudding user response：

I used the row_number() function to generate a row number for each timestamp per item and then filter the top 1. I put that query in a common table expression (CTE) we can reuse.

WITH inv_count AS (
    SELECT  ROW_NUMBER() OVER (PARTITION BY item_id ORDER BY [timestamp] desc) number, 
            [timestamp], item_id, units_counted
    FROM    inventory_count
)
SELECT  [timestamp], item_id, units_counted units
FROM    inv_count
WHERE   number = 1

UNION

SELECT  r.[timestamp], r.item_id, r.units_received units
FROM    inventory_received r
        inner join inv_count c on r.item_id = c.item_id
WHERE   r.[timestamp] > c.[timestamp]
        and c.number = 1

A detailed explanation about the ROW_NUMBER() function here

CodePudding user response：

Items from the table 'count' have the same id but different date and 'count number', you just need the latest one for each id.so, the key is:

SELECT *
FROM (
    SELECT timestamp, item_id, units_counted
    FROM `count`
    ORDER BY timestamp DESC
)
GROUP BY item_id

The code above will only left the latest time for each id and work in MySQL, not sure whether it will work in SQL Server.

CodePudding user response：

NOTE: I used the Oracle dialect of SQL, but I believe everything in the query is SQL Standard compliant, so it should work in most other dialects.

In the sample data below, I added a row to the first table (counts) with no corresponding receipts in the second table, and also a row to the second table (receipts) without a corresponding count in the first table. You didn't mention these cases - I handled them in the most logical way I could think of. You need to discuss with your users and decide how they should be handled.

timestamp is a keyword in many database products, it's not a good column name. I changed it to ts.

In the output I added a note column to show where each row is coming from. This is particularly important for item_id = 999999, at the end - it doesn't come with a "most recent count" as the first row. You may keep it in your output for development and testing purposes, and remove it before you put the query in production if you really don't need it.

Sample data:

create table inventory_count (ts, item_id, units_counted) as
  select to_date('23/06/22', 'dd/mm/rr'), 352465, 137 from dual union all
  select to_date('27/06/22', 'dd/mm/rr'), 761697,  65 from dual union all
  select to_date('29/06/22', 'dd/mm/rr'), 352465, 102 from dual union all
  select to_date('28/06/22', 'dd/mm/rr'), 888888, 300 from dual
;
create table inventory_received (ts, item_id, units_received) as
  select to_date('24/06/22', 'dd/mm/rr'), 352465, 60 from dual union all
  select to_date('26/06/22', 'dd/mm/rr'), 352465, 72 from dual union all
  select to_date('28/06/22', 'dd/mm/rr'), 352465, 24 from dual union all
  select to_date('29/06/22', 'dd/mm/rr'), 761697, 21 from dual union all
  select to_date('30/06/22', 'dd/mm/rr'), 352465, 96 from dual union all
  select to_date('02/07/22', 'dd/mm/rr'), 352465, 36 from dual union all
  select to_date('13/05/22', 'dd/mm/rr'), 999999, 40 from dual
;

Query and output (from the sample data posted above):

with
  recent_count (ts, item_id, units_counted) as (
    select max(ts), item_id,
           max(units_counted) keep (dense_rank last order by ts)
    from   inventory_count
    group  by item_id
  )
select ts, item_id, units_counted as units, 'Last count' as note
from   recent_count
union all
select ir.ts, ir.item_id, ir.units_received as units, 'Newly received' as note
from   inventory_received ir left outer join recent_count rc
                             on ir.item_id = rc.item_id
where  ir.ts > rc.ts or rc.item_id is null
order  by item_id, ts
;

TS           ITEM_ID      UNITS  NOTE          
--------  ----------  ---------  --------------
29/06/22      352465        102  Last count    
30/06/22      352465         96  Newly received
02/07/22      352465         36  Newly received
27/06/22      761697         65  Last count    
29/06/22      761697         21  Newly received
28/06/22      888888        300  Last count    
13/05/22      999999         40  Newly received