"simon" wrote:

> Case of study:
> I have warehouse which stores items.
> Wharehouse has defined different locations - location is represented with
> locationID.
> (location has also type and distance - that is the distance in
> meters from start position, but this data is used just for order)
> Each location has one or more palette. Palette has unique label: itemOZN.
> Each palette has many items(articles), and item is marked with itemID.
>
> Each combination of palette and item has different stockID
> (on the same palette can be items with the same itemID but different
> properties)
>
> So, if you want to define one item in stock, you must have its stockID.
>
> When I get request to export one ot more items(articles) with some quantity,
> I must find
> all the positions of the items in the warehouse
> (the same article can be on different locations) and take the most
> appropriate ones (already reserved are at the end of the choice, and also
> closer articles has
> the precedence over the more distantiated ones,
> also if the production date is older, than the article must go out from
> stock before the new ones, and so on..)
>
> When I find the appropriate items, I must wite them into reservation table.
> That is stockID and quantity.
>
> I hope that it's clear enough. It's simple example and I solve it with 2
> cursor.
> It works perfect but with slow performance.
>
> You have my example with all DDL and test data.
>
> I would like to know, if it's possible to create the same solution without
> cursor because of performance reason.
>
> The working code:
>
> declare @itemID varchar(10),@quality int,@requiredQ decimal(15,5),@pDate
> datetime,@lokType int,@stockID int
> declare @availableQ decimal(15,5)
>
> BEGIN TRANSACTION
> --first, I lookup for all required items to export from warehouse
> declare cCur cursor local for select
> itemID,quality,quantity,productionDate,lokType from dbo.tblRequiredItems
> open cCur
> fetch next from cCur INTO @itemID ,@quality ,@requiredQ ,@pDate, @lokType
> while @@fetch_status=0
> begin
> --for each item I find all stocks and I order stocks from most appropriate
> to the less appropriate(ORDER BY)
> --if quality is gived I must find the items with required or better quality
> else doesn't matter, the same is with production date
>
>  declare cCur1 cursor local for
>  select T1.stockID,T1.availableQ FROM
>  (select i.stockID,i.itemQuantity-isnull(sum(r.quantity),0) as availableQ,
>  reservation=case when exists(SELECT * FROM dbo.tblReservations r1 INNER
> JOIN dbo.tblItems i1
>  ON r1.stockID=i1.stockID WHERE i1.itemID=@itemID) then 1 else 0 end,
> l.distance
>  from dbo.tblItems i INNER JOIN dbo.tblLocation l ON
> i.locationID=l.locationID
>  AND (@lokType is null OR l.lokType=@lokType) LEFT JOIN dbo.tblReservations
> r ON i.stockID=r.stockID
>  WHERE i.itemID=@itemID AND (@pDate is null OR i.itemProductionDate>=@pDate)
> AND
>  (@quality is null OR i.itemQuality>=@quality)
>  GROUP BY i.stockID,i.itemQuantity,l.distance
>  having i.itemQuantity-isnull(sum(r.quantity),0)>0)as T1
>  ORDER BY T1.reservation,T1.distance,T1.availableQ DESC
>
>  open cCur1
>  fetch next from cCur1 INTO  @stockID,@availableQ
> --I reserve items until reach the required quantity
>  while @@fetch_status=0
>  begin
>   if @availableQ>=@requiredQ
>   begin
>    if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@stockID)
>     UPDATE dbo.tblReservations SET quantity=quantity+@requiredQ WHERE
> stockID=@stockID
>    else
>     INSERT INTO dbo.tblReservations(stockID,quantity)
>     SELECT @stockID,@requiredQ
>
>    SET @requiredQ=0
>    BREAK
>   end
>   else
>   begin
>    SET @requiredQ=@requiredQ-@availableQ
>    if exists(SELECT * FROM dbo.tblReservations WHERE stockID=@stockID)
>     UPDATE dbo.tblReservations SET quantity=quantity+@availableQ WHERE
> stockID=@stockID
>    else
>     INSERT INTO dbo.tblReservations(stockID,quantity)
>     SELECT @stockID,@availableQ
>   end
>   fetch next from cCur1 INTO @stockID,@availableQ
>  end
>  close cCur1
>  deallocate cCur1
>
>  if @requiredQ>0
>  begin
>   RAISERROR(60017,11,1)
>   break
>  end
>
>  fetch next from cCur INTO @itemID ,@quality ,@requiredQ ,@pDate, @lokType
> end
> close cCur
> deallocate cCur
>
> delete from dbo.tblRequiredItems
>
> if @@error=0
>  COMMIT TRANSACTION
> else
>  ROLLBACK TRANSACTION
>
> The DDL with sample data:
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblItems]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[tblItems]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblLocation]') and OBJECTPROPERTY(id, N'IsUserTable') =
> 1)
> drop table [dbo].[tblLocation]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblRequiredItems]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[tblRequiredItems]
> GO
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[tblReservations]') and OBJECTPROPERTY(id, N'IsUserTable')
> = 1)
> drop table [dbo].[tblReservations]
> GO
>
> CREATE TABLE [dbo].[tblItems] (
>  [stockID] [int] IDENTITY (1, 1) NOT NULL ,
>  [itemOZN] [varchar] (20) COLLATE Slovenian_CI_AS NOT NULL ,
>  [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
>  [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
>  [itemQuantity] [decimal](15, 5) NOT NULL ,
>  [itemQuality] [int] NOT NULL ,
>  [itemProductionDate] [datetime] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblLocation] (
>  [locationID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
>  [lokType] [int] NULL ,
>  [distance] [int] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblRequiredItems] (
>  [requireID] [int] IDENTITY (1, 1) NOT NULL ,
>  [itemID] [varchar] (10) COLLATE Slovenian_CI_AS NOT NULL ,
>  [quality] [int] NULL ,
>  [quantity] [decimal](15, 5) NOT NULL ,
>  [productionDate] [datetime] NULL ,
>  [lokType] [int] NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[tblReservations] (
>  [reservationID] [int] IDENTITY (1, 1) NOT NULL ,
>  [stockID] [int] NULL,
>  [quantity] [decimal](15, 5) NOT NULL ,
>
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblItems] ADD
>  CONSTRAINT [PK_tblItems] PRIMARY KEY  CLUSTERED
>  (
>   [stockID]
>  )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblLocation] ADD
>  CONSTRAINT [PK_tblLocation] PRIMARY KEY  CLUSTERED
>  (
>   [locationID]
>  )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblRequiredItems] ADD
>  CONSTRAINT [PK_tblRequiredItems] PRIMARY KEY  CLUSTERED
>  (
>   [requireID]
>  )  ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[tblReservations] ADD
>  CONSTRAINT [PK_tblReservations] PRIMARY KEY  CLUSTERED
>  (
>   [reservationID]
>  )  ON [PRIMARY]
> GO
>
>
>
>
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('A001',1,100)
>
> INSERT INTO [tblLocation]([locationID], [lokType], [distance])
> VALUES('B001',1,200)
>
> INSERT INTO [dbo].[tblLocation]([locationID], [lokType], [distance])
> VALUES('C001',2,150)
>
>
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0001','A001',20,1,'20050101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0002','A001',15,1,'20050202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00001','0003','A001',50,1,'20050101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0001','B001',30,1,'20050101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0002','B001',60,1,'20050101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00002','0003','B001',40,1,'20050101')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0001','B001',5,1,'20050202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0002','B001',10,1,'20050202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00003','0003','B001',30,1,'20050202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0001','C001',20,1,'20050202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0002','C001',5,1,'20050202')
> INSERT INTO [dbo].[tblItems]([itemOZN], [itemID], [locationID],
> [itemQuantity],[itemQuality], [itemProductionDate])
> VALUES('00004','0003','C001',25,1,'20050202')
>
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(1,5)
>
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(5,10)
>
> INSERT INTO [simon].[dbo].[tblReservations]([stockID], [quantity])
> VALUES(9,12)
>
>
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0001',1,40,'20050101',null)
>
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0002',1,10,'20041212',null)
>
> INSERT INTO [dbo].[tblRequiredItems]([itemID], [quality], [quantity],
> [productionDate], [lokType])
> VALUES('0003',null,10,null,2)
>
>
>