Hi sat,
Refer below query.
SQL
CREATE TABLE #Company(Companies CHAR(3),DC VARCHAR(10))
INSERT INTO #Company VALUES('Co1','26/04/21')
INSERT INTO #Company VALUES('Co2','26/04/21')
INSERT INTO #Company VALUES('Co3','25/04/21')
INSERT INTO #Company VALUES('Co4','25/04/21')
INSERT INTO #Company VALUES('Co2','25/04/21')
INSERT INTO #Company VALUES('Co3','25/04/21')
INSERT INTO #Company VALUES('Co1','24/04/21')
INSERT INTO #Company VALUES('Co1','22/04/21')
INSERT INTO #Company VALUES('Co2','22/04/21')
INSERT INTO #Company VALUES('Co3','22/04/21')
INSERT INTO #Company VALUES('Co4','21/04/21')
INSERT INTO #Company VALUES('Co2','20/04/21')
INSERT INTO #Company VALUES('Co3','20/04/21')
INSERT INTO #Company VALUES('Co5','19/03/21')
INSERT INTO #Company VALUES('Co1','19/02/21')
SELECT Companies,MAX(DC) DC,
(SELECT COUNT(DC) FROM #Company c2 WHERE DATEDIFF(DAY,TRY_PARSE(DC AS DATE USING 'en-in'),GETDATE()) > 30 AND c1.Companies=c2.Companies) Old
FROM #Company c1
GROUP BY Companies
DROP TABLE #Company
Output
| Companies |
DC |
Old |
| Co1 |
26/04/21 |
1 |
| Co2 |
26/04/21 |
0 |
| Co3 |
25/04/21 |
0 |
| Co4 |
25/04/21 |
0 |
| Co5 |
19/03/21 |
1 |
