0

I’m trying to create query that will return how many cases were aged over one day by date. In other words, how many cases were not resolved on day created and were outstanding on each specified day. This is to look at historical performance of resolution and how many each day were over one day old. I can accomplish this easily with excel, but I have around 1 million cases/rows to count and excel just can’t handle that all at once. I know the basics of SQL, but I’m not sure how to accomplish by an SQL query.

On excel, I have a variable that is list of calendar dates I want a result for, and then using COUNTIFS the formula looks at the data and counts what meets the criteria. I don’t know where to begin to translate that to SQL. I’m using MS Access.

Hopefully, using the below example data as solved with Excel will assist in explaining what I’m attempting to accomplish.

Example data
Case_ID  Create_Date    Resolved_Date
1   1/1/2013    1/2/2013
2   1/1/2013    1/3/2013
3   1/1/2013    1/4/2013
4   1/1/2013    1/5/2013
5   1/2/2013    1/5/2013
6   1/2/2013    1/5/2013
7   1/4/2013    1/5/2013
8   1/4/2013    1/5/2013
9   1/5/2013    1/5/2013
10  1/5/2013    1/5/2013
11  1/6/2013    1/9/2013
12  1/7/2013    1/10/2013
13  1/7/2013    1/9/2013
14  1/8/2013    1/12/2013
15  1/8/2013    1/13/2013
16  1/8/2013    1/9/2013
17  1/8/2013    1/11/2013
18  1/8/2013    1/11/2013
19  1/9/2013    1/11/2013
20  1/10/2013   1/14/2013

Excel results with formula "=COUNTIFS(Create_Date,"<"&E2,Resolved_Date, ">"&E2)" <- "E2" Is just the cell reference of "1/1/2013."
Column(E)   Column(F)
Date        Count of Aged Cases
1/1/2013    0
1/2/2013    3
1/3/2013    4
1/4/2013    3
1/5/2013    0
1/6/2013    0
1/7/2013    1
1/8/2013    3
1/9/2013    5
1/10/2013   5
1/11/2013   3
1/12/2013   2
1/13/2013   1
1/14/2013   0
pnuts
  • 58,317
  • 11
  • 87
  • 139
Shuhart
  • 1
  • The following may help: http://stackoverflow.com/questions/582637/sql-equivalent-of-countif?rq=1 – Seymour Dec 01 '13 at 21:52
  • Do you have a calendar table of some sort that has one row for each date whether it has activity or not? – Dan Bracuk Dec 01 '13 at 22:10
  • Thanks adkSerenity, I did come across ths one, but I believe it address a different issue. My thought so far is a calculation needs to occur for each date. To answer Dan, I'm thinking as well there would need to be a separate calendar table to base each calculation on. The problem is I don't know how to begin to do that with SQL. – Shuhart Dec 02 '13 at 15:15
  • @Shuhart It's difficult to do first because the specification is incomplete/ambiguous. You specify "how many cases were not resolved on day created"; and you also include dates in the output such as "1/3/2013" and "1/14/2013", neither of which reflect a "day created". Remove ambiguities before anything else. That should make it much clearer. – user2338816 May 09 '14 at 20:10

0 Answers0