How to query on a date range, display matching records but also display companies with no ma

OK, hopefully someone can help me on this one. I have a report that allows a user to select a date range and returns the applicable data. Hypothetically, this returns to the user 3 rows of data for 3 different companies\sites. The problem is that the client wants to see the other 7 companies\sites that don't have data for the date range specified by the user, in the same report.

I am having issues with this as my query returns the results that exist within the date range. If now rows of data exist for the date range, how can I possibly display the other 7 companies? I looked at embedding a report inside of another report, but I can't think of a query I could write that says, if these companies are not in the result set generated by the user, display them anyway. I also looked at using a different dataset then the one I included below.

Maybe I am missing something, I don't know. I am pretty knew to SQL but don't know how to go about capturing this data. I included my query for your review. It relies on views and whatnot, but maybe someone has an idea I could look into.

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)
GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

Jambi,

you can basically write an Union Sql query

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site
WHERE (vTTAttRollup.AttendanceDate >= @.StartDate) AND (vTTAttRollup.AttendanceDate < @.EndDate + 1) AND (vTTAttRollup.ProgramStartDate < @.StartDate)

UNION ALL

SELECT TOP (100) PERCENT vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, SUM(vTTAttRollup.HC) AS HC, SUM(vTTAttRollup.ProgDays)
AS ProgDays, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays, DailyFTE.Offer_Hired
FROM vTTAttRollup LEFT OUTER JOIN
DailyFTE ON vTTAttRollup.Site = DailyFTE.Site

WHERE some 7 other company exist

GROUP BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site, vTTAttRollup.ProgramStartDate, vTTAttRollup.TotalProgDays,
DailyFTE.Offer_Hired
ORDER BY vTTAttRollup.BellCity, vTTAttRollup.DeputyDirector, vTTAttRollup.Site

The union statement will return your date range and the other 7 company data if they exist.

Ham