Thanks Lockwelle, that was where I was headed too.

JennyLynn, if the data is too large to do this with you can use a stored procedure with parameters to limit the scope of the data at selection time.

 

SUCCESS!!!
Thank you so much for your help DBlank and lockwell!

I ended up creating 3 stored procs, one that selected all the ticket open times and grouped them by assignment then date, one that selected all the closed ticket times and grouped them the same way, then the third stored proc essentially did lockwell's suggestion and put them both into the same table. After that when back in crystal I could group them and it drew the information back together (or close enough to it).

The sad part is I still need to output the resulting report data to excel if I want to graph it because the backlog data (opened - closed) is done by a running total and Crystal Reports X can't graph on that!

For anyone reading who wants the full solution I'll paste it below (because I hate when I'm reading a post and the final solution isn't posted!)


Stored Proc #1:
CREATE PROCEDURE dbo.usp_interactions_opened_by AS

SELECT DatePart(month, PROBSUMMARYM1.OPEN_TIME) as MONTH, DatePart(day, PROBSUMMARYM1.OPEN_TIME) as DAY, DatePart(year, PROBSUMMARYM1.OPEN_TIME) as YEAR, PROBSUMMARYM1.ASSIGNMENT, COUNT(PROBSUMMARYM1.ASSIGNMENT) as OPENED, NULL AS CLOSED
FROM   database.dbo.PROBSUMMARYM1 PROBSUMMARYM1
GROUP BY PROBSUMMARYM1.ASSIGNMENT, DatePart(year, PROBSUMMARYM1.OPEN_TIME), DatePart(month, PROBSUMMARYM1.OPEN_TIME), DatePart(day, PROBSUMMARYM1.OPEN_TIME)
GO


Stored Proc #2:
CREATE PROCEDURE dbo.usp_interactions_closed_by AS

SELECT DatePart(month, PROBSUMMARYM1.CLOSE_TIME) as MONTH, DatePart(day, PROBSUMMARYM1.CLOSE_TIME) as DAY, DatePart(year, PROBSUMMARYM1.CLOSE_TIME) as YEAR, PROBSUMMARYM1.ASSIGNMENT, NULL AS OPENED, COUNT(PROBSUMMARYM1.ASSIGNMENT) as CLOSED
FROM   database.dbo.PROBSUMMARYM1 PROBSUMMARYM1
WHERE PROBSUMMARYM1.CLOSE_TIME != null
GROUP BY PROBSUMMARYM1.ASSIGNMENT, DatePart(year, PROBSUMMARYM1.CLOSE_TIME), DatePart(month, PROBSUMMARYM1.CLOSE_TIME),  DatePart(day, PROBSUMMARYM1.CLOSE_TIME)
GO


Stored Proc #3:
CREATE PROCEDURE dbo.usp_together AS

create table #tOpen1(
 MONTH INT,
 DAY INT,
 YEAR INT,
 ASSIGNMENT VARCHAR(130),
 OPENED INT,
 CLOSED INT)

insert into #tOpen1
exec usp_interactions_opened_by

insert into #tOpen1
exec usp_interactions_closed_by

select * from #tOpen1
GO


Thanks again to both of you!


Edited by JennyLynn - 19 Aug 2009 at 2:31pm