Query for to capture single value from mutliple

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Query for to capture single value from mutliple

Author

Topic

sureshprpt
Starting Member

33 Posts

Posted - 2013-02-19 : 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

52326 Posts

Posted - 2013-02-19 : 00:41:47

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sureshprpt
Starting Member

33 Posts

Posted - 2013-02-19 : 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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 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

33 Posts

Posted - 2013-02-19 : 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

52326 Posts

Posted - 2013-02-19 : 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

33 Posts

Posted - 2013-02-19 : 05:48:51

Ok Thank you, now i got it

Thanks & Regards
Suresh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-19 : 05:56:28

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources