I'm not too great with SQL, so looking for a little guidance on how to achieve the following through a stored procedure (or procedures).
I have 2 tables, Headers and Lines.
Cutdown schemas as follows:
HeaderNumber, HeaderSuffix, HeaderLine, Complete
HeaderNumber, HeaderSuffix, Line, Location, Complete
The data is populated as such that you have one header and multiple lines. The reason for the HeaderLine, is that in certain circumstances, certain lines, need to be handled outside of the main group of lines, before the full list can be worked with.
So for example, one line has Location = 'EXTERNAL', which is telling me stock is not in the current warehouse, so it needs to be handled as a single line, to get stock to the warehouse, then the line will be updated, and the LineHeader will be closed. In other words, the master header is locked, until all interim processes have been completed and once they're completed, the Master can be released.
So what I'm trying to do is as follows:
-- Stage 1 Get a list of lines, whose location was passed in. Select * from [Lines] where Location=@Location -- Stage 2 Using the HeaderNumber, HeaderSuffix, Line from stage 1 Mark headers as complete Update [Header] setting Complete=2 where Number, Suffix and Line match. -- Stage 3 Using the HeaderNumber, HeaderSuffix, Line from stage 1 Update [Line] setting Location=@NewLocation -- Stage 4 Check if any other line headers exist for the Header Number returned and if not -- update the master headers so they can be used. Select max(HeaderLine) from [Header] where Header/Suffix/Line and Complete=0 match returned in Stage 1 and if returns Null or 0 then update Complete = 0 on Header with Line=0
I apologise that this is a little long winded, but I wasn't sure how else to describe it.
Any pointers would be greatly appreciated.