I have some old code consisting of over 30 Union statements, unioning 7/1/15-8/1/15 data according to the below code.  Each block is the same except for the date.  How could I rewrite these 30 union statements into a more concise query?

Each client (people_id) has a distinct agency_id_number.  So the code below totals the number of distinct clients/day and groups them by programs and facilities.

Sample data that you can use to test this report:

IF OBJECT_ID(N'tempdb..#sample_census') IS NOT NULL drop table #sample_census

create table #sample_census
(
start_date datetime,
program_name varchar(25),
facility_name varchar(25),
agency_id_number varchar(25)
)

insert #sample_census values('7/1/15','IHS','Memphis,TN','00001')
insert #sample_census values('7/2/15','OPS','Memphis,TN','00001')
insert #sample_census values('7/3/15','IHS','Nashville,TN','00001')

insert #sample_census values('7/1/15','MMP','Memphis,TN','00002')
insert #sample_census values('7/2/15','IHS','Memphis,TN','00002')
insert #sample_census values('7/3/15','IHS','Nashville,TN','00002')

insert #sample_census values('7/1/15','IHS','Memphis,TN','00003')
insert #sample_census values('7/2/15','IHS','Memphis,TN','00003')
insert #sample_census values('7/3/15','IHS','Nashville,TN','00003')

select * from #sample_census order by agency_id_number, start_date

select distinct 
      '07/01/2015' as date, 
case
      when GETDATE() < '07/01/2015' then '0'
      when GETDATE() >= '07/01/2015' then count(civ.agency_id_number) 
end as census, civ.program_name, civ.profile_name
from dbo.rpt_critical_info_view as civ  with (nolock) 
where program_name in ('Residential Treatment - Agency', 'Community-based Residential Treatment')
      and civ.people_id not in(select people_id from dbo.test_clients)
      and (civ.program_start_date <= '07/01/2015 11:59 PM' and (civ.program_end_date >= '07/01/2015 12:00 AM' or civ.program_end_date is null))
      and (civ.facility_start_date <= '07/01/2015 11:59 PM' and (civ.facility_end_date >= '07/01/2015 12:00 AM' or civ.facility_end_date is null))
Group by civ.program_name, civ.profile_name

UNION ALL
Select distinct 
      '07/02/2015' as date, 
case
      when GETDATE() < '07/02/2015' then '0'
      when GETDATE() >= '07/02/2015' then count(civ.agency_id_number) 
end as census, civ.program_name, civ.profile_name
from dbo.rpt_critical_info_view as civ  with (nolock) 
where program_name in ('Residential Treatment - Agency', 'Community-based Residential Treatment')
      and civ.people_id not in(select people_id from dbo.test_clients)
      and (civ.program_start_date <= '07/02/2015 11:59 PM' and (civ.program_end_date >= '07/02/2015 12:00 AM' or civ.program_end_date is null))
      and (civ.facility_start_date <= '07/02/2015 11:59 PM' and (civ.facility_end_date >= '07/02/2015 12:00 AM' or civ.facility_end_date is null))
Group by civ.program_name, civ.profile_name

Well, the simple answer here - use a calendar table. Do a left join from the calendar table with the dates you need to your table based on the conditions similar to what you have shown.

You'd need to correct your sample query as it doesn't relate to the data you provided. Could also do with more explanation as to end game.

Good chance I've completely mis-understood but a rolling count of the number of respondents... this would count one person twice if they appeared in disparate days.

select distinct 
      start_date, 
	  sum(NumInDay) over (partition by program_name order by start_date desc rows between unbounded preceding and current row) as census, civ.program_name
--from #sample_census as civ  with (nolock) 
from (
select program_name, start_date, count(distinct agency_id_Number) as NumInDay
from #sample_census 
group by program_name, start_date
) civ

If you need a rolling count with distinct respondents across all in-scope days then maybe...

select program_name, d.start_date, count(distinct agency_id_number) as NumInDay
from (select distinct start_date from #sample_census) d
	inner join #sample_census  s on d.start_date <= s.start_date
group by program_name, d.start_date

Thanks, Naomi.  I created a calendar table, and like you said that did the trick!  It was simple.