SQL to create sub records - Divide liquid volume into sub volumes (1 Viewer)

Lightwave

Ad astra
Local time
Today, 17:46
Joined
Sep 27, 2004
Messages
1,521
Dear All

Looking for TSQL appropriate for SQL Azure.

I have a need to create sub records on a table by calculating a split of a volume. I probably haven't explained that very well but if you see the example below it might be clearer. Essentially I have casks and I want to split them into bottles... The below example numbers are a simplified case.

So I would like to generate table 2 below from table 1 - and if I change the split into value it will alter the number of records in table 2.
I would probably create just a query first of all and then I understand how to insert into using select. I'm struggling how to do that split at the moment. I've looked into split with google but so far I keep getting pointed at parsing.

Table 1
pkid Volume Split into
1 100litres 40litres

Table 2
pkid pkidtable1 Volume
1 1 40litres
2 1 40litres
3 1 20litres

I'm thinking I'll have a query just to create a view of table 2 firstly and then I will do an insert query of that query into table 2 (I know how to do that part)

Any guidance much appreciated.
 
Last edited:

Lightwave

Ad astra
Local time
Today, 17:46
Joined
Sep 27, 2004
Messages
1,521
Thank you that looks like the solution
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:46
Joined
May 7, 2009
Messages
19,169
sorry haven't tried the split in mssql.
but i am interested so i download the express edition.
see if it will work.
 

Lightwave

Ad astra
Local time
Today, 17:46
Joined
Sep 27, 2004
Messages
1,521
OK started thinking about this.. First issue I've thought of is that I need to deal with decimals rather than integers. I guess I can multiply out to remove the decimals I thought that there might be a remainder issue as well but I don't think there will be because I am not dividing by a fraction I am subtracting a set amount each time.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 13:46
Joined
Jan 23, 2006
Messages
15,364
Mark,
I'm not a TSQL nor Azure person, but Mod function and integer division seem appropriate to "split" volumes into parts.
Good luck.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 17:46
Joined
Feb 19, 2013
Messages
16,553
this query will append the quantities

Code:
SELECT Table1.ID, utilCount.Counter, IIf([Counter]<[qty]\[split],[split],[qty] Mod [split]) AS Expr1 INTO table2
FROM utilCount, Table1
WHERE (((utilCount.Counter)<=[qty]\[split]))

it requires a 'counter' table or query (called utilCount above) which is simply a single field table populated with numbers from 0 to whatever you feel is a maximum required. There are ways to generate this as a simple query so can be reused.

I use a query based on a table
utilCounter utilCounter

num
0​
1​
2​
3​
6​
7​
8​
9​
4​
5​

and the utilcount query
Code:
SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM utilCounter AS singles, utilCounter AS tens, utilCounter AS hundreds, utilCounter AS thousands;

which counts from 0 to 9999 which I've found is generally sufficient but can be modified to show greater (or fewer) numbers if required
 

Minty

AWF VIP
Local time
Today, 17:46
Joined
Jul 26, 2013
Messages
10,355
This is an interesting one, I think you can use Row Number and Mod() functions to do this quite easily, with a Numbers table to line up the result.

Edit: @CJ_London beat me to it, and has an example.
 

Minty

AWF VIP
Local time
Today, 17:46
Joined
Jul 26, 2013
Messages
10,355
Double post - this isn't quite what you are after but is a great demo of the sort of manipulation you can achieve;
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:46
Joined
May 7, 2009
Messages
19,169
create a table (tline) to your sql:

tline.png

then create a Query to insert to Table2.
the table t1, in the query should be renamed to your table name:
query.png


the result is table2:

table2.png
 

Lightwave

Ad astra
Local time
Today, 17:46
Joined
Sep 27, 2004
Messages
1,521
So I have started implementing this specifically for my case which is for a whisky distillery splitting whisky into bottles.
As a first step I went through Arnel's and CJs code and just made sure I understood it.
I just thought I would document it here so others can follow more easily as well as for my own reference
Should work in SQL Azure. Should work for a lot of splitting problems.

Code:
CREATE TABLE [dbo].[t1](
    [pkid] [int] IDENTITY(1,1) NOT NULL,
    [volume] [decimal](10, 4) NULL,
    [split] [decimal](10, 4) NULL
) ON [PRIMARY]

And then manually enter in a couple of different splits eg (in my example the numbers are in litres 100 litre cask being split into 1 litre bottles and a 50 litre cask being split into 700cl bottles.
100 - 1
50 -0.7

Next we add the Counter table referred to by arnelg and CJ_London and required by the final SQL insert query.
Code:
CREATE TABLE [dbo].[t1](
    [pkid] [int] IDENTITY(1,1) NOT NULL,
    [volume] [decimal](10, 4) NULL,
    [split] [decimal](10, 4) NULL
) ON [PRIMARY]

Next we edit this table and add sufficient counters..
1 1
2 2
3 3
4 4
5 5
.... etc.. the number of entries needs to be larger than the division that will be required.

Next create the table to hold in this case the splits which in my case will be bottles
Code:
CREATE TABLE [dbo].[table2](
    [line] [int] NULL,
    [pkid] [int] NOT NULL,
    [volume] [decimal](15, 4) NULL
) ON [PRIMARY]

And here is the code that creates the split, in my case bottles into table 2.
I slightly altered arnelg's code to insert into table2 rather than his code that required table2 not to exist and the query created it. I also referenced the split from the table in the query so multiple different splits can be created at the same time from multiple records in t1.
Code:
INSERT INTO table2(line,pkid,volume)
SELECT
    b.line,
    a.pkid,
    iif(a.split* b.line > a.volume, a.volume - a.split * (b.line-1), a.split) as volume
    from dbo.t1 as a , dbo.tline as b
    where a.split * b.line <= a.volume + a.split;

I'm going to write this up on my blog at some point and possibly make it into a stored procedure so that t1 has a bit field that records whether the split has occurred and filter out those that have been split already from t1 because in production sadly they haven't figured out how to create the infinitely full Whisky cask!

Interestingly in production they dilute the cask whisky from whatever strength it is to usually 40% proof so there will be some additional fields in t1 that shows the existing strength and a target strength and I am guessing their is a formula that will allow me to work out the actual volume of water required to reach 40% and then the split will be on that total amount. Thanks Again - Mark
 
Last edited:

Users who are viewing this thread

Top Bottom