We help IT Professionals succeed at work.

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

V B
V B asked
on
128 Views
Last Modified: 2017-03-19
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
Comment
Watch Question

Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
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 BSenior Analyst

Author

Commented:
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 BSenior Analyst

Author

Commented:
Stock Transaction File
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 VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
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 BSenior Analyst

Author

Commented:
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
Senior Manager
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
V BSenior Analyst

Author

Commented:
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 BSenior Analyst

Author

Commented:
Hi Nakul,

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

Regards
VB
Nakul VachhrajaniSenior Manager
CERTIFIED EXPERT

Commented:
Hello!

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

Author

Commented:
Apologies South Mod,

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

Regards
V
V BSenior Analyst

Author

Commented:
Best Solution: Works as expected

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.