Help with SQL Query
Hello EE,
Lets say I have a table like this :
ID Group Date Description
1 8000 2014-11-08 17:00 From: NA TO: EE01
1 8000 2014-11-08 22:00 From: EE01 TO: EE04
2 8000 2014-11-08 17:00 From: NA TO: EE01
3 8000 2014-11-08 17:00 From: NA TO: EE01
3 8000 2014-11-10 13:10 From: EE01 TO: EE05
4 1234 2014-11-11 08:00 From: NA TO: EE01
7 1234 2014-11-11 08:00 From: NA TO: EE01
and so on ...
So just to explain, every ID is attached to a Group. So in group 8000 I have ID 1,2 and 3. in group 123 I got 4 and 7.
The Description column tells me a kind of history that the ID 3 started in Location NA then went to EE01 on the 11-08 at 17:00. Then it went to location EE05 on the 11-10 at 13:10pm...
What I would like as a qeury result is to see this:
Location QTY Units
NA 0
EE01 3
EE04 1
EE05 1
So basically.. by locaiton inside the description column.. what is the count of ID in each right now.
and even if its possible... If I give a specific date... I would see the QTY up to that date (to see in time how it was into those locations)
Thanks a lot for the help...
Lets say I have a table like this :
ID Group Date Description
1 8000 2014-11-08 17:00 From: NA TO: EE01
1 8000 2014-11-08 22:00 From: EE01 TO: EE04
2 8000 2014-11-08 17:00 From: NA TO: EE01
3 8000 2014-11-08 17:00 From: NA TO: EE01
3 8000 2014-11-10 13:10 From: EE01 TO: EE05
4 1234 2014-11-11 08:00 From: NA TO: EE01
7 1234 2014-11-11 08:00 From: NA TO: EE01
and so on ...
So just to explain, every ID is attached to a Group. So in group 8000 I have ID 1,2 and 3. in group 123 I got 4 and 7.
The Description column tells me a kind of history that the ID 3 started in Location NA then went to EE01 on the 11-08 at 17:00. Then it went to location EE05 on the 11-10 at 13:10pm...
What I would like as a qeury result is to see this:
Location QTY Units
NA 0
EE01 3
EE04 1
EE05 1
So basically.. by locaiton inside the description column.. what is the count of ID in each right now.
and even if its possible... If I give a specific date... I would see the QTY up to that date (to see in time how it was into those locations)
Thanks a lot for the help...
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi Phillipe,
This is not something I'd recommend. The idea behind this logic is that each location change is recorded as a separate transaction of 2 data rows, 1 minusing off the original location and a second one adding to the new location. So if you move from A to B and then on to C, you would first record a transaction from A to B, and then a separate transaction from B to C. Because the data works with datetime data, you can check at any point in time where your stock was.
If you don't have a from-to line, how do you know where your stock is? Sorry to say, but it sounds like a bit of an awkward system the way it's designed. I've never come across a system that tracks stock movement in a way that's so difficult to extract.
This is not something I'd recommend. The idea behind this logic is that each location change is recorded as a separate transaction of 2 data rows, 1 minusing off the original location and a second one adding to the new location. So if you move from A to B and then on to C, you would first record a transaction from A to B, and then a separate transaction from B to C. Because the data works with datetime data, you can check at any point in time where your stock was.
If you don't have a from-to line, how do you know where your stock is? Sorry to say, but it sounds like a bit of an awkward system the way it's designed. I've never come across a system that tracks stock movement in a way that's so difficult to extract.
ASKER
thanks
ASKER
But Koen, just one las tlittle thing... Lets say I have an extra column in your #Loc_from_to Table. lets say i called it: FinalLocation
so if in that table I have this :
ID Group Date From To FinalLocation
9 5555 2014-11-11 14:45 NA EE01 EE99
9 5555 2014-11-11 22:00 EE05 EE07 EE99
.....
etc....
What it means is that, sometimes... I will not have a line with a "From To" for the last and final location.
I would like to be able to -1 the latest location date for an ID the "TO" and to +1 the Location of the FinalLocation so that the inventory matches. I forgot that sometimes like I said.. I will not have a from to line so right now your sum(Qty_Transaction) do not match the real data...
you think its easy to fix.. ? that would be all after... thanks a lot.