SELECT statement of stock

27 Aug 2005 8:06 PM

simon

I have table tblArticles where I have stock of all articles.

Some of them are reserved. The reservations are in table tblReservations.

If I would like to see all available stock, I should select all from
tblArticles
reduced with reserved articles.

example:

tblArticles:(PS_SIF is the package mark)

ART_SIF    PS_SIF    quality    quantity
----------------------------------------------
   ART1            1            1            100
   ART1            1            2            200
   ART1            2            1              20
   ART1            3            1              30
   ART1            2            2              30
   ART2            2            1              50
........
........

tblReservations:
(I have reserved 20 peaces of ART1 of quality 1
and 10 peaces of ART1 of quality 2.)

ART_SIF    quality    quantity
-----------------------------------------
    ART1           1            40
    ART1           2            10
.........
.........

So I must reduce ART1 of quality1 for 40peaces from tblArticles to get the
real stock.

In tblArticles I have many ART1 with quality1 but with different PS_SIF.
Lets say I take out from stock first the articles with greater PS_SIF.

Select statement is something like this:
SELECT * from tblArticles-(select * from tblReservations) order by PS_SIF
desc

And the resullt should be:

ART_SIF    PS_SIF    quality    quantity
----------------------------------------------
   ART1            1            1            100
   ART1            1            2            200
   ART1            2            1              10
   ART1            2            2              20
   ART2            2            1              50
........

What is the select statement?
If possible without cursors.

Regards,S

Here is DDL:

CREATE TABLE [dbo].[tblReservations] (
[ART_SIF] [varchar] (50) NOT NULL ,
[quality] [int] NOT NULL ,
[quantity] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblArticles] (
[ART_SIF] [varchar] (50) NOT NULL ,
[PS_SIF] [varchar] (50) NOT NULL ,
[QUALITY] [int] NOT NULL ,
[quantity] [decimal](18, 0) NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblReservations] ADD
CONSTRAINT [PK_tblReservations] PRIMARY KEY CLUSTERED
(
[ART_SIF],
[quality]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblArticles] ADD
CONSTRAINT [PK_tblArticles] PRIMARY KEY CLUSTERED
(
[ART_SIF],
[PS_SIF],
[QUALITY]
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tblArticles] VALUES ('ART1','1','1',100)
INSERT INTO [dbo].[tblArticles] VALUES ('ART1','1','2',200)
INSERT INTO [dbo].[tblArticles] VALUES ('ART1','2','1',20)
INSERT INTO [dbo].[tblArticles] VALUES ('ART1','3','1',30)
INSERT INTO [dbo].[tblArticles] VALUES ('ART1','2','2',30)
INSERT INTO [dbo].[tblArticles] VALUES ('ART2','2','1',50)
GO

INSERT INTO [dbo].[tblReservations] VALUES ('ART1',1,40)
INSERT INTO [dbo].[tblReservations] VALUES ('ART1',2,10)

27 Aug 2005 9:23 PM

--CELKO--

Do you really have this many codes that are all CHAR(50) and a quantity
that is greater than the number of atoms in the universe? You do not
put a prefix like "tbl-" in front of table names. I do not think
you did any research on this DDL.

Can we do this in one table instead of splitting the inventory over
multiple tables?

CREATE TABLE Articles
(art_sif CHAR(10) NOT NULL,
ps_sif CHAR(10) NOT NULL,
quality_code INTEGER DEFAULT 1 NOT NULL
CHECK (quality_code BETWEEN 1 AND 3),
qty_in_stock INTEGER DEFAULT 0 NOT NULL
CHECK (qty_in_stock >= 0),
qty_on_reserve INTEGER DEFAULT 0 NOT NULL
    CHECK (qty_on_reserve >= 0),
PRIMARY KEY (art_sif, ps_sif, quality_code)
);

You can also add other types of quantities - damaged, on order, etc.
Now just do an update to move articles around.

UPDATE Articles
   SET qty_in_stock = qty_in_stock - @my_qty,
       qty_on_reserve = qty_on_reserve + @my_qty
WHERE art_sif = @my_art_sif
   AND ps_sif = @my_ps_sif
   AND quality_code = @my_quality_code;