getting the first expiry products using MS SQL

Hi satabeach,

I am checking. I will get back to you soon.

More explanation is needed as lets say when values change then what will happen.

Also which column is primary key.

And how do you group panadol tablets? Grouping column must be created.

The table design is not proper for the result you are looking for.

Primary key column is stock Id in the inventory table and I have another table called Products with primary key productId

When the user changed to quantity, for example user requested 5 panadol (productId 10) then need to check whether how many panadol are there in the stock with short expiry, lets say 2 are there in the short expiry (stockId 2), take the two from the short expiry and then take another 3 from the next expiry (stockId 5)

Now the table shoudl be like this

when the customer changes quanity to 2

now the table should look like this

I understand your question but I have a doubt regarding updated Table after changing the quantity.

If customer changes quantity to 2 then row with quantity 2 must be removed and rest of the data must be present in the database. Like below.

If I got you wrong please correct me.

Yes, but not this one, it should come like below and it has short expry, If customer changed quantity to 1 the result will be same

and if customer wants 3 then the result will be this, becourse it has 2 with stockid (5) which is less than the custermer required amount and also have same product with another batch and expiry