Hi,
I have written a two query. now i want write in one/single query
One query is getting latest update date of scheme entry. Means which is lastest scheme is updated.
And Second is getting our head officer has been rejected that scheme if he dont’ like or there is any mistake data entry.
So I want display which is latest dated entry done by data entry and rejected entry has been rejected by officer . So data entry can again make/update that rejecte entry or data entery can make change/update until he has not send to upper office to approval or reject. Means i want display both latest entry and data not rejecte/approval from upper officer.
---To get latest update Entry
Select * From NEW_RURAL_MPR_STATEMENT_A A
Inner Join NEW_RURAL_MPR_REGION_MASTER R On R.ID = A.REGION_ID
Inner Join NEW_RURAL_MPR_CIRCLE_MASTER C On C.ID = A.CIRCLE_ID
Inner Join NEW_RURAL_MPR_DIVISION_MASTER D On D.ID = A.DIVISION_ID
Inner Join NEW_RURAL_MPR_DISTRICT_MASTER DIV On DIV.ID = A.DISTRICT_ID
Inner Join NEW_RURAL_MPR_TALUKA_MASTER T On T.ID = A.TALUKA_ID
Inner Join NEW_RURAL_MPR_SCHEME_MASTER S On S.ID = A.NAME_OF_SCHEME
inner join (Select NAME_OF_SCHEME,
(Case When Min(D.MONTH) In(1, 2, 3) Then Max(D.LAST_MODIFIED)
When Min(D.MONTH) In(4,5,6,7,8,9,10,11,12)
Then Max(D.LAST_MODIFIED) Else 0 End) As MaxDate
From NEW_RURAL_MPR_STATEMENT_A D
Group By NAME_OF_SCHEME
) TM on A.LAST_MODIFIED = TM.MaxDate And A.NAME_OF_SCHEME = TM.NAME_OF_SCHEME And S.STATUS = 1
and A.DIVISION_ID = 63
Order By A.LAST_MODIFIED Desc
--To get Data entry is done / Data rejected by Upper Officer/Supervisor
Selcet * from From NEW_RURAL_MPR_STATEMENT_A A
Inner Join NEW_RURAL_MPR_REGION_MASTER R On R.ID = A.REGION_ID
Inner Join NEW_RURAL_MPR_CIRCLE_MASTER C On C.ID = A.CIRCLE_ID
Inner Join NEW_RURAL_MPR_DIVISION_MASTER D On D.ID = A.DIVISION_ID
Inner Join NEW_RURAL_MPR_DISTRICT_MASTER DIV On DIV.ID = A.DISTRICT_ID
Inner Join NEW_RURAL_MPR_TALUKA_MASTER T On T.ID = A.TALUKA_ID
Inner Join NEW_RURAL_MPR_SCHEME_MASTER S On S.ID = A.NAME_OF_SCHEME
inner join (Select NAME_OF_SCHEME,
E.CIRCLE_STATUS
From NEW_RURAL_MPR_STATEMENT_A E
Where (E.CIRCLE_STATUS = 0 OR E.CIRCLE_STATUS = 2)
And (E.ALERT_STATUS = 3)
Group By NAME_OF_SCHEME, CIRCLE_STATUS
) TMM On A.CIRCLE_STATUS = TMM.CIRCLE_STATUS And A.NAME_OF_SCHEME = TMM.NAME_OF_SCHEME And S.STATUS = 1
And A.DIVISION_ID = 55
Order By A.LAST_MODIFIED Desc