Solved

Help with SQL Query

Posted on 2014-11-11
5
226 Views
Last Modified: 2014-11-14
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...
0
Comment
Question by:PhilippeRenaud
  • 2
  • 2
5 Comments
 
LVL 24

Assisted Solution

by:chaau
chaau earned 100 total points
ID: 40436633
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
 
LVL 12

Accepted Solution

by:
Koen Van Wielink earned 400 total points
ID: 40436667
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
 
LVL 1

Author Comment

by:PhilippeRenaud
ID: 40437551
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
 
LVL 12

Expert Comment

by:Koen Van Wielink
ID: 40437656
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
 
LVL 1

Author Closing Comment

by:PhilippeRenaud
ID: 40442726
thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now