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...
LVL 1
PhilippeRenaudAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
You need to decide if you wish to normalise your database. The sooner the better. Using a free-text field to determine location is not a good idea.

Now, to your problem. First of all you need to get the latest records for the given date range. Use this query:
declare @startDate datetime, @endDate datetime
select @startDate='20141108 00:00:00', @endDate='20141109 00:00:00'
with cte as (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ID, Group ORDER BY [Date] DESC) rn
From yourTable WHERE [Date] >= @startDate and [Date] < @endDate
)
SELECT * FROM cte WHERE rn = 1

Open in new window

This will give you a unique record for the latest location for the given date range.
Now, you need to massage the data in the description table. Let's enhance our query to get these values:
declare @startDate datetime, @endDate datetime
select @startDate='20141108 00:00:00', @endDate='20141109 00:00:00'
with cte as (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ID, Group ORDER BY [Date] DESC) rn
From yourTable WHERE [Date] >= @startDate and [Date] < @endDate
), 
fromTo AS (
SELECT *, 
        case when charindex('From: ', description)>0 THEN 
            case when charindex(' ', description, charindex('From: ', description)+6) > 0
                 then substring(description, charindex('From: ', description)+6, charindex(' ', description, charindex('From: ', description)+6) - charindex('From: ', description)-6)
            end
        end as FromLoc,
        case when charindex('TO: ', description)>0 
             then substring(description, charindex('TO: ', description)+4, 100)
        end as ToLoc
FROM cte WHERE rn = 1)
SELECT * from fromTo 

Open in new window

And now, all we need is to GROUP BY ToLoc column, like this:
declare @startDate datetime, @endDate datetime
select @startDate='20141108 00:00:00', @endDate='20141109 00:00:00'
with cte as (
SELECT *,
ROW_NUMBER() OVER(PARTITION BY ID, Group ORDER BY [Date] DESC) rn
From yourTable WHERE [Date] >= @startDate and [Date] < @endDate
), 
fromTo AS (
SELECT *, 
        case when charindex('From: ', description)>0 THEN 
            case when charindex(' ', description, charindex('From: ', description)+6) > 0
                 then substring(description, charindex('From: ', description)+6, charindex(' ', description, charindex('From: ', description)+6) - charindex('From: ', description)-6)
            end
        end as FromLoc,
        case when charindex('TO: ', description)>0 
             then substring(description, charindex('TO: ', description)+4, 100)
        end as ToLoc
FROM cte WHERE rn = 1)
SELECT ToLoc, Count(*) QTY from fromTo GROUP BY ToLoc

Open in new window

0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
Here's an alternative way to accomplish this. What it does is split each location into 2 columns into a temp table. After that, a union query selects first the "from" location with a quantity of -1, and then the "To" location with a quantity of +1. Assuming that your data also contains the original receiving records (for example, the unit moved from NA to EE01, how did it get to NA in the first place), the sum of the Qty_transaction column up to a given date, grouped by location, will give you the remaining quantity on each location:

--Table for test data
create table #movements
(	ID	int
,	[Group]	int
,	[Date] datetime
,	[Description]	nvarchar(100)
)

-- Insert test data
insert into #movements
Values	(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')

-- Create temp table to hold the split locations
Create table #Loc_from_to
(	ID	int
,	ID_group	int
,	Transaction_date	datetime
,	Location_from	nvarchar(12)
,	Location_to	nvarchar(12)
)

-- Insert split location info
insert into #loc_from_to

select		ID
		,	[GROUP]
		,	[DATE]
		,	LTRIM(RTRIM(substring(m.[Description], CHARINDEX(':', m.[Description]) + 1,(CHARINDEX('TO:', m.[Description]) - 1) - CHARINDEX(':', m.[Description]))))
		,	LTRIM(RTRIM(substring(m.[Description], CHARINDEX('TO:', m.[Description]) + 3,LEN(m.[Description]) - CHARINDEX('TO:', m.[Description]))))
from	#movements m;

-- Select transaction sum per location

Select		Location
		,	sum(Qty_transaction) as 'Qty'
from	(
		Select		Transaction_date as 'Transaction_date'
				,	Location_from as 'Location'
				,	-1 as 'Qty_transaction'	
		from	#Loc_from_to

		union all
				
		Select		Transaction_date
				,	Location_to
				,	1 as 'Qty_transaction'
		from	#Loc_from_to
		) as a
--where	a.Transaction_date <= '2014-11-09'
group by 	Location
having SUM(Qty_transaction) > 0

drop table #movements
drop table #Loc_from_to

Open in new window


I've added the HAVING clause because like I said, there are no records that show how the stock arrived in location "NA" in the first place. Unless NA means there was no location and that's actually your receiving location. If that's the case you can modify the statement like this:

--Table for test data
create table #movements
(	ID	int
,	[Group]	int
,	[Date] datetime
,	[Description]	nvarchar(100)
)

-- Insert test data
insert into #movements
Values	(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')

-- Create temp table to hold the split locations
Create table #Loc_from_to
(	ID	int
,	ID_group	int
,	Transaction_date	datetime
,	Location_from	nvarchar(12)
,	Location_to	nvarchar(12)
)

-- Insert split location info
insert into #loc_from_to

select		ID
		,	[GROUP]
		,	[DATE]
		,	LTRIM(RTRIM(substring(m.[Description], CHARINDEX(':', m.[Description]) + 1,(CHARINDEX('TO:', m.[Description]) - 1) - CHARINDEX(':', m.[Description]))))
		,	LTRIM(RTRIM(substring(m.[Description], CHARINDEX('TO:', m.[Description]) + 3,LEN(m.[Description]) - CHARINDEX('TO:', m.[Description]))))
from	#movements m;

-- Select transaction sum per location

Select		Location
		,	sum(Qty_transaction) as 'Qty'
from	(
		Select		Transaction_date as 'Transaction_date'
				,	Location_from as 'Location'
				,	-1 as 'Qty_transaction'	
		from	#Loc_from_to
		where	Location_from <> 'NA'

		union all
				
		Select		Transaction_date
				,	Location_to
				,	1 as 'Qty_transaction'
		from	#Loc_from_to
		) as a
--where	a.Transaction_date <= '2014-11-09'
group by 	Location

drop table #movements
drop table #Loc_from_to

Open in new window


You can use the "where a.transaction_date <= somedate" to check historical stock positions.
The benefit of this method is that it also gives you an error checking mechanism. If you find quantities which are smaller than 0, you know you're missing records from your data (quantity went out of the location, but never went in).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PhilippeRenaudAuthor Commented:
Thanks for the help,  It seems to me Koen version is a better clearer.. but i will give some points to both.

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.
0
Koen Van WielinkBusiness Intelligence SpecialistCommented:
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.
0
PhilippeRenaudAuthor Commented:
thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.