SQLTeam.com | Weblogs | Forums

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

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.

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

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)

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

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

I was wondering. As Debug has been depreciated.

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.

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