April 6, 2012 at 5:53 am
Hi, I need help in resolving this issue. I have the following tables (Some comments are there to understand the logic)
DECLARE @UnitsOfMeasurement TABLE
(
[UOM_ID] INT NOT NULL PRIMARY KEY,
[UOM_NAME] [nvarchar](100) NOT NULL,
[UOM_GROUP] INT NOT NULL --TO KEEP SAME MEASUREMENTS IN A GROUP.
)
INSERT @UnitsOfMeasurement
(
[UOM_ID]
,[UOM_NAME]
,[UOM_GROUP]
)
SELECT 1, 'Millilitre', 1
UNION ALL
SELECT 2, 'Litre', 1
UNION ALL
SELECT 3, 'Gallon', 1
UNION ALL
SELECT 4, 'Hectolitre', 1
DECLARE @Item_UOM TABLE
(
[ITU_ID] INT IDENTITY(1,1) NOT NULL,
[ITU_UOM_FK] INT NULL, --FROM @UnitsOfMeasurement Table
[ITU_ITM_FK] INT NULL,
[ITU_QUANTITY_UOM] [float] NULL, -- Conversion Factor. Since Millilitre is the basic UOM, this field would have 1 for ML and 1000 for Litre etc.
[ITU_QUANTITY_IN_HAND] [float] NOT NULL DEFAULT ((0)) --ACTUAL QUANTITY For e.g. 100 ml OR 50 Litres etc
)
INSERT @Item_UOM
(
[ITU_UOM_FK]
,[ITU_ITM_FK]
,[ITU_QUANTITY_UOM]
,[ITU_QUANTITY_IN_HAND]
)
SELECT 1, 1, 1, 500 -- Millilitre Stock for Item
UNION ALL
SELECT 2, 1, 1000, 100 -- Litre Stock for Item
UNION ALL
SELECT 3, 1, 3785.41178, 50 -- Gallon Stock for Item
UNION ALL
SELECT 4, 1, 100000, 50 -- HectoLitre Stock for Item
So we have
SELECT SUM([ITU_QUANTITY_UOM] * [ITU_QUANTITY_IN_HAND]) FROM @Item_UOM
5289770.589 Millilitres Stock.
Now, the problem is I have to keep the stock updated for each sale i.e. [ITU_QUANTITY_IN_HAND] column. The user can sell items on millilitre basis, litre basis OR whatever measurement against the item. There is an application check that ensures that the targeted sale could not exceed the maximum quantity in hand. Moreover, the user can use different kind of measurements in one sale. For e.g. Selling 600 ml, 5 Litre, 1 HectoLitre in one go is a valid scenario. These targeted sale item values and measurements would be available as an xml parameter. i.e.
declare @p1 xml
set @p1=convert(xml,N'<SalesInvoiceItemCollection>
<SalesInvoiceItem>
<ITU_ITM_FK>1</ITU_ITM_FK>
<ITU_UOM_FK>1</ITU_UOM_FK>
<ITU_QUANTITY_IN_HAND>600</ITU_QUANTITY_IN_HAND>
<SII_LINE_NO>1</SII_LINE_NO>
</SalesInvoiceItem>
<SalesInvoiceItem>
<ITU_ITM_FK>1</ITU_ITM_FK>
<ITU_UOM_FK>2</ITU_UOM_FK>
<ITU_QUANTITY_IN_HAND>5</ITU_QUANTITY_IN_HAND>
<SII_LINE_NO>2</SII_LINE_NO>
</SalesInvoiceItem>
<SalesInvoiceItem>
<ITU_ITM_FK>1</ITU_ITM_FK>
<ITU_UOM_FK>4</ITU_UOM_FK>
<ITU_QUANTITY_IN_HAND>1</ITU_QUANTITY_IN_HAND>
<SII_LINE_NO>3</SII_LINE_NO>
</SalesInvoiceItem>
</SalesInvoiceItemCollection>')
SELECT nref.value('ITU_ITM_FK[1]', 'INT') ITU_ITM_FK
,nref.value('ITU_UOM_FK[1]', 'INT') ITU_UOM_FK
,nref.value('ITU_QUANTITY_IN_HAND[1]', 'FLOAT') ITU_QUANTITY_IN_HAND
,nref.value('SII_LINE_NO[1]', 'INT') SII_LINE_NO
FROM @p1.nodes('//SalesInvoiceItem') AS R(nref)
A set based solution is what I am looking for. I have tried to keep things simple enough. I hope I was able to convery it properly. Thanks in advance.
I missed the output 🙁 Here it is
Since 600 ml, 5Litre and 1 HectoLitre were to be sold
and the current stock is
500 ml 100 Litre (1000 ml) 50 Gallon (3785.41178 ml) 50 HectoLitre(100000 ml)
The remaining stock could be
0 ml 94.9 Litre (1000 ml) 50 Gallon (3785.41178 ml) 49 HectoLitre(100000 ml)
The subtraction could be done from higher or lower unit of measurement
EDIT:
To let know why I do not have the Conversion value in the UnitOfMeasurement table and for more details:
There are different kind of measurements. As for measurement in Carton/packs/pieces, for certain items the values could be different. For e.g. Item1 could have 12 pieces in one pack but for item2 there could be 20 pieces.
EDIT1:
For a calculation on piece, pack and carton, here is how it would be done
We have current stock for Item10
ConversionFactor UOMType QuantityInHand
1 Piece 20
12 Pack 10
60 Carton 5
The remaining Stock would be
20-20+( 2 pieces remaining from the pack) , 11 Packs(10 pieces sold and remaining 2 are moved to pieces UOM) 4 Carton (1 Carton is sold)
At the moment, we are not dealing with fractions, so the remainder i.e. 2 pieces from packs are moved back to the pieces UOM
PS: One Item can have only one kind of measurement assigned to it.
April 6, 2012 at 6:06 am
are you storing just the xml in the database, or processing the xml?
if you are processing the xml, don't you stick the data into a Product Sold Table somewhere? then it's just a view or select statement to combine the sum(onhand) - sum(sold) = current stock.?
am i missing something
Lowell
April 6, 2012 at 6:41 am
Lowell (4/6/2012)
are you storing just the xml in the database, or processing the xml?if you are processing the xml, don't you stick the data into a Product Sold Table somewhere? then it's just a view or select statement to combine the sum(onhand) - sum(sold) = current stock.?
am i missing something
We are processing the xml and we are also storing the transactions in another table. I can understand what you mean and it makes sense. But sometimes you have to live with what you are told to do. The whole purpose of this was
1. To aviod the routine of showing what stock is exacltly there at runtime.
2. Having an updated stock for the item readily available in a single table SELECT.
BTW, I suggested a nightly job to update the stock, as we only had to SUM up the current days data, but that was turned down as well 🙁
April 6, 2012 at 6:53 am
Moreover, to keep things simple, I have not mentioned that there are multiple measurement groups like mg, gram, kg OR packs, carton, pieces etc. as that part could easily be handled. Furthermore, since so many items could be sold with different measurements, we wanted to keep this calculation done after each sale.
April 6, 2012 at 7:49 am
ok first thing is i would move your conversion factor to your unit table, makes for easier changes with out haveing to make sure you update every conversion factor in your items table (and finishes the normalization) so your conversion table would look like:
INSERT @UnitsOfMeasurement
(
[UOM_ID]
,[UOM_NAME]
,[ITU_QUANTITY_UOM] [float] NULL, -- put it here since it changes by unit not by product
,[UOM_GROUP]
)
second could you have on hand table have a set up like this:
DECLARE @Item_UOM TABLE
(
[ITU_ID] INT IDENTITY(1,1) NOT NULL,
[ITU_UOM_FK] INT NULL, --FROM @UnitsOfMeasurement Table
[ITU_ITM_FK] INT NULL,
[ITU_QUANTITY_START_DAY] float NOT NULL -- add this
[ITU_QUANTITY_IN_HAND] [float] NOT NULL DEFAULT ((0)) --ACTUAL QUANTITY For e.g. 100 ml OR 50 Litres etc
)
your sales would go in the sales table, a trigger on sales would update quantity in hand (or part of your check out process) and a nightly job would check the sales table and make sure the numbers match up (Sum quantity_sold + quantity in hand = quantity start) and then update start of day.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 6, 2012 at 7:57 am
capn.hector (4/6/2012)
ok first thing is i would move your conversion factor to your unit table, makes for easier changes with out haveing to make sure you update every conversion factor in your items table (and finishes the normalization) so your conversion table would look like:
INSERT @UnitsOfMeasurement(
[UOM_ID]
,[UOM_NAME]
,[ITU_QUANTITY_UOM] [float] NULL, -- put it here since it changes by unit not by product
,[UOM_GROUP]
)
second could you have on hand table have a set up like this:
DECLARE @Item_UOM TABLE
(
[ITU_ID] INT IDENTITY(1,1) NOT NULL,
[ITU_UOM_FK] INT NULL, --FROM @UnitsOfMeasurement Table
[ITU_ITM_FK] INT NULL,
[ITU_QUANTITY_START_DAY] float NOT NULL -- add this
[ITU_QUANTITY_IN_HAND] [float] NOT NULL DEFAULT ((0)) --ACTUAL QUANTITY For e.g. 100 ml OR 50 Litres etc
)
your sales would go in the sales table, a trigger on sales would update quantity in hand (or part of your check out process) and a nightly job would check the sales table and make sure the numbers match up (Sum quantity_sold + quantity in hand = quantity start) and then update start of day.
I was thinking along the same lines as the cap'n.
I'd also get rid of the acronyms for all the columns: when you are looking at the code 6 months from now, which is easier to understand: UOM_ID or UnitsOfMeasureID for the column name? all the tablenames and column names i'd suggest to make them longer and more descriptive.
I'd still try to use a view to calculate any totals if i could, but i understand how sometimes you've got to use a service broker or trigger or regularly scheduled job to do the updates instead.
Lowell
April 6, 2012 at 8:01 am
capn.hector (4/6/2012)
ok first thing is i would move your conversion factor to your unit table, makes for easier changes with out haveing to make sure you update every conversion factor in your items table (and finishes the normalization) so your conversion table would look like:
INSERT @UnitsOfMeasurement(
[UOM_ID]
,[UOM_NAME]
,[ITU_QUANTITY_UOM] [float] NULL, -- put it here since it changes by unit not by product
,[UOM_GROUP]
)
Sorry, I think I have to edit my question to let know that there are different kind of measurements. That is why it cannot be done. Why? As for measurement in Carton/packs/pieces, for certain items the values could be different. For e.g. Item1 could have 12 pieces in one pack but for item2 there could be 20 pieces.
second could you have on hand table have a set up like this:
DECLARE @Item_UOM TABLE
(
[ITU_ID] INT IDENTITY(1,1) NOT NULL,
[ITU_UOM_FK] INT NULL, --FROM @UnitsOfMeasurement Table
[ITU_ITM_FK] INT NULL,
[ITU_QUANTITY_START_DAY] float NOT NULL -- add this
[ITU_QUANTITY_IN_HAND] [float] NOT NULL DEFAULT ((0)) --ACTUAL QUANTITY For e.g. 100 ml OR 50 Litres etc
)
your sales would go in the sales table, a trigger on sales would update quantity in hand (or part of your check out process) and a nightly job would check the sales table and make sure the numbers match up (Sum quantity_sold + quantity in hand = quantity start) and then update start of day.
In my earlier response, I mentioned nightly job case but that was turned down. Moreover,
a trigger on sales would update quantity in hand (or part of your check out process)
This is the actual process in TSQL I am looking for 🙁
April 6, 2012 at 8:06 am
jewel.sacred (4/6/2012)
capn.hector (4/6/2012)
ok first thing is i would move your conversion factor to your unit table, makes for easier changes with out haveing to make sure you update every conversion factor in your items table (and finishes the normalization) so your conversion table would look like:
INSERT @UnitsOfMeasurement(
[UOM_ID]
,[UOM_NAME]
,[ITU_QUANTITY_UOM] [float] NULL, -- put it here since it changes by unit not by product
,[UOM_GROUP]
)
Sorry, I think I have to edit my question to let know that there are different kind of measurements. That is why it cannot be done. Why? As for measurement in Carton/packs/pieces, for certain items the values could be different. For e.g. Item1 could have 12 pieces in one pack but for item2 there could be 20 pieces.
second could you have on hand table have a set up like this:
DECLARE @Item_UOM TABLE
(
[ITU_ID] INT IDENTITY(1,1) NOT NULL,
[ITU_UOM_FK] INT NULL, --FROM @UnitsOfMeasurement Table
[ITU_ITM_FK] INT NULL,
[ITU_QUANTITY_START_DAY] float NOT NULL -- add this
[ITU_QUANTITY_IN_HAND] [float] NOT NULL DEFAULT ((0)) --ACTUAL QUANTITY For e.g. 100 ml OR 50 Litres etc
)
your sales would go in the sales table, a trigger on sales would update quantity in hand (or part of your check out process) and a nightly job would check the sales table and make sure the numbers match up (Sum quantity_sold + quantity in hand = quantity start) and then update start of day.
In my earlier response, I mentioned nightly job case but that was turned down. Moreover,
a trigger on sales would update quantity in hand (or part of your check out process)This is the actual process in TSQL I am looking for 🙁
If Item1 has 12 pieces per pack and Item2 has 20 pieces per pack, they have different units of measure and conversion values and therefore separate records in the Item_UnitOfMeasure table. This is necessary to properly compute quantities sold and on hand.
April 6, 2012 at 8:15 am
Lynn Pettis (4/6/2012)
jewel.sacred (4/6/2012)
capn.hector (4/6/2012)
ok first thing is i would move your conversion factor to your unit table, makes for easier changes with out haveing to make sure you update every conversion factor in your items table (and finishes the normalization) so your conversion table would look like:
INSERT @UnitsOfMeasurement(
[UOM_ID]
,[UOM_NAME]
,[ITU_QUANTITY_UOM] [float] NULL, -- put it here since it changes by unit not by product
,[UOM_GROUP]
)
Sorry, I think I have to edit my question to let know that there are different kind of measurements. That is why it cannot be done. Why? As for measurement in Carton/packs/pieces, for certain items the values could be different. For e.g. Item1 could have 12 pieces in one pack but for item2 there could be 20 pieces.
second could you have on hand table have a set up like this:
DECLARE @Item_UOM TABLE
(
[ITU_ID] INT IDENTITY(1,1) NOT NULL,
[ITU_UOM_FK] INT NULL, --FROM @UnitsOfMeasurement Table
[ITU_ITM_FK] INT NULL,
[ITU_QUANTITY_START_DAY] float NOT NULL -- add this
[ITU_QUANTITY_IN_HAND] [float] NOT NULL DEFAULT ((0)) --ACTUAL QUANTITY For e.g. 100 ml OR 50 Litres etc
)
your sales would go in the sales table, a trigger on sales would update quantity in hand (or part of your check out process) and a nightly job would check the sales table and make sure the numbers match up (Sum quantity_sold + quantity in hand = quantity start) and then update start of day.
In my earlier response, I mentioned nightly job case but that was turned down. Moreover,
a trigger on sales would update quantity in hand (or part of your check out process)This is the actual process in TSQL I am looking for 🙁
If Item1 has 12 pieces per pack and Item2 has 20 pieces per pack, they have different units of measure and conversion values and therefore separate records in the Item_UnitOfMeasure table. This is necessary to properly compute quantities sold and on hand.
to lynn's point. if 6 months after you start using this you have to change the pieces per pack you have to make sure to update every item using that new measure. if you have 20 items and only change 18 you have errors in your database. if you have 20 items that are changing in my and lynn's setup but not all 12 peices to a pack are changing, you add a new unit of measure for the 20 items that are changing (or if you all ready have an x pieces per pack unit that they would fit you change the unit id)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 6, 2012 at 8:19 am
jewel.sacred (4/6/2012)
a trigger on sales would update quantity in hand (or part of your check out process)[/quote-0]This is the actual process in TSQL I am looking for 🙁
i would see if since you can query the current on hand column could you not have a job to verify incase you end up with a bad update? those things do happen and having a check all ready built in before going live will save some headaches in the future when it happens. Murphy is a bitch some times
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 6, 2012 at 8:31 am
capn.hector (4/6/2012)
to lynn's point. if 6 months after you start using this you have to change the pieces per pack you have to make sure to update every item using that new measure. if you have 20 items and only change 18 you have errors in your database. if you have 20 items that are changing in my and lynn's setup but not all 12 peices to a pack are changing, you add a new unit of measure for the 20 items that are changing (or if you all ready have an x pieces per pack unit that they would fit you change the unit id)
I agree with that. But that change is allowed only once through the application. Any change in that is logged and the application is configured to stop running if there is a change made manually in the table. And I also know that this hurdle can be overcome by a smartie, but I guess you cannot cope with all the situations.
April 6, 2012 at 8:33 am
Just for the information, I have edited my question and put in some more info. If anyone wants more information, please let me know and I will update the question with that. Thanks.
April 6, 2012 at 8:48 am
jewel.sacred (4/6/2012)
capn.hector (4/6/2012)
to lynn's point. if 6 months after you start using this you have to change the pieces per pack you have to make sure to update every item using that new measure. if you have 20 items and only change 18 you have errors in your database. if you have 20 items that are changing in my and lynn's setup but not all 12 peices to a pack are changing, you add a new unit of measure for the 20 items that are changing (or if you all ready have an x pieces per pack unit that they would fit you change the unit id)I agree with that. But that change is allowed only once through the application. Any change in that is logged and the application is configured to stop running if there is a change made manually in the table. And I also know that this hurdle can be overcome by a smartie, but I guess you cannot cope with all the situations.
your different peices per pack is like miles of distance per hour and kilometers of distance per hour. they both measure the same thing (distance over time) but are different units of measure. just like your pieces per pack. 12 pieces per pack and 20 pieces per pack both measure the same thing (Pieces per pack) but are different.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 6, 2012 at 9:06 am
capn.hector (4/6/2012)
jewel.sacred (4/6/2012)
capn.hector (4/6/2012)
to lynn's point. if 6 months after you start using this you have to change the pieces per pack you have to make sure to update every item using that new measure. if you have 20 items and only change 18 you have errors in your database. if you have 20 items that are changing in my and lynn's setup but not all 12 peices to a pack are changing, you add a new unit of measure for the 20 items that are changing (or if you all ready have an x pieces per pack unit that they would fit you change the unit id)I agree with that. But that change is allowed only once through the application. Any change in that is logged and the application is configured to stop running if there is a change made manually in the table. And I also know that this hurdle can be overcome by a smartie, but I guess you cannot cope with all the situations.
your different peices per pack is like miles of distance per hour and kilometers of distance per hour. they both measure the same thing (distance over time) but are different units of measure. just like your pieces per pack. 12 pieces per pack and 20 pieces per pack both measure the same thing (Pieces per pack) but are different.
This is why we keep them per item basis 🙂 We cannot afford so many units of measure.
April 6, 2012 at 9:11 am
jewel.sacred (4/6/2012)
capn.hector (4/6/2012)
jewel.sacred (4/6/2012)
capn.hector (4/6/2012)
to lynn's point. if 6 months after you start using this you have to change the pieces per pack you have to make sure to update every item using that new measure. if you have 20 items and only change 18 you have errors in your database. if you have 20 items that are changing in my and lynn's setup but not all 12 peices to a pack are changing, you add a new unit of measure for the 20 items that are changing (or if you all ready have an x pieces per pack unit that they would fit you change the unit id)I agree with that. But that change is allowed only once through the application. Any change in that is logged and the application is configured to stop running if there is a change made manually in the table. And I also know that this hurdle can be overcome by a smartie, but I guess you cannot cope with all the situations.
your different peices per pack is like miles of distance per hour and kilometers of distance per hour. they both measure the same thing (distance over time) but are different units of measure. just like your pieces per pack. 12 pieces per pack and 20 pieces per pack both measure the same thing (Pieces per pack) but are different.
This is why we keep them per item basis 🙂 We cannot afford so many units of measure.
so its legacy that you are stuck doing it this way. its this way now so it has to be this way when we move to the new system?
in that case i recind all remarks on normalizing since some mountians are easier to go around and come back to later. i would still look at normalizing as soon as you can. you cant afford not to in the long run.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply