Retrieve transactions with different date ranges for each warehouse 1

[hubbsshaun]

Hi all,

I am attempting to write a query that will get me all of the transactions for a warehouse from the time it finished its year end inventory count until the actual fiscal year end. Because of staff resourcing issues we had to count/inventory each warehouse on a different day and so many of the counts/inventories ended up happening before the actual fiscal year end. Our financial department needs to know what the actuals were at year end.

I am hoping to be able to write some sort of query that would have a subquery that would grab the date the inventory finished and then use that to determine the date range of transactions for each of the warehouses.

I am also hoping to do this without a stored procedure or function, if possible.

Any help is greatly appreciated. I have not posted any example code so pseudo-code would be a great help as a reply. If you feel that you could assist better though with some actual code examples I can gladly provide.

Thanks.

Shaun

 

[RiverGuy]

Hard to say without seeing your tables, but if you have one one transaction table in addition to a table which stores your year end inventory counts, the pseudocode would be as follows:

SELECT t.*
FROM Transactions t
INNER JOIN InventoryCounts i
ON t.WarehouseID = i.WarehouseID
AND t.TransactionDate BETWEEN i.CountDate AND <End of Fiscal Year>