question

This is actually a very good question which I believe deserves some elaboration. The challenge here is that after the status of the work order changes but work order remains not closed, it needs to be considered as an open work order every contiguous day until either work order closes/completes or the last date of the report is reached. So, there is a need to create a set of contiguous days which starts with the earliest change date and ends with the latest change date in the work status table. This set cannot be inner/left joined to work status but needs to be cross joined first in order to "fill the gaps" and only then unwanted records need to be filtered out afterwards in the where clause. What needs to be considered is the following: 1. Records with report date for the specific work order earlier than the earliest work status record are filtered out 2. Records for the day when the work order becomes closed are filtered out. In other words, even if the work order has some not closed statuses in the work status table but then closes on the same day then all records for that day need to be disregarded. 3. Time part needs to be dropped from all datetime values. In the solution below this is done via casting the datetime value as date. If the SQL Server version is 2005 then **date** data type is not available, so the cast need to be restated via dateadd/datediff combination, i.e replace **cast(changedate as date)** with **dateadd(day, datediff(day, 0, changedate), 0)**. This expression removes the time part from the datetime value in the most efficient manner. 4. I believe that it is best to pre-collapse the records in the source before pivoting (thus the distinct is used) 5. If the other priorities besides 4 and 12 exist and need to be added as the additional column(s) to the results, they need to be coded into the IN part of the pivot and also to the final select list. Here is the script which works as expected. Please note the use of CTE which creates a set of contiguous dates with one record per day beginning from the earliest date in the work status table and ending with latest date for specified period (across all orders): /* Since the total date span is over 5 years, it is possible that you don't really need the report displaying the work orders counts for every day of every year for the last 5 years, 365 rows per year. This means that you might want to consider reducing the date range just to get the query to run fast enough and then we can come up with the ways to make it run reasonably fast for any time range. */ -- as an example, lets make the calendar for one full month (July of 2016) declare @days int, @firstDate date; select @firstDate = '20170701', @days = datediff(day, @firstDate, dateadd(month, 1, @firstDate)); ;with dates as ( -- This creates the set of days with one record per day. -- To accomodate those work orders which were initiated prior to the first date of the report -- it makes sense to add 10 days (or however many are needed to ensure that the work orders -- do not fall out of the report results). 10 days are added based on the assumption -- that the work orders' lifecycle is normally less than 10 days. select top(@days + 10) dateadd(day, row_number() over (order by Wonum) - 11, @firstDate) ReportDate from dbo.wostatus ) select pvt.ReportDate, [12] TotalP12OpenOrders, [4] TotalP4OpenOrders from ( select distinct dates.ReportDate, wo.WoNum, wo.[Priority] from dates cross join dbo.wostatus ws inner join dbo.WorkOrder wo on wo.Wonum = ws.Wonum where dates.ReportDate >= cast(wo.OpenDate as date) and not ( wo.[Status] in ('Close', 'Complete') and dates.ReportDate >= cast(wo.StatusDate as date) ) --and wo.persongroup = 'Network' /* uncomment if needs to be */ ) src pivot (count(WoNum) for [Priority] in ([12], [4])) pvt where pvt.ReportDate >= @firstDate; Based on the sample data in question, the query above produces the following result when executed:

ReportDate      TotalP12OpenOrders TotalP4OpenOrders
----------------       -----------------------------  ---------------------------
2016-06-30       1                             0
2016-07-01       2                             0
2016-07-02       2                             1
2016-07-03       2                             1
2016-07-04       2                             1
2016-07-05       2                             1
2016-07-06       2                             1
2016-07-07       2                             1
2016-07-08       2                             0

Interestingly enough, when using the table definitions from the script file attached to the question, I cannot see the column named OpenDate, so please change the script to accommodate it if needed. As far as the tables' design is concerned, barring the excessive number of column in one table, I believe that the following modifications will improve performance of this and many other queries against these tables: 1. Cluster the workorder table by wonum (if it is indeed a heap table now), optionally reducing the column size (no need to define it as size 50 when in reality the values are much shorter). If the wonum values are unique and most importantly, are ever-increasing, then the clustered index would be a good candidate for a clustered primary key 2. Find a suitable combination of columns to cluster the wostatus table if it is a heap table as seen from the table definition 3. Add the index on wonum to wostatus table 4. Consider indexing the OpenDate and StatusDate of the workorder table. 5. Formally relate the wostatus and workorder via foreign key on wonum column Please consider reading the excellent articles about indexes by Gail Shaw, it is 3-part series beginning from [Introduction to Indexes][1] article. Hope this helps, Oleg [1]: http://www.sqlservercentral.com/articles/Indexing/68439/