How to count max date row if current status column is 0 or 2? Example Data and Table

Currentstatus   Datetime                  Open_reopen_status  Parent_id
-------------   -----------------------   ------------------  ---------
5                                         0                   214
5                                         1                   214
0               2017-09-25 18:23:07.181   0                   214
5               2017-10-18 00:00:00       0                   214
5                                         0                   214
5                                         1                   214
0               2017-09-25 18:23:07.181   0                   214
5               2017-10-18 00:00:00       0                   214
5               2017-09-25 00:00:00       0                   226
5               2017-09-25 00:00:00       1                   226
0               2018-10-04 18:33:15.762   0                   226
5               2018-10-06 00:00:00       0                   226
5               2017-09-25 00:00:00       1                   226
0               2018-10-08 18:33:15.762   0                   226
5               2019-03-07 08:55:35.772   0                   126900
5               2019-03-27 15:51:28.63    1                   126900
0               2019-03-27 15:51:28.63    0                   126900


Question

I need a count of those rows whose date is maximum and currentstatus=0 along with particular id value.  

E.g., in the above table the count would be 2, because the parent_id 226 and 126900 have a currentstatus=0 and the max date 2018-10-08 and 2019-03-27.
The way I understand your question is, for each Parent_id you want to determine the maximum Datetime and then find the rows that have both the maximum Datetime and the Currentstatus of 0. And finally you want to count the resulting rows.

First of all, you can get the maximum Datetime values using a window aggregate function MAX() OVER ():

SELECT
  Currentstatus,
  Datetime,
  Open_reopen_status,
  Parent_id,
  MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
FROM
  YourTable
;


That will give you a result set like this:

Currentstatus  Datetime                 Open_reopen_status  Parent_id  MaxDatetime
-------------  -----------------------  ------------------  ---------  -----------------------
5                                       0                   214        2017-10-18 00:00:00
5                                       1                   214        2017-10-18 00:00:00
0              2017-09-25 18:23:07.181  0                   214        2017-10-18 00:00:00
5              2017-10-18 00:00:00      0                   214        2017-10-18 00:00:00
5                                       0                   214        2017-10-18 00:00:00
5                                       1                   214        2017-10-18 00:00:00
0              2017-09-25 18:23:07.181  0                   214        2017-10-18 00:00:00
5              2017-10-18 00:00:00      0                   214        2017-10-18 00:00:00
5              2017-09-25 00:00:00      0                   226        2018-10-08 18:33:15.762
5              2017-09-25 00:00:00      1                   226        2018-10-08 18:33:15.762
0              2018-10-04 18:33:15.762  0                   226        2018-10-08 18:33:15.762
5              2018-10-06 00:00:00      0                   226        2018-10-08 18:33:15.762
5              2017-09-25 00:00:00      1                   226        2018-10-08 18:33:15.762
0              2018-10-08 18:33:15.762  0                   226        2018-10-08 18:33:15.762
5              2019-03-07 08:55:35.772  0                   126900     2019-03-27 15:51:28.63
5              2019-03-27 15:51:28.63   1                   126900     2019-03-27 15:51:28.63
0              2019-03-27 15:51:28.63   0                   126900     2019-03-27 15:51:28.63


Use the query as a derived table and apply this filter to it:

WHERE Datetime = MaxDatetime
  AND Currentstatus = 0


which for your example will match these two rows:

Currentstatus  Datetime                 Open_reopen_status  Parent_id  MaxDatetime
-------------  -----------------------  ------------------  ---------  -----------------------
0              2018-10-08 18:33:15.762  0                   226        2018-10-08 18:33:15.762
0              2019-03-27 15:51:28.63   0                   126900     2019-03-27 15:51:28.63


And now it only remains for you to put COUNT(*) in the main SELECT. This is the full query:

SELECT
  COUNT(*) AS CountResult
FROM
  (
    SELECT
      Currentstatus,
      Datetime,
      Open_reopen_status,
      Parent_id,
      MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
    FROM
      YourTable
  ) AS derived
WHERE Datetime = MaxDatetime
  AND Currentstatus = 0
;


Note that if there can be two or more matching rows per Parent_id and you want to count only the different Parent_id values, you need to express that in your query. The easiest way would be to replace COUNT(*) with COUNT(DISTINCT Parent_id):

SELECT
  COUNT(DISTINCT Parent_id) AS CountResult
FROM
  (
    SELECT
      Currentstatus,
      Datetime,
      Open_reopen_status,
      Parent_id,
      MAX(Datetime) OVER (PARTITION BY Parent_id) AS MaxDatetime
    FROM
      YourTable
  ) AS derived
WHERE Datetime = MaxDatetime
  AND Currentstatus = 0
;