SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Query for to capture single value from mutliple
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sureshprpt
Starting Member

India
32 Posts

Posted - 02/19/2013 :  00:34:41  Show Profile  Reply with Quote
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
52317 Posts

Posted - 02/19/2013 :  00:41:47  Show Profile  Reply with Quote

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/

Go to Top of Page

sureshprpt
Starting Member

India
32 Posts

Posted - 02/19/2013 :  01:16:39  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  01:25:03  Show Profile  Reply with Quote
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/

Go to Top of Page

sureshprpt
Starting Member

India
32 Posts

Posted - 02/19/2013 :  02:57:42  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  04:19:08  Show Profile  Reply with Quote
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/

Go to Top of Page

sureshprpt
Starting Member

India
32 Posts

Posted - 02/19/2013 :  05:48:51  Show Profile  Reply with Quote
Ok Thank you, now i got it

Thanks & Regards
Suresh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/19/2013 :  05:56:28  Show Profile  Reply with Quote
welcome

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.22 seconds. Powered By: Snitz Forums 2000