But you are not assigning the local variable from inside the CTE.

I know how to do it outside.

I am not really interested in how to get the monthly list. This could be a list of names.

But I am trying to see if I can do one select statement that is executed once and assigned to a local variable that I can use in my outside query and display multiple rows of data that will also have the local variable as one of the columns. In my first, query I got the answer I wanted but I didn't want the query that fills the variable executing multiple times.

I am trying to use one CTE to get some data, then feed that into another CTE that will use that result set to fill a local variable. Then have the outside query display the table from the 1st query along with the variable.

There are other ways of doing it but I am trying to find out if this way can work.

Thanks,

Tom