question

tom.maher88 avatar image
tom.maher88 asked

Count based on results of existing query

Hi All,

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

Thanks

Tom

querycountresults
sql.png (24.0 KiB)
sql-2.png (26.5 KiB)
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
lokeshlehkara avatar image
lokeshlehkara answered

@tom.maher88

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.

https://docs.microsoft.com/en-us/sql/relational-databases/performance/subqueries?view=sql-server-2017

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.