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.
GrogelAsked:
Who is Participating?
 
Rajkumar GsConnect With a Mentor Software EngineerCommented:
DECLARE @Date DATETIME, @IDN VARCHAR(20)
SET @Date = '12/23/2011'
SET @IDN = 'ABC'
SELECT ISNULL((SELECT SUM(Qty) from Table1 WHERE Date = @Date), 0) * ISNULL(Factor, 0)
FROM Table2 
WHERE IDN = @IDN

Open in new window


Regards
0
 
GrogelAuthor Commented:
Thank you for reply, i am getting 23 rows and in every row there is 0.0000.

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?
0
 
GrogelAuthor Commented:
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.
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
Rajkumar GsSoftware EngineerCommented:
Sample data for Table1 and Table2 with expected output would be more clearer

Regards
0
 
Kevin CrossConnect With a Mentor Chief Technology OfficerCommented:
Think you are close Raj. The missing pieces is the connection of ID and whether or not the date has timestamps.

This may work better:
DECLARE @Date DATETIME, @IDN VARCHAR(20)
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
;

Open in new window


If the final sum should be for all rows matching the IDN, then this should be easier.
DECLARE @Date DATETIME, @IDN VARCHAR(20)
SET @Date = '12/23/2011'
SET @IDN = 'ABC'

SELECT SUM(q.Qty * f.Factor)
FROM Table2 f
JOIN Table1 q ON q.ID = f.ID
WHERE f.IDN = @IDN
AND q.Date >= @Date
AND q.Date < DATEADD(DD, +1, @Date)
;

Open in new window

0
 
AmbusyCommented:
why so complicated?

SELECT sum(qty * factor) FROM t1, t2 where t1.id = t2.id and t2.idn=userValue and t1.date = "UserValue"

Open in new window

with a
Group by someColumn

Open in new window

if it's not over the entire table
0
 
AmbusyConnect With a Mentor Commented:
want it for each different id?:
SELECT t1.id, sum(qty * factor) FROM t1, t2 where t1.id = t2.id and t2.idn='userValue' and t1.date = 'UserValue' Group by t1.id

Open in new window

0
 
GrogelAuthor Commented:
At this time i am getting same results (0.0000) 23 rows with this code:


Open in new window

DECLARE @Date DATETIME, @IDN VARCHAR(20)
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

Open in new window


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.


0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
GrogelAuthor Commented:
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.
0
 
Rajkumar GsSoftware EngineerCommented:
@mwvisa1 - With respect, shall I ask you - what you meant by "fake ones suppied earlier by Raj" ?

Raj
0
 
GrogelAuthor Commented:
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.
0
 
Kevin CrossChief Technology OfficerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.