June 24, 2011 at 4:41 am
Hello
I am currently using Dynamics CRM 4.0 as a Case management system and I have been asked to perform some trend analysis for the last 6 months on the following
1) Number of cases opened each day
2) Number of cases resovled each day
3) Number of cases opened and resolved in the same day
and not essential but would be nice
4) Number of cases still active at the end of the day for the last 6 months
I can achieve 1,2,3 as the following
1) Count(createdon) , group by (Createdon)
2) Count(modifiedon) where status = 'Resolved', group by (modifiedon)
3) Count(*) where createdon = modifiedon and Stauts = 'Resolved', group by createdon
4) No idea how to achieve this
I can achieve 1,2 and 3 in seperate queries but I need them in the same query so that I can create an SSRS Report as my end user whats the Trend analysis in a graph.
Below is an example of 1 of my queries but can somebody advise how I can combine the 3 or 4 requests within one query. My attempts so far have failled with multiple results for each Createdon date.
select COUNT(*), dateadd(day, datediff(day, 0, createdon), 0) AS mydate
from FilteredIncident
where
datepart(YYYY,createdon) = datepart(YYYY,modifiedon)
and
datepart(MM,createdon) = datepart(MM,modifiedon)
AND
datepart(DD,createdon) = datepart(DD,modifiedon)
And
(statecodename = 'resolved' or statecodename = 'Canceled')
AND createdon >= (DATEADD(mm,-6,GETDATE()))
group by dateadd(day, datediff(day, 0, createdon), 0)
June 27, 2011 at 7:25 am
4 :
where createdon <> DateResolved, group by createdon
June 27, 2011 at 7:28 am
Thank you for this - Now I see it written down I realise the mistake I have made -
Any ideas how I can get all 4 into one query? All I seem to get is a list of duplciate dates and nulls?
Thanks
Tony
June 27, 2011 at 7:31 am
Thinking about the resolved part.
Do you have a resolveddate somewhere (is it in another table). That's what would seem the easiest to do.
June 27, 2011 at 7:35 am
This trick might come in handy :
SELECT SUM(CASE WHEN DateCreated <> DateResolved THEN 1 ELSE 0 END) AS NotResolvedSameDay, SUM(CASE WHEN DateCreated = DateResolved THEN 1 ELSE 0 END) AS ResolvedSameDay,
COUNT(*) AS CasesPerDay, COUNT(ResolvedID) AS Resolved >> left join will throw nulls and get ignored in the count.
FROM Cases LEFT OUTER JOIN Resolvedcases
June 27, 2011 at 7:35 am
For the purpose of my report it is acceptable to use the last modifiedon date and this will not change once a Case is resolved
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply