Hello All.... I am again stuck-up in making a query.... I tried making it.... Please guide further..
Please do let me know if any other data is required....... PLEASE HELP.... ITS URGENT
I am sharing my table-structures and the query...
Table Structure
1. Table Name – Tbl_Entity_Master
| E_CODE | E_NAME | STATUS | ACTIVE | DISDATE | E_TYPE |
| 100 | ABC PvtLtd | Active | 1 | 1-1-2018 | Private |
| 101 | BCD Private Limited | InActive | Null | 1-1-2018 | Public |
| 102 | DEF Co. Ltd | Strike-Off with other things | 1 | 1-1-2018 | Individual |
| 103 | ABC Co. Ltd | Struck-Off with others | 1 | Null | Individual |
| 104 | BCD Co. td | Active | 1 | null | Public |
| 105 | GHI Co. | Amalgagted with others | 1 | Null | Individual |
| 106 | JKL Co. | Handed over to others | Null | Null | Public |
| 107 | MNO Pvt Ltd | Handed over | Null | Null | Individual |
| 108 | PQR Co. Ltd | Active | 1 | Null | Private |
| 109 | PQR Pvt Ltd | Active | 1 | Null | Public |
| 110 | ACB Co. Ltd | Active | 1 | Null | Private |
| 111 | XYZ Pvt Ltd | Active | 1 | Null | Public |
| 112 | IJK Co. Ltd | Active | Null | null | Private |
| 113 | KLM Pvt Ltd | Handed over | Null | Null | Individual |
| 114 | EFG PvtLtd | Handed over | Null | 1-1-2018 | Private |
| 115 | FGH Pvt LTd | Struck-Off | Null | 1-1-2018 | Public |
| 116 | RST Pvt Ltd | Active | 1 | Null | Private |
| 117 | XYZ Co. Ltd | Active | 1 | Null | Public |
| 118 | HIJ Pvt Ltd | Active | 1 | Null | Private |
| 119 | LMO Co. Ltd | Activie | 1 | Null | Private |
| 120 | MNO Co. Ltd | null | 1 | null | Public |
2. TBL_ENTITY_DETAILS
| ID | E_CODE | ACC_CODE | ACC_NAME | SHARES(in%) | CEO_CODE | gref |
| 1 | 100 | 20 | Rohit Joshi | 8 | 10 | Others |
| 2 | 101 | 20 | Rohit Joshi | 8 | 10 | Others |
| 3 | 102 | 20 | Rohit Joshi | 8 | 10 | Others |
| 4 | 103 | 20 | Rohit Joshi | 8 | 10 | Others |
| 5 | 104 | 20 | Rohit Joshi | 8 | 10 | Outside |
| 6 | 105 | 20 | Rohit Joshi | 8 | 10 | Outside |
| 7 | 102 | 30 | Vijay Shah | 8 | 20 | Others |
| 8 | 103 | 30 | Vijay Shah | 8 | 20 | Others |
| 9 | 104 | 30 | Vijay Shah | 5 | 20 | Others |
| 10 | 105 | 30 | Vijay Shah | 5 | 20 | Others |
| 11 | 106 | 30 | Vijay Shah | 5 | 20 | Others |
| 12 | 107 | 30 | Vijay Shah | 5 | 20 | Others |
| 13 | 108 | 50 | Malini Parikh | 5 | 40 | Others |
| 14 | 109 | 50 | Malini Parikh | 5 | 40 | Others |
| 15 | 110 | 50 | Malini Parikh | 5 | 40 | Others |
| 16 | 109 | 50 | Malini Parikh | 5 | 40 | Others |
| 17 | 110 | 50 | Malini Parikh | 5 | 40 | Others |
| 18 | 111 | 50 | Malini Parikh | 6 | 30 | Outside |
| 19 | 112 | 20 | Rohit Joshi | 6 | 30 | Others |
| 20 | 113 | 30 | Vijay Shah | 6 | 30 | Others |
| 21 | 114 | 50 | Malini Parikh | 6 | 30 | Others |
| 22 | 115 | 60 | Shruti Dave | 6 | 30 | Others |
| 23 | 116 | 60 | Shruti Dave | 6 | 44 | Others |
| 24 | 117 | 60 | Shruti Dave | 6 | 44 | Others |
| 25 | 114 | 60 | Shruti Dave | 7 | 44 | Others |
| 26 | 115 | 20 | Rohit Joshi | 7 | 44 | Others |
| 27 | 116 | 20 | Rohit Joshi | 7 | 44 | Others |
| 28 | 117 | 30 | Vijay Shah | 7 | 55 | Others |
| 29 | 118 | 50 | Malini Parikh | 7 | 55 | Others |
| 30 | 119 | 50 | Malini Parikh | 7 | 55 | Others |
| 31 | 120 | Select | NA | Null | 55 | Others |
| 32 | 119 | Select | NA | null | 55 | Others |
3. TBL_CERTIFIED_ENTITIES
| ID | E_CODE | MONTH | YEAR |
| 1 | 100 | MAY | 2018 |
| 2 | 108 | MAY | 2018 |
| 3 | 109 | MAY | 2018 |
| 4 | 110 | MAY | 2018 |
| 5 | 111 | NULL | NULL |
| 6 | 112 | NULL | NULL |
| 7 | 116 | MAY | 2018 |
| 8 | 117 | MAY | 2018 |
| 9 | 118 | MAY | 2018 |
| 10 | 119 | MAY | 2018 |
| 11 | 120 | NULL | NULL |
Total Companies Query –
Select master.e_code, master.e_name
from tb_entity_master master, Tbl_entity_details details
Where master.e_code =details.e_code and
(Master.status =active or Master.Status is null)
AND (master.status not like ‘%Strike %’ or status is null)
AND (master.status not like ‘%Struck %’ or status is null)
AND (master.status not like ‘%Amalgated %’ or status is null)
AND (master.status not like ‘%Handed%’ or status is null)
AND (master.status <> ‘InActive’)
AND master.E_TYPE <> ‘Individual’
AND master.Active = 1
AND master.Disdate is null
AND details.gref <> ‘Outside’
AND details.ACC_CODE <>’Select’
Certified Companies Query
Select master.e_code, master.e_name , ‘Certified’ as CertifiedStatus
from tb_entity_master master, Tbl_entity_details details, TBL_Certified_Companies certified
Where master.e_code =details.e_code and
Master.e_code = certified.e_code and
(Master.status =active or Master.Status is null)
AND (master.status not like ‘%Strike %’ or status is null)
AND (master.status not like ‘%Struck %’ or status is null)
AND (master.status not like ‘%Amalgated %’ or status is null)
AND (master.status not like ‘%Handed%’ or status is null)
AND (master.status <> ‘InActive’)
AND master.E_TYPE <> ‘Individual’
AND master.Active = 1
AND master.Disdate is null
AND details.gref <> ‘Outside’
AND details.ACC_CODE <>’Select’
And certified.Month = ‘May’ AND certified.Year=2018
UNION
Select master.e_code, master.e_name , ‘UNCertified’ as CertifiedStatus
from tb_entity_master master, Tbl_entity_details details, TBL_Certified_Companies certified
Where master.e_code =details.e_code and
Master.e_code = certified.e_code and
(Master.status =active or Master.Status is null)
AND (master.status not like ‘%Strike %’ or status is null)
AND (master.status not like ‘%Struck %’ or status is null)
AND (master.status not like ‘%Amalgated %’ or status is null)
AND (master.status not like ‘%Handed%’ or status is null)
AND (master.status <> ‘InActive’)
AND master.E_TYPE <> ‘Individual’
AND master.Active = 1
AND master.Disdate is null
AND details.gref <> ‘Outside’
AND details.ACC_CODE <>’Select’
And certified.Month is null AND certified.Year is null
----------
Issue is - The total count of both the queries should be same. But, the count is coming different. I am not getting where I am going wrong.
Please guide further...