Dynamically Updating Columns with new Data

I am handling an SQL table with over 10K+ Values, essentially it controls updating the status of a production station over the day. Currently the SQL server will report a new message at the current time stamp – ergo a new entry can be generated for the same part hundreds of times a day whilst only having the column “Production_Status” and “TimeStamp” changed. I want to create a new table that selects unique part names then have two other columns that control bringing up the LATEST entry for THAT part.

I have currently selected the data – reordered it so the latest timestamp is first on the list. I am currently trying to do this dynamic table but I am new to sql.

select dateTimeStamp,partNumber,lineStatus 
from tblPLCData 
where lineStatus like '_ Zone %' or lineStatus = 'Production'
order by dateTimeStamp desc;

The Expected results should be a NewTable with the row count being based off how many parts are in our total production facility – this column will be static – then two other columns that will check Originaltable for the latest status and timestamp and update the two other columns in the newTable.

I don’t need help with the table creation but more the logic that surrounds the updating of rows based off of another table.

Much Appreciated.

Answer

It looks like you could take advantage of a sub join that finds the MAX statusDate for each partNumber, then joins back to itself so that you can get the corresponding lineStatus value that corresponds to the record with the max date. I just have you inserting/updating a temp table but this can be the general approach you could take.

-- New table that might already exist in your db, I am creating one here
declare @NewTable(
    partNumber int,
    lineStatus varchar(max),
    last_update datetime
)

-- To initially set up your table or to update your table later with new part numbers that were not added before
insert into @NewTable
select tpd.partNumber, tpd.lineStatus, tpd.lineStatusdate
from tblPLCData tpd
join (
    select partNumber, MAX(lineStatusdate) lineStatusDateMax
    from tblPLCData
    group by partNumber
) maxStatusDate on tpd.partNumber = maxStatusDate.partNumber
    and tpd.lineStatusdate = maxStatusDate.lineStatusDateMax
left join @NewTable nt on tbd.partNumber = nt.partNumber
where tpd.lineStatus like '_ Zone %' or tpd.lineStatus = 'Production' and nt.partNumber is null

-- To update your table whenever you deem it necessary to refresh it.  I try to avoid triggers in my dbs
update nt set nt.lineStatus = tpd.lineStatus, nt.lineStatusdate = tpd.lineStatusDate
from tblPLCData tpd
join (
    select partNumber, MAX(lineStatusdate) lineStatusDateMax
    from tblPLCData
    group by partNumber
) maxStatusDate on tpd.partNumber = maxStatusDate.partNumber
    and tpd.lineStatusdate = maxStatusDate.lineStatusDateMax
join @NewTable nt on tbd.partNumber = nt.partNumber
where tpd.lineStatus like '_ Zone %' or tpd.lineStatus = 'Production'