StrikeEagleII
Technical User
I'm creating a database that tracks open issues on a product. the data includes the date the issue was opened and the date it was closed. I am trying to figure out how to create a query that will return the number of open issues per day
Simplified sample data:
pkID DateOpened DateClosed
1 2/1/2010 2/4/2010
2 2/2/2010 2/5/2010
3 2/3/2010 2/7/2010
4 2/3/2010 2/5/2010
5 2/5/2010 2/7/2010
6 2/7/2010 2/8/2010
7 2/7/2010 2/9/2010
desired output:
Date: OpenItems:
2/1/2010 1
2/2/2010 2
2/3/2010 4
2/4/2010 3
2/5/2010 2
2/6/2010 2
2/7/2010 2
2/8/2010 1
2/9/2010 0
Ultimately, it will be exported to excel to make a pretty graph for management... the initial thought was to create a temp table that has a row for each date between the earliest DateOpened and latest DateClosed and then loop through and for each Date, count the number of records where Date > DateOpened AND Date < DateClosed, but that seems pretty clunky and inefficient.
Simplified sample data:
pkID DateOpened DateClosed
1 2/1/2010 2/4/2010
2 2/2/2010 2/5/2010
3 2/3/2010 2/7/2010
4 2/3/2010 2/5/2010
5 2/5/2010 2/7/2010
6 2/7/2010 2/8/2010
7 2/7/2010 2/9/2010
desired output:
Date: OpenItems:
2/1/2010 1
2/2/2010 2
2/3/2010 4
2/4/2010 3
2/5/2010 2
2/6/2010 2
2/7/2010 2
2/8/2010 1
2/9/2010 0
Ultimately, it will be exported to excel to make a pretty graph for management... the initial thought was to create a temp table that has a row for each date between the earliest DateOpened and latest DateClosed and then loop through and for each Date, count the number of records where Date > DateOpened AND Date < DateClosed, but that seems pretty clunky and inefficient.