Member_2_4286571
asked on
SQL query two tables with some math?
Hi.
This is the story.
I have data in two tables.
Table One:
ID, DATE, Qty
Table two:
IDN,ID,FACTOR
------------------
I need query which will give me (SUM(QTY) where DATE = 'User input') * Factor) where IDN ='User input'
In other words: I need Summary of QTY data on specific date multiplied with factor data of IDN (which is suplied by user). There can be different ID, using same IDN FACTOR data.
ID is the same unique data in both tables
User will provide IDN and DATE value.
This is probably simpler than i realize, but today my brain seems to stop functioning.
English is not my native language so i hope you understand all this.
Thank you.
This is the story.
I have data in two tables.
Table One:
ID, DATE, Qty
Table two:
IDN,ID,FACTOR
------------------
I need query which will give me (SUM(QTY) where DATE = 'User input') * Factor) where IDN ='User input'
In other words: I need Summary of QTY data on specific date multiplied with factor data of IDN (which is suplied by user). There can be different ID, using same IDN FACTOR data.
ID is the same unique data in both tables
User will provide IDN and DATE value.
This is probably simpler than i realize, but today my brain seems to stop functioning.
English is not my native language so i hope you understand all this.
Thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Also I have to put ID somewhere because that is only variable that is the same in both tables.
Filter that by Date, then I can search table 2 only by ID value that is found also in table 1. Then filter all that by IDN.
At least that's the picture in my brain, i guess there is always better ways.
Filter that by Date, then I can search table 2 only by ID value that is found also in table 1. Then filter all that by IDN.
At least that's the picture in my brain, i guess there is always better ways.
Sample data for Table1 and Table2 with expected output would be more clearer
Regards
Regards
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why so complicated?
SELECT sum(qty * factor) FROM t1, t2 where t1.id = t2.id and t2.idn=userValue and t1.date = "UserValue"Group by someColumnSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
At this time i am getting same results (0.0000) 23 rows with this code:
SET @Date = '12/23/2011'
SET @IDN = 'ABC'
SELECT ISNULL(q.SumQty, 0) * f.Factor
FROM Table2 f
LEFT OUTER JOIN (
SELECT ID, SUM(Qty) AS SumQty
FROM Table1
WHERE Date >= @Date
AND Date < DATEADD(DD, +1, @Date)
GROUP BY ID
) q ON q.ID = f.ID
WHERE f.IDN = @IDN
Other examples gives me NULL as result.
I guess that i explained something wrong way or I am using this code in wrong way.
Let me explain in greater detail.
This is POS aplication. I need to create report to show how much Ingredients was used when some Food(ID) was made and sold on some date/dates and selected Ingredient (IDN)
Some Food has more than one ingredient, and some ingredients are used in more than one food.
Table 1 covers sales data (FoodID (ID), Quantity(QTY), Date(DATE) VAT....)
Table 2 covers Ingredients as what Food(ID) is using which Ingredient(IDN), and how much of it (Factor).
Let's say that User want's to know how much Sugar was used on 23/10/2011
Sales Data(Table 1) tells us that there was 33(QTY) Tea(tea ID is 202) made and sold today, and according Ingredients table (table2), Tea is using 0.005(Factor) kilograms of sugar, 0.010 lemon...etc
(QTY * Factor) or 33 * 0.005=0.165 Kg of sugar was used today.
If there was also one pancake and in Ingredients table says that pancake is using 0.020 of sugar then end result is 0.185.
I need sum(qty) group by ID because there will be a lot of other things in sales data (Soda, Bread...)
Then check if any of that Grouped data is using sugar(IDN) and if it does take Factor and multiply with summed QTY.
User knows what Ingredient he/she wants to check, and for what period of time(only dates). Everything else needs to be found with code.
I don't know if this helps, I hope it does.
I can provide exact Table data, but that is not in English so you could have hard time reading those...
P.S. You did help me a lot with this code you all provided. Now i understand much more than before.
SET @Date = '12/23/2011'
SET @IDN = 'ABC'
SELECT ISNULL(q.SumQty, 0) * f.Factor
FROM Table2 f
LEFT OUTER JOIN (
SELECT ID, SUM(Qty) AS SumQty
FROM Table1
WHERE Date >= @Date
AND Date < DATEADD(DD, +1, @Date)
GROUP BY ID
) q ON q.ID = f.ID
WHERE f.IDN = @IDN
Other examples gives me NULL as result.
I guess that i explained something wrong way or I am using this code in wrong way.
Let me explain in greater detail.
This is POS aplication. I need to create report to show how much Ingredients was used when some Food(ID) was made and sold on some date/dates and selected Ingredient (IDN)
Some Food has more than one ingredient, and some ingredients are used in more than one food.
Table 1 covers sales data (FoodID (ID), Quantity(QTY), Date(DATE) VAT....)
Table 2 covers Ingredients as what Food(ID) is using which Ingredient(IDN), and how much of it (Factor).
Let's say that User want's to know how much Sugar was used on 23/10/2011
Sales Data(Table 1) tells us that there was 33(QTY) Tea(tea ID is 202) made and sold today, and according Ingredients table (table2), Tea is using 0.005(Factor) kilograms of sugar, 0.010 lemon...etc
(QTY * Factor) or 33 * 0.005=0.165 Kg of sugar was used today.
If there was also one pancake and in Ingredients table says that pancake is using 0.020 of sugar then end result is 0.185.
I need sum(qty) group by ID because there will be a lot of other things in sales data (Soda, Bread...)
Then check if any of that Grouped data is using sugar(IDN) and if it does take Factor and multiply with summed QTY.
User knows what Ingredient he/she wants to check, and for what period of time(only dates). Everything else needs to be found with code.
I don't know if this helps, I hope it does.
I can provide exact Table data, but that is not in English so you could have hard time reading those...
P.S. You did help me a lot with this code you all provided. Now i understand much more than before.
Are you changing the date and ID parameters to match your environment? If you use the fake ones supplied earlier by Raj, then of course it won't work. Try his solution again with proper values. Same with mine.
ASKER
Thank you all for your patience. Problem was that I made some mistakes in adjusting code for my needs. Thank you once again. Too bad I can't give more points to at least fairly compensate all of you for having so much patience.
@mwvisa1 - With respect, shall I ask you - what you meant by "fake ones suppied earlier by Raj" ?
Raj
Raj
ASKER
I believe He meant this (i think) and he was right if he did mean that one.
SET @Date = '12/23/2011'
SET @IDN = 'ABC'
But mistake was in correct Date format passed from VB, not in those details (I did change them). Acctually you all provided great support, and you are all correct. You was on the solution, but i messed up giving wrong feedback (fighting with english, sometimes hard to find words...sorry bout that)
At the end all solutions was good. I hope if there is no hard feelings if i split the points among three of you.
SET @Date = '12/23/2011'
SET @IDN = 'ABC'
But mistake was in correct Date format passed from VB, not in those details (I did change them). Acctually you all provided great support, and you are all correct. You was on the solution, but i messed up giving wrong feedback (fighting with english, sometimes hard to find words...sorry bout that)
At the end all solutions was good. I hope if there is no hard feelings if i split the points among three of you.
Exactly. Raj, December 23, 2011 has not occurred yet, so it is a "fake" or "example" value for date, right? So is 'ABC'. To get this to work, "real" values from the actual data has to be used. ;) I was supporting that your query was actually working, but because the supplied date does not exist then it was always going to the default supplied to ISNULL() and resulting in 23 rows of 0.
Grogel, I am glad you picked up what I meant.
Best regards and happy coding,
Kevin
Grogel, I am glad you picked up what I meant.
Best regards and happy coding,
Kevin
ASKER
This data can't be zero because there is qty and factor as positive numbers although factor is decimal number. Can you help me little bit more?