1

The Data I'm working with is

INC#  Client   Summary     Opened Date     Closed Date  
1     user A   Issue 1      12/18/2006     07/03/2015  
2     user B   Issue 2      04/01/2015     07/02/2015  
3     user C   Issue 3      05/04/2015     05/06/2015

I want to run a report that will show me how many open tickets were in our queue at the end of each month. I need a formula to answer the question "was this ticket still open on April 30th 2015" then show a count

Right now I'm pulling all the tickets we have from Track-IT! 9 have and just done some filtering out of subTickets in Crystal reports XI.

BJones
  • 2,450
  • 2
  • 17
  • 25
Liz
  • 35
  • 1
  • 6
  • Right now my SQL looks like this SELECT "TASKS"."WO_NUM", "TASKS"."REQUEST", "TASKS"."TASK", "TASKS"."OPENDATE", "TASKS"."CLSDDATE", "TASKS"."RESPONS" FROM "TRACKIT9_DATA"."dbo"."TASKS" "TASKS" WHERE ("TASKS"."OPENDATE"<{ts '2015-09-02 00:00:00'} AND "TASKS"."CLSDDATE">={ts '2015-09-01 00:00:00'} OR "TASKS"."CLSDDATE" IS NULL ) ORDER BY "TASKS"."RESPONS", "TASKS"."OPENDATE" – Liz Sep 01 '15 at 21:52

1 Answers1

0

This sql script may help

declare @endOfMonths table(endDay datetime);
insert into @endOfMonths values('2015-01-31');
insert into @endOfMonths values('2015-02-28');
insert into @endOfMonths values('2015-03-31');
... -- you may insert these dates within a loop also

select count(*), endDay 
from dataTable
inner join @endOfMonths on endDay between OpenedDate and ClosedDate
group by endDay
order by endDay
Abdullah Nehir
  • 1,027
  • 13
  • 23