| Author |
 Topic  |
|
|
sureshprpt
Starting Member
India
25 Posts |
 Posted - 02/19/2013 : 00:34:41
|
Hi,
I have two table which contain the document number is common for both table .In the one table the total qty consumed was avaliable , in another table the qty and value was avaliable in lot wise basis.If join the table1 & table2 , two time qty was appeared ,due to entry in table 2 lot wise. Request to provide query to captured total value based on total qty.
Example data are mentioned below
Table1
Doc_no Customer Qty
1234 XXX 1000
1278 YYY 800
Table_2
Doc_no qty lotno unit_cost total_cost
1234 500 2012 2 1000
1234 500 2013 2 1000
1278 500 2011 10 5000
1278 300 2013 10 3000
The output is
Doc_no Customer Qty unit cost
1234 xxx 1000 2
1278 yyy 800 10
or
Doc_no Customer Qty totalcost
1234 xxx 1000 2000
1278 yyy 800 8000
Thanks
Suesh
Thanks & Regards
Suresh |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/19/2013 : 00:41:47
|
SELECT t1.*,t2.total_cost
FROM Table1 t1
INNER JOIN (SELECT Doc_No,SUM(total_cost) AS total_cost
FROM Table2
GROUP BY Doc_No
)t2
ON t2.Doc_No = t1.Doc_No
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
sureshprpt
Starting Member
India
25 Posts |
Posted - 02/19/2013 : 01:16:39
|
Thanks Visakh,
I try this query and unable to locate on it, because the data selection is little bit different compare with original entry . The current entry in tables are mention below with ouput.Request to provide the query.
Tabe1
WMDOCO WMDCTO WMCPIT WMCPIL WMUM WMTRQT
13600184 W2 641414 RMBL00003 KG 6132
13600184 W2 642011 RMSP00001 KG 8468
Table2
ILITM ILLITM ILLOCN ILLOTN ILDCTO ILDOCO ILTRQT ILUNCS ILPAID
642011 RMSP00001 ITMSHOP 201301120001 W2 13600184 5200 2.16 11232
641414 RMBL00003 ITMSHOP 201301270001 W2 13600184 4000 34.5758 138303.2
642011 RMSP00001 ITMSHOP 201301120001 W2 13600184 3268 2.16 7058.88
641414 RMBL00003 ITMSHOP 201301270001 W2 13600184 2132 34.5758 73715.6056
Output required
WMDOCO WMDCTO WMCPIT WMCPIL WMUM WMTRQT ILPAID
13600184 W2 641414 RMBL00003 KG 6132 18290.88
13600184 W2 642011 RMSP00001 KG 8468 212018.8056
Thanks
Thanks & Regards
Suresh |
Edited by - sureshprpt on 02/19/2013 01:18:17 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/19/2013 : 01:25:03
|
I dont know what difficulty you faced as its straightforward application of what i gave to your query
see below
SELECT t1.*,t2.ILPAID
FROM Tabe1 t1
INNER JOIN (SELECT ILITM,SUM(ILPAID) AS ILPAID
FROM table2
GROUP BY ILITM)t2
ON t2.ILITM = t1.WMCPIT
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sureshprpt
Starting Member
India
25 Posts |
Posted - 02/19/2013 : 02:57:42
|
Hi Visakh,
When i try this query , it ILPAID is not in table.
Please find the query
SELECT F3111.WMDOCO,F3111.WMDCTO,F3111.WMCPIT,F3111.WMCPIL,F3111.WMUORG/1000,F3111.WMTRQT/1000,F3111.WMUM,F4111.ILPAID FROM P2DTAA/F3111 INNER JOIN (SELECT F4111.ILITM,SUM(F4111.ILPAID) FROM F4111 GROUP BY F4111.ILITM) F4111 ON F3111.WMCPIT =F4111.ILITM AND F3111.WMDOCO = F4111.ILDOCO AND F3111.WMDCTO = F4111.ILDCTO WHERE (F3111.WMDCTO = 'W2') AND (F3111.WMMCU LIKE '%ITM') AND (F3111.WMDOCO = 13600184)
Thanks
Thanks & Regards
Suresh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/19/2013 : 04:19:08
|
see how i've used it. you should define an alias for sum column.
Compare and correct your query yourselves. I'm not going to spoonfeed you!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sureshprpt
Starting Member
India
25 Posts |
Posted - 02/19/2013 : 05:48:51
|
Ok Thank you, now i got it
Thanks & Regards
Suresh |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/19/2013 : 05:56:28
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Query for to capture single value from mutliple
