Friends,
I had written the following query;
select Ar.Factory_Name,Ar.District_Name,Sum(Production) Prod,SUM(TA) TEA,
case when Sum(Production)>0 then (SUM(Production)/SUM(TA)) else 0 end Yield
from
(select Factory_Name,District_Name,SUM(convert(numeric(18,2),tea_area)) TA from factory_registration fr join growers_login gl on gl.Dum_ID=fr.dum_id join
growers_communication_addr gca on gca.Dum_ID=fr.dum_id join taluk_master tm on tm.Taluk_ID=gca.Taluk_ID
join district_master dm on dm.District_ID=tm.District_ID join state_master sm on sm.State_ID=dm.State_ID
where sm.State_Name='XYZ'
and gl.Growers_Status='Approved' and fr.factory_type='EF' group by Factory_Name,District_Name) Ar
join
(select Factory_Name,District_Name,SUM(case when ctc_qty is null then 0 else ctc_qty end+
case when orthodox_qty is null then 0 else orthodox_qty end+
case when greentea_qty is null then 0 else greentea_qty end+
case when organic_qty is null then 0 else organic_qty end+
case when othertea_qty is null then 0 else othertea_qty end) Production from factory_teamfgqty tf join factory_registration fr on fr.dum_id=tf.dum_id
join growers_login gl on gl.Dum_ID=fr.dum_id join growers_communication_addr gca on gca.Dum_ID=fr.dum_id join taluk_master tm on tm.Taluk_ID=gca.Taluk_ID
join district_master dm on dm.District_ID=tm.District_ID join state_master sm on sm.State_ID=dm.State_ID where mfg_month between '4/1/2015' and '3/1/2016'
and sm.State_Name='XYZ' and gl.Growers_Status='Approved' and fr.factory_type='EF'
group by Factory_Name,District_Name) Pr
on Ar.factory_name=Pr.factory_name and Ar.District_Name=Pr.District_Name group by Ar.factory_name,Ar.District_Name
Subquery-1 Returns 275 Data
Subquery-2 Returns 256 Data due to some data not available.
When I execute whole query, it returns 256 rows only but i need 275 rows.
If Subquery-2 doesn't return any value, the query need to put "0". So I can get 275 rows.
Please help me to get the 275 data.
Thanks in advance.
Guna