Adding count for intervening months 1

[IanWaterman]

I have to come up with a query to count calls in a month. I also have to count calls which span month ends.

So far I have 4 simply queries unioned together to give me Calls opened, calls closed, calls carried forward, ie opened in month but closed in subsequent month and calls brought forward, opened in prior month and closed in subsequent month.

The brought forward anc carried forward are failing when a call spans more than a month. For example I have 50 calls opened at end of Feb but were not closed until April.

So my data looks like

Feb Carried forward = 232
March Brought forward = 182
March carried forward = 189
April Brought forward = 239

I can only count data based on Accepted dates and Cleared dates, when a call is uncleared ie still open the date is set as 1900/01/01.

A further complication is that calls can remain open for several months, How do I add counts in the intervening months to increment BF and CF figures by those calls.

BF Query

select callno,
case when dcleared = {ts '1900-01-01 00:00:00'} then getdate() else dcleared end post_date,
1 as Brought_forward,
0 as Opened,
0 as Closed,
0 as Carried_forward
from calldetails
where ccode = 'LBLEW'
AND daccepted >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))
And daccepted < DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)-1
and (MONTH(daccepted) < MONTH(dcleared) or dcleared = {ts '1900-01-01 00:00:00'})

CF query
select callno, daccepted post_date,
0 as Brought_forward,
0 as Opened,
0 as Closed,
1 as Carried_forward
from calldetails
where ccode = 'LBLEW'
AND daccepted >= dateadd(yy, -1, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))
And daccepted < DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)-1
and (MONTH(daccepted) < MONTH(dcleared) or dcleared = {ts '1900-01-01 00:00:00'})

I appreciate I will have to improve my test for calls spanning a year end too.

The report is a rolling 12 months to be run on the first day of a month. This will need to be changed too to cater for calls still open in 12 months time, system was only installed in Feb this year, so not an issue yet.

Thanks

Ian

 

[RobertC22]

Even if this doesn't give you the answer you seek, hopefully it might give you the techniques you can use

select 
StartDate
, EndDate
, mm
, sum ( case when daccepted < StartDate and ( dcleared >= StartDate or dcleared = '1900-01-01') then 1 else 0 end ) as Brought_forward
, sum ( case when daccepted between StartDate and EndDate then 1 else 0 end ) as Opened
, sum ( case when dcleared between  StartDate and EndDate then 1 else 0 end ) as Closed
, sum ( case when daccepted <= EndDate and ( dcleared > EndDate or dcleared = '1900-01-01') then 1 else 0 end ) as Carried_forward
, sum ( case when daccepted between StartDate and EndDate and ( dcleared > EndDate or dcleared = '1900-01-01') then 1 else 0 end ) as ThisMonths_Carried_forward
, sum ( case when daccepted between StartDate and EndDate 
		and dcleared between  StartDate and EndDate  then 1 else 0 end ) as OpenResolvedThisMonth

from calldetails
cross join ( 
	Select 
	dateadd( mm, mm * -1 , today - day(today ) ) + 1 as StartDate
	, dateadd( mm, ( mm - 1) * -1 , today - day( today )  ) as EndDate
	, mm
	from 
		( select 1 as mm union all select 2 union all select 3
		union all select 4 union all select 5 union all select 6
		union all select 7 union all select 8 union all select 9
		union all select 10 union all select 11 union all select 12 
		) cal cross join (select convert(datetime,convert(char(10),getdate(),121)) as today) tt
) DateRange
where 
ccode = 'LBLEW'
and
( dcleared = {ts '1900-01-01 00:00:00'} /* open calls, regardless of when started */
or daccepted >=  dateadd ( yy, -1, getdate() - day(getdate() ) ) /* all calls at least one year */
)
group by StartDate, EndDate, mm
order by StartDate Desc

------
Robert

 

[IanWaterman]

Robert

Thank you very much for your input. Unfortunately the results retured are not quite right. But as you said hopefully when I get my head around what you have done I should be able to correct logic to give me what I want.

Thanks again

Ian

 

[IanWaterman]

Robert

Spotted the issue, because I am using datetime your end date was too early, needed to be midnight on the first day of following month.

Now working perfectly.

Thanks again.

Ian