asked on

SQL Historical Inventory Query

Hello Experts,

I am trying to write a query to show the historical inventory for a time specified by the user. I have the following tables (tbl_header & tbl_details):

The link between the tables is tbl_header.id = tbl_details.id

From the above dataset, the user may request to show all items on inventory (tbl_details.process = storing) for the date 31/08/2015 (2015-08-31 23:59:59.999). This would need to only show items on "storing" at that time.

Any items that have been issued or consumed before that date need to be ignored. Any items received after that date need to be ignored. So, from the above dataset, the records that would be returned are:

Any assistance you can provide would be greatly appreciated. I have been trying to write the query without success.

Kind Regards,

John

Vikas Garg

Hi,

Hope this will help you

SELECT * FROM  TBL_HEADER
INNER JOIN 
TBL_DETAILS 
ON TBL_HEADER.ID = TBL_DETAILS.ID
WHERE tbl_details.process = 'storing'
AND tbl_details.DATE_TIME <= '2015-08-31 23:59:59.999'

Open in new window

Shaun Kline

Using Vikas' query as a base, tack on a NOT EXISTS to eliminate data that has been issued or consumed prior to the requested date/time:

SELECT * FROM  TBL_HEADER
INNER JOIN 
TBL_DETAILS 
ON TBL_HEADER.ID = TBL_DETAILS.ID
WHERE tbl_details.process = 'storing'
AND tbl_details.DATE_TIME <= '2015-08-31 23:59:59.999'
AND NOT EXISTS (SELECT 1 
    FROM TBL_DETAILS A
     WHERE A.ID = TBL_DETAILS.ID
              AND A.DATE_TIME <= '2015-08-31 23:59:59.999'
              AND A.Process IN ('issuing', 'consuming'))

Open in new window

john-formby

ASKER

Hello,

Thank you for the comments. I had to post sample data, but in reality, there can be many different "processes" and they do not necessarily follow a set order. Is it possible to change the last bit:

AND A.Process IN ('issuing', 'consuming'))

Open in new window

to say that if it is not storing then ignore, rather than listing all the processes to ignore?

Kind Regards,

John

Shaun Kline

Maybe something like:

SELECT * FROM  TBL_HEADER
INNER JOIN TBL_DETAILS ON TBL_HEADER.ID = TBL_DETAILS.ID
INNER JOIN (SELECT ID, MAX(DATE_TIME) MaxDate FROM TBL_DETAILS WHERE DATE_TIME <= '2015-08-31 23:59:59.999' GROUP BY ID) MaxDetail  ON TBL_DETAILS.ID = MaxDetail.ID
WHERE tbl_details.process = 'storing'
AND tbl_details.DATE_TIME <= '2015-08-31 23:59:59.999'
AND TBL_DETAILS.CheckDate = MaxDetail.MaxDate

Open in new window

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial