Pls Help to Get Result..

RajeshTaunk · #1 (**permalink**) 02-27-10, 16:49

Hi Everyone..

I have 3 Tables

First is Prodm
---------------------------
PdID| Name |OPStock
----------------------------
1 | aaaa | 10.00
----------------------------
2 | bbbb | 20.00
----------------------------
3 | cccc | 50.00
----------------------------
4 | dddd | 30.00
----------------------------
5 | eeee | 40.00
----------------------------

Second Is Production
---------------------
PdId |Date |Production
-------------------------------
2 |01/01/2010|100.00
-------------------------------
5 |01/01/2010|200.00
-------------------------------
1 |01/01/2010|100.00
-------------------------------

Third IS Sales
-------------
PdID|Date | Sales
----------------------
1 |01/01/2010| 5.00
----------------------
1 |01/02/2010|10.00
----------------------
3 |01/02/2010|50.00
----------------------

Now SQL Should Display Result as under..

PdId |Name|OpStk|ProdQ|Sales|CLStock

0001|aaaa |10.00|100.00|15.00|095.00
0002|bbbb |20.00|100.00|00.00|120.00
0003|cccc |50.00|000.00|50.00|000.00
0004|dddd |30.00|000.00|30.00|000.00
0005|eeee |40.00|200.00|00.00|240.00

Hope I will get help from u all..

Rajesh

r937 · #2 (**permalink**) 02-27-10, 17:01

Code:

SELECT m.PdId 
     , m.Name
     , m.OpStk
     , COALESCE(p.ProdQ,0) AS ProdQ
     , COALESCE(s.Sales,0) AS Sales
     , COALESCE(p.ProdQ,0) -
       COALESCE(s.Sales,0) AS CLStock
  FROM Prodm  AS m
LEFT OUTER
  JOIN Production AS p
    ON p.PdId = m.PdId
LEFT OUTER
  JOIN ( SELECT PdId
              , SUM(Sales) AS Sales
           FROM Sales
         GROUP
             BY PdId ) AS s
    ON s.PdId = m.PdId

Internet Services

Web Development

Digital Marketing

Consumer Tech