I am having issue getting the correct data out of this query.
I am trying to fetch ClientID by comparision between 2 table based on Date range
Table A service data
Table B outcome data
Report Requirement – List of the Clients and the total number of clients that have a date entry in Table A but missing entry for that Client in Table B in the date range within the previous six months or in the next 45 days of that date.
Two main field for comparison in the table are ClientID & Date and I am using the below query to get those client IDs from Table A
SELECT DISTINCT B.ClientID,B.Date
FROM Table_B AS B
LEFT JOIN Table_A AS A
ON B.ClientID = A.ClientID
WHERE CAST(B.Date AS date) NOT BETWEEN DATEADD(DAY, -180, CAST(A.Date AS date))
AND DATEADD(DAY, 45, CAST(A.Date AS date))
P.S. I am using all the cast as the dates are in Varchar format as this table is created and populated from Azure.
Could be something really simple but is not striking,