I have jobnumber, startdateofjob,enddateofjob in SQL table. If there is ‘1900-01-01 00:00:00.000’ in enddateofjob column, this means it is active job. Other inactive job. I have data for last two years.

This is currently I am using Select jobnumber, startdateofjob,enddateofjob –‘Australia’ as Country–,STARTDT,ACTEND –into #Temp from JOB where (enddateofjob = ‘1900-01-01′ OR enddateofjob >= GETDATE())

How I can find out no of active jobs on certain date(History date) , I need to make this dynamic. Like On date Active jobs 2021-12-28 00:00:00.000 10 (where enddateofjob =’1900-01-01 00:00:00.000′)

2021-12-27 00:00:00.000 5 (where enddateofjob =’1900-01-01 00:00:00.000′) 2021-12-26 00:00:00.000 8 (where enddateofjob =’1900-01-01 00:00:00.000’)

so on …