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)
 SQL Query for two table analysing
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sureshprpt
Starting Member

India
32 Posts

Posted - 02/18/2013 :  11:57:29  Show Profile  Reply with Quote
Hi,

I have two tables , in the first table Itemcode and qty are available and in another cost are available .But the issue is , In the cost table i have two cost method based on my inventory cost 02&07.
Now i want 07 cost for the itemcode available in table.In some codes, 07 cost was not available in the table 2. If write the query for this report, If the code not having the 07 cost in the table 2 was not appearing in the report.But if is not available , cost should be 0 in the report.request to provide the query to my report. The examples are mentioned below with my output

Table 1

Code_no Qty
123 1
124 3
125 6

Table 2

Code_no Cost_type Cost
123 07 45
123 02 10
124 02 5
125 02 10
125 07 15

My out put is

Code_no qty 07Cost
123 1 45
124 3 0
125 6 15

Thanks

Regards
Suresh


Thanks & Regards
Suresh

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/18/2013 :  12:18:00  Show Profile  Reply with Quote
SELECT
	a.Code_no,
	a.Qty,
	ISNULL(b.Cost,0) AS [07Cost]
FROM
	Table1 a
	LEFT JOIN Table2 b ON 
		a.Code_no = b.Code_no AND b.Cost_type = '07';
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:18:32  Show Profile  Reply with Quote

SELECT t1.*, COALESCE(t2.Cost,0) AS Cost
FROM table1 t1
LEFT JOIN table2 t2
On t2.Code_no = t1.Code_no
AND t2.Cost_type='07'


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

Go to Top of Page

sureshprpt
Starting Member

India
32 Posts

Posted - 02/18/2013 :  12:29:49  Show Profile  Reply with Quote
Hi Visakh,

This is my query
SELECT F3111.WMDOCO,F3111.WMDCTO,F3111.WMCPIT,F3111.WMCPIL,F3111.WMUORG/1000,F3111.WMTRQT/1000,F3111.WMUM,COALESCE(F4105.COUNCS,0) FROM P2DTAA/F3111 LEFT JOIN F4105 ON F3111.WMMCU=F4105.COMCU AND F3111.WMCPIT = F4105.COITM WHERE (F3111.WMDCTO = 'W2') AND (F3111.WMMCU LIKE '%ITM') AND (F3111.WMDOCO = 13600191)AND (F4105.COLEDG ='07').

Bus still code which not contain 07 cost was not appeared in the report.

Thanks

By
suresh

Thanks & Regards
Suresh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:34:03  Show Profile  Reply with Quote
it should be


SELECT F3111.WMDOCO,F3111.WMDCTO,F3111.WMCPIT,F3111.WMCPIL,F3111.WMUORG/1000,F3111.WMTRQT/1000,F3111.WMUM,COALESCE(F4105.COUNCS,0) 
FROM P2DTAA/F3111 
LEFT JOIN F4105 ON F3111.WMMCU=F4105.COMCU AND F3111.WMCPIT = F4105.COITM 
AND (F4105.COLEDG ='07'
WHERE (F3111.WMDCTO = 'W2') AND (F3111.WMMCU LIKE '%ITM') AND (F3111.WMDOCO = 13600191)).


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

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:34:45  Show Profile  Reply with Quote
see the reason here

http://weblogs.sqlteam.com/jeffs/archive/2007/05/14/criteria-on-outer-joined-tables.aspx

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

Go to Top of Page

sureshprpt
Starting Member

India
32 Posts

Posted - 02/18/2013 :  12:40:14  Show Profile  Reply with Quote
Thanks Visakh,Now i can able to capture in my report.

Let me know your email ID , if any doubts i will send mail directly to you .

Thanks

Suresh

Thanks & Regards
Suresh
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/18/2013 :  12:43:25  Show Profile  Reply with Quote
you can post it here itself as that will make sure other experts can also see and give you quick solution even if I'm not available.

------------------------------------------------------------------------------------------------------
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.08 seconds. Powered By: Snitz Forums 2000