Query too complex

Hi all,
can someone help with the following problem?
TBL_PRODUCTS
--
Prod_code
A
B
C
D
...
TBL_FCST
--
Prod_code FCST_QTY FCST_GRP FCST_PERIOD
A 10 1 1
A 20 2 1
A 30 3 1
A 10 1 2
A 20 2 2
A 30 3 2
B 10 1 1
B 20 2 1
B 30 3 1
B 10 1 2
B 20 2 2
B 30 3 2
TBL_SALES
--
Prod_CODE Sales_date sales_quantity cust_code
A 01-jan-05 5 100
A 01-jan-05 10 105
A 02-jan-05 5 110
B 01-jan-05 15 100
B 01-jan-05 15 105
B 02-jan-05 5 110
...
TBL_STOCK
--
Prod_code actualstock
A 500
B 400
What I would like to get back:
Sales versus fcst by product for specific period (in this case 1 (1=jan2005)
prod_code fcst_qty sales_quantity actualstock
A 60 20 500
B 60 35 400
Hope someone can give me a hand here.
Greetings EricTry,
select
p.Prod_code,
(select sum(a.FCST_QTY) from TBL_FCST as a where a.Prod_code = p.Prod_code
and a.FCST_PERIOD = 1) as fcst_qty,
(select sum(b.sales_quantity) from TBL_SALES as b where b.Prod_CODE =
p.Prod_code and b.Sales_date >= '20050101' and b.Sales_date < '20050201') as
sales_quantity,
s.actualstock
from
TBL_PRODUCTS as p
inner join
TBL_STOCK as s
on p.Prod_code = s.Prod_code
AMB
"ericvdb" wrote:

> Hi all,
> can someone help with the following problem?
> TBL_PRODUCTS
> --
> Prod_code
> A
> B
> C
> D
> ...
> TBL_FCST
> --
> Prod_code FCST_QTY FCST_GRP FCST_PERIOD
> A 10 1 1
> A 20 2 1
> A 30 3 1
> A 10 1 2
> A 20 2 2
> A 30 3 2
> B 10 1 1
> B 20 2 1
> B 30 3 1
> B 10 1 2
> B 20 2 2
> B 30 3 2
> TBL_SALES
> --
> Prod_CODE Sales_date sales_quantity cust_code
> A 01-jan-05 5 100
> A 01-jan-05 10 105
> A 02-jan-05 5 110
> B 01-jan-05 15 100
> B 01-jan-05 15 105
> B 02-jan-05 5 110
> ....
> TBL_STOCK
> --
> Prod_code actualstock
> A 500
> B 400
> What I would like to get back:
> Sales versus fcst by product for specific period (in this case 1 (1=jan200
5)
> prod_code fcst_qty sales_quantity actualstock
> A 60 20 500
> B 60 35 400
> Hope someone can give me a hand here.
> Greetings Eric
>
>|||Great, thanks it works.
Now how can i limit the result with codes that only exist in the table
TBL_FCST ?
Eric
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:94399F41-859C-4576-B6BA-09CBA5FB521F@.microsoft.com...
> Try,
> select
> p.Prod_code,
> (select sum(a.FCST_QTY) from TBL_FCST as a where a.Prod_code = p.Prod_code
> and a.FCST_PERIOD = 1) as fcst_qty,
> (select sum(b.sales_quantity) from TBL_SALES as b where b.Prod_CODE =
> p.Prod_code and b.Sales_date >= '20050101' and b.Sales_date < '20050201')
as
> sales_quantity,
> s.actualstock
> from
> TBL_PRODUCTS as p
> inner join
> TBL_STOCK as s
> on p.Prod_code = s.Prod_code
>
> AMB
>
> "ericvdb" wrote:
>
(1=jan2005)|||Use clause WHERE with EXISTS.
select
p.Prod_code,
(select sum(a.FCST_QTY) from TBL_FCST as a where a.Prod_code = p.Prod_code
and a.FCST_PERIOD = 1) as fcst_qty,
(select sum(b.sales_quantity) from TBL_SALES as b where b.Prod_CODE =
p.Prod_code and b.Sales_date >= '20050101' and b.Sales_date < '20050201') as
sales_quantity,
s.actualstock
from
TBL_PRODUCTS as p
inner join
TBL_STOCK as s
on p.Prod_code = s.Prod_code
where
exists(select * from TBL_FCST as c where c.Prod_code = p.Prod_code and
c.FCST_PERIOD = 1)
AMB
"ericvdb" wrote:

> Great, thanks it works.
> Now how can i limit the result with codes that only exist in the table
> TBL_FCST ?
> Eric
> "Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in messa
ge
> news:94399F41-859C-4576-B6BA-09CBA5FB521F@.microsoft.com...
> as
> (1=jan2005)
>
>|||select a.Prod_Code,b.FCST_QTY ,a.Sales_Quantity,c.actualstock
from (select prod_code,sum(sales_quantity) as sales_quantity from TBL_SALES
where Sales_date='01-jan-05' group by prod_code ) as a
inner join (select Prod_code,sum(FCST_QTY) as FCST_QTY from TBL_FCST where
FCST_PERIOD = 1 group by Prod_code) as b
on a. prod_code=b. prod_code
inner join TBL_STOCK as c on a.prod_code=c. prod_code
Babu M K
Comat Technologies Pvt. Ltd.
"ericvdb" <eric.vdb@.gmail.com> wrote in message
news:uFmeCC4EFHA.3244@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> can someone help with the following problem?
> TBL_PRODUCTS
> --
> Prod_code
> A
> B
> C
> D
> ...
> TBL_FCST
> --
> Prod_code FCST_QTY FCST_GRP FCST_PERIOD
> A 10 1 1
> A 20 2 1
> A 30 3 1
> A 10 1 2
> A 20 2 2
> A 30 3 2
> B 10 1 1
> B 20 2 1
> B 30 3 1
> B 10 1 2
> B 20 2 2
> B 30 3 2
> TBL_SALES
> --
> Prod_CODE Sales_date sales_quantity cust_code
> A 01-jan-05 5 100
> A 01-jan-05 10 105
> A 02-jan-05 5 110
> B 01-jan-05 15 100
> B 01-jan-05 15 105
> B 02-jan-05 5 110
> ...
> TBL_STOCK
> --
> Prod_code actualstock
> A 500
> B 400
> What I would like to get back:
> Sales versus fcst by product for specific period (in this case 1
(1=jan2005)
> prod_code fcst_qty sales_quantity actualstock
> A 60 20 500
> B 60 35 400
> Hope someone can give me a hand here.
> Greetings Eric
>