That's the ticket, thanks so much 🙂

Can you break down the calculation for me. I need to add a "next week" group and doing this works, so the last number...

Thanks for the tips.

With Luis' one, that will take the next 7 days. By "this week", I want the calendar week, so when looking at it today, "this...

It's also the aggregate functions, well COUNT at least. If I strip those out it returns nothing if grouping is true, even with using literal values.

So it seems:

There is no group called Everyone.
If I strip out some of the query to just this, and add 1=2 in the where clause, it still returns a row:

What I can't understand is if I change the 2nd select to column names:
From:

select      'EVY', 'Everyone', cast(0 as bit), 2,
...


To:

select e.EMP_Forename, e.EMP_Surname, cast(0...

The data returned is presented in a web app. I want to return a lit of departments that are bound to a .NET repeater control, which itself has a nested...

PS: I'm doing 2 union all and the FROM statements are almost the same in each, as is some of the WHERE statement.

I've totally binned this idea. I've instead fabricated an additional column which is bit (true if favourite, false if not).

Thanks.  I like alternate options. 🙂

Thanks for the replies guys. I think I'm there:

Direct link: https://www.sqlservercentral/Forums/Uploads/Images/1766297-1.png

Yeah that's what I'm doing.

Direct link: http://i.imgur.com/gczrdrb.png