?
Solved

SQL query two tables with some math?

Posted on 2011-10-23
13
Medium Priority
?
218 Views
Last Modified: 2012-05-12
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.
0
Comment
Question by:Grogel
  • 5
  • 3
  • 3
  • +1
13 Comments
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 668 total points
ID: 37014448
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
 

Author Comment

by:Grogel
ID: 37014476
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
 

Author Comment

by:Grogel
ID: 37014497
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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 37014520
Sample data for Table1 and Table2 with expected output would be more clearer

Regards
0
 
LVL 60

Assisted Solution

by:Kevin Cross
Kevin Cross earned 668 total points
ID: 37014537
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
 
LVL 4

Expert Comment

by:Ambusy
ID: 37014630
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
 
LVL 4

Assisted Solution

by:Ambusy
Ambusy earned 664 total points
ID: 37014639
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
 

Author Comment

by:Grogel
ID: 37014719
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37014774
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
 

Author Comment

by:Grogel
ID: 37014884
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 37014949
@mwvisa1 - With respect, shall I ask you - what you meant by "fake ones suppied earlier by Raj" ?

Raj
0
 

Author Comment

by:Grogel
ID: 37014963
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 37015332
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question