asked on

SQL: Get Holding Day Based On Transfer Date And Return Date

Hello All,

Need a help on SQL.

I have an item ABC that was transferred from location A to Location B. The transfer from location A was on Jan 1 2017, and the receipt on location B was on Jan 15 2017.

Location B returned the same item on Jan 20 2017.

I need to run a sql where the from and To date on the SQL parameter will be Jan 1 2017 to Jan 31 2017.

I am running the sql based on all items which are not there in Location A but exist in Location B between Jan 1 and Jan 31. The problem is item ABC will never be returned in the sql result because Item B now exist in Location A.

My requirement is to return Item ABC as well, since this item was with location B for 20 days.

can someone help me with this please ?

Regards
VB

Nakul Vachhrajani

I'm quite sure it is, but is the transitions between the locations tracked in a permanent history/log?

Can you provide a table structure (scaled down version with only relevant columns and Id fields would be fine) and some sample data to work with? That would be extremely helpful. Thanks!

V B

ASKER

Hello Nakul,

yes, the transactions are tracked on a permanent log ( we do have a stock transaction history file ).

I will update the question with a screenshot of the table.

Thanks for looking into this

VB

V B

ASKER

Hi Nakul,

you can see that the transaction number 12345 was used to send item ABC-123 from Location A to Location B on 01 Jan 2017, and Location B received it on 15 Jan 2017.

Location B then send the same item back on order number 67890 on 20 Jan 2017 to Location A, and Location A received it on 22 Jan 2017

Hope this helps
VB

Nakul Vachhrajani

Ok, in this case, you basically need to simply check for any "Send" transactions that happened between your start (January 01, 2017) and end (January 31, 2017) range. If an item has been sent/dispatched from Location A, obviously it is not with location A for any given period of time.

That logic leads me to a query like the one provided below (of course, this demonstration is purely based on sample test table/data - the query will need to be adapted to your specific objects as part of your implementation).

Do let me know if it helps.

USE tempdb;
GO
--Test Scenario
DECLARE @holdingDayQuery TABLE (TransactionNumber INT         NOT NULL,
                                ItemNumber        VARCHAR(50) NOT NULL,
                                TransactionDate   DATE        NOT NULL,
                                OrderId           VARCHAR(50) NOT NULL,
                                OwnerLocation     VARCHAR(50) NOT NULL,
                                TransferType      VARCHAR(20) NOT NULL
                               );

--Test Data
INSERT INTO @holdingDayQuery (TransactionNumber, ItemNumber, TransactionDate, OrderId, OwnerLocation, TransferType)
VALUES (12345, 'ABC-123', '2017-01-01', '00145789', 'Location A', 'Send'),
       (12345, 'ABC-123', '2017-01-15', '00145789', 'Location B', 'Receipt'),
       (67890, 'ABC-123', '2017-01-20', '00198745', 'Location B', 'Send'),
       (67890, 'ABC-123', '2017-01-22', '00198745', 'Location A', 'Receipt'),
       (12346, 'ABC-124', '2017-01-17', '00145790', 'Location A', 'Send'),
       (12346, 'ABC-124', '2017-02-05', '00145790', 'Location C', 'Receipt');

--Input parameters
DECLARE @reportFromDate DATE = '2017-01-01';
DECLARE @reportToDate DATE = '2017-01-31';
DECLARE @sendingLocation VARCHAR(50) = 'Location A';
DECLARE @receivingLocation VARCHAR(50) = 'Location B';

--Query
SELECT sendingLocation.TransactionNumber,
       sendingLocation.ItemNumber,
       sendingLocation.TransactionDate,
       sendingLocation.OrderId,
       sendingLocation.OwnerLocation AS SendingLocation,
       receivingLocation.OwnerLocation AS DestinationLocation
FROM @holdingDayQuery AS sendingLocation
INNER JOIN @holdingDayQuery AS receivingLocation ON sendingLocation.TransactionNumber = receivingLocation.TransactionNumber
WHERE sendingLocation.TransferType = 'Send'
  AND sendingLocation.OwnerLocation = @sendingLocation
  AND receivingLocation.TransferType = 'Receipt'
  AND receivingLocation.OwnerLocation = @receivingLocation
  AND sendingLocation.TransactionDate BETWEEN @reportFromDate AND @reportToDate;

/* RESULTS
TransactionNumber ItemNumber  TransactionDate OrderId   SendingLocation  DestinationLocation 
----------------- ----------- --------------- --------- ---------------- --------------------
12345             ABC-123     2017-01-01      00145789  Location A       Location B
*/

Open in new window

V B

ASKER

Hi Nakul,

thanks for this, i just quickly tested this, looks like no matter what date we input in the reportfromdate and reporttodate param the transaction for item ABC-123 is always returned, for instance, i ran it for dates between 01 Jan 2017 and 10 Jan 2017, ideally the ABC-123 item should not show up in Location A since it was returned only on 22 Jan 2017.

Is there something i am missing ?

Regards
V

ASKER CERTIFIED SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

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

Start Free Trial

V B

ASKER

Hi Nakul,

thanks for this, i will be testing this in the next two days.

Will let you know the results here.

Regards
VB

V B

ASKER

Hi Nakul,

thank you for helping me out on this, this works as expected :)

Regards
VB

Nakul Vachhrajani

Hello!

Did my query help you in achieving the desired result? If yes, can you please mark the appropriate comment as the answer?

V B

ASKER

Apologies South Mod,

there was a mix up from my end on marking the right comment as solution

Regards
V

V B

ASKER

Best Solution: Works as expected