You are here:

MS SQL Server/arithmetic over using 2 database tables



I currently have 3 tables in my database as described below:

sampleId, current_amount, sampleDate

aliquotID, sampleID, aliquot_current_amount

labID, aliquotID, aliquot_amount_used, labDate

Aliquots are basically made up from one sample and are then sent to various places to use in experiments. What I would like to do is keep a record of the amounts left for each aliquot and then the overall amount of the sample.

So for example, the sampleID is T3000, the aliquotIDs are T3000-1, T3000-2, T3000-3. the amount of the sample T3000 is 30ml. Each aliquot has 10ml each. However, 5ml of aliquot T3000-1 is sent to be used in an experiment.
How do I then record the current amounts so that aliquot T3000-3 only has 5ml left and the sample T3000 has 25ml left?


I may not have a 100% crystal clear picture of what's going on, so if this takes a back-and-forth or two, that's totally fine.

Here's how I think the data looks at the beginning of your example:

sampleId, current_amount, sampleDate
T3000, 30, 10/30/2014

aliquotID, sampleID, aliquot_current_amount
T3000-1, T3000, 10
T3000-2, T3000, 10
T3000-3, T3000, 10

Then 5 ml of T3000-1 is sent, which I'm assuming means a row is added to LAB_PROJECTS. Here's where some of the gray area creeps in. I don't know the process/mechanism for determining the various facts (which sample, which lab, etc.) nor do I know things like "do you want to know that T3000-1 originally had 10ml and/or T3000 originally had 30ml?" (although that could be determined by querying the LAB_PROJECTS table and summing by aliquotID and/or sampleID).

So I'll go with the simplest scenario and we can expand from there if necessary.

The assumption is that your program or process knows all the facts. At that point, it's as simple as something like this:
DECLARE @experiment_amount INT
DECLARE @experiment_labID INT
DECLARE @experiment_date DATETIME
DECLARE @experiment_source INT --this is the id of the aliquot the sample is drawn from

<<here would be some mechanism to populate the above variables... perhaps parameters to a stored procedure?>>

INSERT INTO LAB_PROJECTS VALUES (@experiment_labID, @experiment_source, @experiment_amount, @experiment_date)

UPDATE ALIQUOT SET aliquot_current_amount = aliquot_current_amount - @experiment_amount
WHERE aliquotID = @experiment_source

UPDATE SAMPLE SET current_amount = current_amount = @experiment_amount
JOIN ALIQUOT A ON S.sampleID = A.sampleID
WHERE aliquotID = @experiment_source

Now, the above is, as I said, VERY simplistic. For example, it doesn't account for cases where the selected ALIQUOT doesn't have the required quantity.

Does that address your issue? If I've totally missed the mark, feel free to expound on your question and I'll try again. Or, if I'm on the right track but still missed somethign, we can use the above as a starting point.

MS SQL Server

All Answers

Answers by Expert:

Ask Experts


David Vaughn


Performance tuning; T-SQL syntax;


Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Truman State University

©2017 All rights reserved.