Practical Buy Stock in On One size, Sell as Another, and Bring Back to Original

1

Everyone was so helpful it was great last time, however again I am needing insight into this issue, and frankly I don't really know where to begin.

The two main tables I am dealing with are ShtStock:
GO

/****** Object:  Table [dbo].[ShtStock]    Script Date: 30/01/2020 8:42:44 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[ShtStock](
	[ShtStockId] [int] IDENTITY(1,1) NOT NULL,
	[Barcode] [nvarchar](500) NULL,
	[PartNumber] [nvarchar](50) NOT NULL,
	[Description] [nvarchar](500) NOT NULL,
 CONSTRAINT [PK__ShtS__1B17B9B1711A71B9] PRIMARY KEY CLUSTERED 
(
	[ShtStockId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

And then StockDelivered

GO

/****** Object:  Table [dbo].[StockDelivered]    Script Date: 30/01/2020 8:47:48 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StockDelivered](
	[DeliveredId] [int] IDENTITY(1,1) NOT NULL,
	[DeliveryDate] [datetime] NOT NULL,
	[Barcode] [nvarchar](500) NULL,
	[ShtStockId] [int] NOT NULL,
	[Quantity] [float] NOT NULL,
	[WaterBodyID] [int] NOT NULL,
	[RegistrantEmail] [varchar](50) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[DeliveredId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[StockDelivered]  WITH CHECK ADD  CONSTRAINT [FK_StockDelivered_ShtStock] FOREIGN KEY([ShtStockId])
REFERENCES [dbo].[ShtStock] ([ShtStockId])
GO

ALTER TABLE [dbo].[StockDelivered] CHECK CONSTRAINT [FK_StockDelivered_ShtStock]
GO

This all works fine...as long as there is an entry in the ShtStock to match the StockDelivered. No issues so far, this is for information.

I am upsizing from an MS Access application and very new to SQL, so please if I am asking dumb questions please understand its because it is all very foreign to me still.

The actual issue I have is there are barcodes in the system that are not official company stock items. Best way to explain this is real world, and I will use laundry powder as most people know what that is...I hope.

I purchase the laundry powder in 25 kilogram sacks. The 25 kilogram sack has a ShtStock. PartNumber call it 1234 and a ShtStockID of 23.

I then sell the 25 kilograms in 1 kilogram lots which I have created a special part number for. Call it SPC133 with a ShtStockID 189.

In MS Access my solution is to create a bridge table, and this may work in SQL, haven't got this done yet, but need it to make the story clearer for the part I am not sure how to approach.

Bridge table contains:
ShtStockID (189 in our example)
Divisor (25 in the case of the laundry powder example)
RelatedStockID (23 in our example)

This is simple enough.

In MS Access what I then do is I run a bit of VBA to tell it if there is a ShtStockID in teh Bridging Table, then divide the StockDelivered.Quantity by the Bridge Table.Divisor and substitute this amount with the RelatedStockID and associated description into the query.

I am unclear how to go about approaching this, or even what to ask about or search for to learn how to do this. I apologise if this doesn't make sense, please ask questions as need to clarify.

2

Still wading through this.

I have
CREATE PROCEDURE [dbo].[TestBarCodeIdea]
-- Add the parameters for the stored procedure here
@PassInID int = 0,
@BackID int = 0
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    BEGIN

    	Set @BackID =(SELECT dbo.StockLink.AssociatedID
    FROM            dbo.StockLink 
    WHERE        (dbo.StockLink.StockID = @PassInID))

    End

    If @BackID = 0 
    Begin 

    Set @BackID =(SELECT        dbo.ShtStock.ShtStockId
    FROM            dbo.ShtStock 
    WHERE        (dbo.ShtStock.ShtStockId = @PassInID))

    End

    SELECT        dbo.ShtStock.ShtStockId
    FROM            dbo.ShtStock 
    WHERE        (dbo.ShtStock.ShtStockId = @BackID)

Which is partially working...If I send in 574 as @PassInID it should return 574 as there is no associatedID. However it returns 0.

If I send in 1995 as @PassID it returns 462 which is the corresponding and correct assciatedID.

I cant see why

3

Run this in SSMS and see what results and post back?

declare @PassInID int = 574 ;
BEGIN
declare @BackID int = 0
    	Set @BackID =(SELECT dbo.StockLink.AssociatedID
    FROM            dbo.StockLink 
    WHERE        (dbo.StockLink.StockID = @PassInID))

    End

--What do you see here ?
select @BackID
PRINT @BackID

    If @BackID = 0 
    Begin 

    Set @BackID =(SELECT        dbo.ShtStock.ShtStockId
    FROM            dbo.ShtStock 
    WHERE        (dbo.ShtStock.ShtStockId = @PassInID))

    End

    SELECT        dbo.ShtStock.ShtStockId
    FROM            dbo.ShtStock 
    WHERE        (dbo.ShtStock.ShtStockId = @BackID)
4

the issue is this check

If @BackID = 0

because the following
Set @BackID =(SELECT dbo.StockLink.AssociatedID
FROM dbo.StockLink
WHERE (dbo.StockLink.StockID = @PassInID))

return NULL

1 Like
5

Is that how I can see the value of a variable?

I was wondering. As Debug has been depreciated.

6

Thanks yosiaz I eventually realised this was the issue and added an NZ function I found online to deal with this. This part works okay now...But the rest is proving interesting to solve. Baby steps for new people like me.

7

We were all there at one time. Every journey starts with a first step