CTE Query help

Transact-SQL
1

Hello friends

In my query, I need to frame a condition like

The ‘Scheduled start/scheduled end date’ (if available) else 'Earliest Start(if available) else Latest End date’ is of a past date when compared to today’s date, get the count of activities which are having category 'EAS-1F' and status 'TD'
I wrote the query like:

;WITH PlanDateLate
 AS
 (SELECT APlanLt.activityIncId, APlanLt.activitySqlId,
	COUNT(CASE WHEN ISNULL(APlanLt.activityScheduledStartDate,
				ISNULL(APlanLt.activityEarliestStartDate, APlanLt.activityLatestEndDate)) < GETDATE()  THEN activityCode ELSE NULL END) AS PlanDateNullCount
	FROM Activities APlanLt
	INNER JOIN ActivitiesCategories ACTPlanLt 
	ON ACTPlanLt.activityCategoryIncId = APlanLt.activityCategoryIncId
	AND ACTPlanLt.activityCategorySqlId = APlanLt.activityCategorySqlId AND APlanLt.isDeleted=0x0 AND ACTPlanLt.isDeleted=0x0
	AND ACTPlanLt.activityCategoryCode = 'EAS-1F' 
	INNER JOIN ActivitiesStatuses AST ON APlanLt.activityStatusIncId = AST.activityStatusIncId
    AND APlanLt.activityStatusSqlId = AST.activityStatusSqlId AND AST.isDeleted = 0x0 
    AND AST.activityStatusCode = 'TD'
 )

Please confirm whether the query logic work fine.

2

what happens when you run it?

3

It is returning results either 0,1 or NULL for the column PlanDateNullCount.

I want a expert opinion whether the condition is satisfied in the query so that whatever results 0,1 or NULL is getting correct or not.

The ‘Scheduled start/scheduled end date’ (if available) else 'Earliest Start(if available) else Latest End date’ is of a past date when compared to today’s date, get the count of activities which are having category 'EAS-1F' and status 'TD'

4

OK -- a simple reading of the query looks like it might work. Question though: are the columns you're testing date or datetime values. Or does that matter? e.g. if Start Date is today at 10 am but it's now 11 am. should that pass the test?