I'm querying a support ticket database, and each ticket has a column for "date opened" and "date closed." Tickets frequently remain open for multiple days, so we need to be able to pull the number of tickets that are OPEN on each day.  

For example, for 4/8/14, we need to know how many tickets were opened on 4/8, combined with the total number of unclosed tickets that were opened prior to 4/8 but remained still open at 12:00am on 4/8 (may or may not have been closed during or after 4/8).

This seems straightforward enough for a single date, but now I need to write a query that will pull a complete range of dates.  

For example, we need to write a query that returns every date between 1/1/14 and 4/10/14 along with the total number of tickets open on each date (including dates on which 0 tickets were open).

Is this possible using only queries or subqueries, without using any stored procedures or temporary datatables?  

We're currently pulling the data into Excel and calculating the date stats there, but Excel is not a scalable solution and we'd like to have SQL perform this work so we can migrate this report to SSRS (SQL Server Reporting Services) down the road.