July 18, 2007 at 9:29 am
hello i need help to resolve this problem in an efficient way (best avoiding cursors ) :
i have two tables ArticlesInStock and Orders which basically are :
[ArticlesInStock]
IdLog---IdArticle
101 --- Art1
102 --- Art1
103 --- Art2
104 --- Art2
105 --- Art2
106 --- Art2
[Orders]
IdOrder --- IdArticle --- IDDestination --- Quantity
201 --- Art1 --- 1 --- 1
202 --- Art2 --- 1 --- 1
203 --- Art2 --- 2 --- 2
and i need an output like :
IDLog ---IDOrder
101---201
103---202
104---203
105---203
any suggestion ? I think has something to do with running totals.
Tanks in advance
July 18, 2007 at 9:41 am
Where do the running totals come in?
J
July 18, 2007 at 11:15 am
Can you explain "in words" how you constructed the output?
IDLog --- IDOrder
101 --- 201
103 --- 202
104 --- 203
105 --- 203
????????????
* Noel
July 19, 2007 at 1:13 am
[ArticlesInStock]
IdLog---IdArticle
101 --- Art1
102 --- Art1
103 --- Art2
104 --- Art2
105 --- Art2
106 --- Art2
[Orders]
IdOrder --- IdArticle --- IDDestination --- Quantity
201 --- Art1 --- 1 --- 1
202 --- Art2 --- 1 --- 1
203 --- Art2 --- 2 --- 2
and i need an output like :
IDLog --- IDOrder
101 --- 201
103 --- 202
104 --- 203
105 --- 203
Since his tables are not rationalised/normalised, I presume he needs to retrieve Orders.OrderID and the "next available" ArticlesinStock.IDLog value using ArticlesinStock.IDArticle as a JOIN on Orders.IDArticle.
One way I can see it can be done is by having a FLAG column on ArticlesInStock Table to identify already used IDLogs.
The other way is by checking the value of MAX(IDLog) in the output table (if itis not a temporary / variable table) and then specifying WHERE clause for an ArticlesInStock.IDLog value greater than that.
Correct me if I'm wrong. (I am not so good at stringing together T-SQL statements, although I can get my point across, so please pardon me.)
July 19, 2007 at 1:21 am
Due to production optimization (which cause some over production) there are some
articles that have no destination order. This articles are stored to a temporary store.
Every morning the first thing to do (before starting new order optimization)
is to reduce this ever growing temporary store according to the new orders arrived,
that is picking some of the articles and then correct the orders quantity accordingly.
The information is stored in two tables ArticlesInStock and Orders.
ArticlesInStock is a sort of log (IdLog) , that is when and where one article (IdArticle)
was stored to the temporary store.
Orders is the classical table IdOrder, IdDestination, IdArticle, OrderQuantity, ....
So every morning i need to give an output of what to pick (ArticlesInStock.IdLog,...) and
where to send (Orders.IdOrder, Orders.IdDestination) like
select picking.IdLog, picking.IdOrder, picking.IdDestination , ...
from ( ??? your suggestion ??? ) picking
Thanks
July 19, 2007 at 2:50 am
Michele, i do not think I quite understand you; before I reply I need you to tell me:
1. whether I understood your problem correctly or not?
2. in slightly clearer terms, your last explanation.
3. why your ArticlesInStock Table is not normalised?
July 19, 2007 at 3:38 am
thank you goodguy, hope this helps you understand:
--1. whether I understood your problem correctly or not?
yes, i think you get the problem. The process is:
Start
look at the articles in the Orders,
look at the articles in ArticlesInStock,
if (one article match) then
go to the ArticlesInStock take away that specific article located in a specific place for that specific order and decrease that order quantity by 1 (that is your FLAG suggestion)
GoTo Start
else END
--3. why your ArticlesInStock Table is not normalised?
ArticlesInStock is normalized , sorry there are some columns missing like
DateLog, StoreX, StoreY, StoreZ, ... for the seek of brevity.
ArticlesInStock tells me all the articles that are actually present in the temporary store
and where they are. For example that I can have 5 identical articles Art1 in 5 different
places [StoreX, StoreY, StoreZ]
July 19, 2007 at 4:05 am
The solution you want may be simpler than it seems, if only we can get DDL for ArticlesInStock Table.
As it stands, there is duplication of values in the two columns, which prompted the others to avoid your post, and which made me give a compicated solution.
Why don't you post the full DDL (or at least the Primary Key) for ArticlesInStock and let's see what we can do.
July 19, 2007 at 4:31 am
A fast forward cursor may be quicker, but you could try using a temp table. Something like:
CREATE TABLE #temp
(
TID int IDENTITY NOT NULL PRIMARY KEY
,IdOrder int NOT NULL
,IdArticle char(4) COLLATE DATABASE_DEFAULT NOT NULL
,IdLog int NOT NULL
,Quantity int NOT NULL
,UNIQUE (IdOrder, TID)
)
INSERT INTO #temp (IdOrder, IdArticle, IdLog, Quantity)
SELECT O.IdOrder
,O.IdArticle
,A.IdLog
,O.Quantity
FROM Orders O
JOIN ArticlesInStock A
ON O.IdArticle = A.IdArticle
ORDER BY O.idOrder
OPTION (MAXDOP 1)
SELECT T.IdOrder, T.IdLog
FROM #temp T
JOIN (
SELECT T2.IdOrder, MIN(T2.TID) AS MinTID
FROM #temp T2
GROUP BY T2.IdOrder
) D1
ON T.IdOrder = D1.IdOrder
LEFT JOIN (
SELECT O1.IdOrder, SUM(O2.Quantity) AS PrevQuantity
FROM Orders O1
JOIN Orders O2
ON O1.IdArticle = O2.IdArticle
AND O1.IdOrder > O2.IdOrder
GROUP BY O1.IdOrder
) D2
ON T.IdOrder = D2.IdOrder
WHERE T.TID BETWEEN D1.MinTID + ISNULL(D2.PrevQuantity, 0)
AND D1.MinTID + ISNULL(D2.PrevQuantity, 0) + T.Quantity - 1
July 19, 2007 at 5:19 am
July 19, 2007 at 6:31 am
A rough outline of the logic is:
1. The temp table gives alls combinations of order and article with each row assigned a sequential number. (TID) (I do not know if MAXDOP is strictly necessary but, just in case SQLServer decides to insert from different points in the stream with multiple threads, it seems safe.)
2. The first join gets the minimum sequential number for each order.
3. The second join (LEFT JOIN) gets the number of articles used by previous orders.
(ie The running total)
4. The where clause works out which TIDs to select for each order.
On looking at my code again, I do not think IdArticle and Quantity are necessary in the temp table.
Also, if you have a lot of rows you will need to play around with the indexes on the temp table.
July 19, 2007 at 6:34 am
Thank you very much Ken. I'm thaking a look at your suggestion
IdLog is the PK for ArticlesInStock
CREATE TABLE [dbo].[ArticlesInStock] (
[IdLog] [int] IDENTITY (1, 1) NOT NULL ,
[DateLog] [datetime] NOT NULL ,
[IdArticle] [int] NOT NULL ,
[IdLocation] [smallint] NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ArticlesInStock] WITH NOCHECK ADD
CONSTRAINT [PK_ArticlesInStock] PRIMARY KEY CLUSTERED
(
[IdLog]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ArticlesInStock] ADD
CONSTRAINT [FK_ArticlesInStock_Articles] FOREIGN KEY
(
[IdArticle]
) REFERENCES [dbo].[Articles] (
[IdArticle]
),
CONSTRAINT [FK_ArticlesInStock_Locations] FOREIGN KEY
(
[IdLocation]
) REFERENCES [dbo].[Locations] (
[IdLocation]
)
GO
July 19, 2007 at 7:34 am
Thank you all. The solution provided by Ken is exactly what I need.
July 19, 2007 at 8:06 am
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply