How can I build this SQL server update query ? ! ! ! please help me

Question

0.00/5 (No votes)

See more:

SQL-Server-2016

Dear all
i face difficulty in building one sql update query
i have one table(stock_table) containing three columns as follow (item_id,box,loose)
item_id, char(5)
box, smallint ==> represent stock quantity for item_id (full box)
loose, smallint ==> represent stock quantity for item_id (loose) for example item_id is medicine
--------------------------------------
another table is sales table contains also same box,loose columns
--------------------------------------
suppose i have stock from one item as follow
item_id = 10000
box = 3 (single box contains three strips each strip 10 tablets i.e: 3x10)
loose = 0
--------------------------------------
my question is:
if i sold only loose one strip so, remaining stock should be (box = 2, loose = 20)
how can i achieve that ????
For your information i'm using sql database + visual basic windows application
Thanks & Regards

What I have tried:

through stored procedure:
update dbo.stock_table
set box = box - @b,
loose = loose - @c
where item_id = @a (@b,@c ==> are the sold quantities)

it is working however,
remainaing quantities ia not correct

Posted 10-Jul-20 0:43am

ahmed yousif

Updated 10-Jul-20 1:21am

Add a Solution

1 solution

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2020-07-10T01:21:00

Solution 1

I can't help thinking you are going about this the wrong way: the schema you describe only works if all products come in boxes of three strips of 10 items - and in the real world that is generally not the case. Medication for example will often come in strips of seven, ten, fourteen, twenty eight, or thirty-one, and be boxed in two strip, three strip, or four strip depending on the "Normal" number of doses per day.

And most establishments want to "get rid" of the oldest stock first, so loose strips will probably be preferred over whole boxes to keep their count down.

I'd suggest that you re-think it, and include strip and box stock levels, along with strip and box "contains" counts:

item_id = 10000
description = "Something shiny"
BoxContains = 3
StripContains = 10
BoxCnt = 3
StripCnt = 0

And then write your SP to accept a "total sold" and use those numbers to update the DB.

It'll be a lot less trouble in the long run ...

Posted 10-Jul-20 1:21am

OriginalGriff

Comments

ahmed yousif 10-Jul-20 9:42am

Dear originalgriff,
thank you for your reply
i tried another code as follow
now i have three tables
all_medicines ==> contains all medicine data
medicine_stock ==> medicine inventory
sales tables ==> parameters values
==========================================
create proc dbo.test_update
@a char(5),
@b smallint,
@c smallint
as
UPDATE dbo.medicine_stock
SET box = medicine_stock.box - @b,
loose = CASE
WHEN loose > 0 THEN loose - @c
WHEN loose = 0 THEN (box * all_medicines.box_content) - @c / all_medicines.box_content
WHEN loose = 0 THEN all_medicines.box_content - @c
END
FROM dbo.all_medicines
INNER JOIN dbo.medicine_stock
ON dbo.all_medicines.medicine_id = dbo.medicine_stock.medicine_id
where dbo.medicine_id = @a;
-----------------------note-------------------------------
box_content column from all_medicines table represent number of tablets per box
this code does not work
what should i do ??
thanks in advance

OriginalGriff 10-Jul-20 9:58am

"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.

Tell us what you did to try and find out why - what did the debugger show you was happening?

MadMyche 10-Jul-20 10:35am

Take a careful look at your CASE statement; you have the same condition twice:

loose = CASE
  WHEN loose>0 THEN loose - @c
  WHEN loose=0 THEN (box*all_medicines.box_content)- @c/all_medicines.box_content
  WHEN loose=0 THEN all_medicines.box_content - @c
END

ahmed yousif 10-Jul-20 10:49am

after execute the query it returns '0 rows affected' means nothing changed in stock columns (there is no any error messages)
what iam expecting,
* if i sold 1 full box + 0 strips==> only box column decrease by 1 nothing happen to strips column
------------------------------------------------
* if i sold only 10 tablets(one strip)==> here there is two probabilities:

1- if column strips already contains stock from this item for example, 10 tablets
then column strips should decrease by 10 tablets (column box remains as it is)

2- if column strips is 0 stock for this item,for example i have 3 boxes : 0 loose
and i sold only 10 tablets(one strip) now i want stock to be [2 boxes: 20 loose]
----------------------------------note-----------------------------------------
i predefined total number of tablets of each box (i.e: box of 30 tablets,50 tablets,....)
i hope i explained my issue

OriginalGriff 10-Jul-20 10:57am

Which means what?
Why would an UPDATE query affect no rows?

If you think about it, it's obvious: because there are no rows to alter - a WHERE clause or JOIN has returned no rows.
So look at the values, use SSMS to modify the UPDATE to a SELECT and see exactly what is returned - no matching rows, nothing to update.
We don't have access to your DB to to try it!

ahmed yousif 10-Jul-20 11:08am

how can i upload my sql database??

OriginalGriff 10-Jul-20 11:35am

You can't and I wouldn't want it!

Come on - this isn't complicated. Run it as a SELECT and see what it returns. Then look at your DB to find out why your rows aren't in the collection you expected.

How can I build this SQL server update query ? ! ! ! please help me

1 solution

Solution 1

Add your solution here

Preview 0

How can I build this SQL server update query ? ! ! ! please help me

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us