SQL Query for two table analysing

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)

SQL Query for two table analysing

Author

Topic

sureshprpt
Starting Member

33 Posts

Posted - 2013-02-18 : 11:57:29

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-18 : 12:18:00

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:18:32

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

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

sureshprpt
Starting Member

33 Posts

Posted - 2013-02-18 : 12:29:49

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:34:03

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/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:34:45

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/

sureshprpt
Starting Member

33 Posts

Posted - 2013-02-18 : 12:40:14

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-18 : 12:43:25

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources