| Author |
 Topic  |
|
|
sureshprpt
Starting Member
India
25 Posts |
 Posted - 02/18/2013 : 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
Flowing Fount of Yak Knowledge
1518 Posts |
Posted - 02/18/2013 : 12:18:00
|
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'; |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/18/2013 : 12:18:32
|
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/
|
|
|
|
sureshprpt
Starting Member
India
25 Posts |
Posted - 02/18/2013 : 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
India
47173 Posts |
Posted - 02/18/2013 : 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
India
47173 Posts |
|
|
sureshprpt
Starting Member
India
25 Posts |
Posted - 02/18/2013 : 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
India
47173 Posts |
Posted - 02/18/2013 : 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/
|
|
|
| |
Topic |
|
SQL Query for two table analysing
