I have a query which produces the following results:
What I want to do is add a column that for all the "Holiday" records, it returns a count of the number of "Not Holiday" records in the results which have a BelongsToDate which falls between the BelongsToDate and the Plus2Weeks date.
The desired results would look like the below:
Any help would be appreciated
Answer by lokeshlehkara ·
If i understood your question well. Then below should work. Please check.
SELECT *, ( SELECT Count(1) FROM #T as B WHERE (A.Person = B.Person AND B.Name = 'Not Holiday' AND B.BelongsToDate BETWEEN A.BelongsToDate AND A.Plus2Weeks ) ) as [Count] FROM #T as A
--Replace #T with your tablename
Just For reading purpose, Below is the link for detail on subquery.