Please take a look.
ALTER function [dbo].[reporting_days_OnIgnore_All]( @ignoreStatus list_varchar_tbltype READONLY )
RETURNS Table
As
return (
WITH OnIgnores as (
Select
ch.CaseId ,ch.CreatedDate ,ch.OldValue ,ch.NewValue
From
CaseHistory ch
Where exists (Select * from @ignoreStatus where s = ch.NewValue)
and exists (select * From dbo.[CASE] c Where c.Id = ch.CaseId and c.cs_OpenSince__c<= ch.CreatedDate )
)
,OffIgnores as (
Select
ch.CaseId ,ch.CreatedDate ,ch.OldValue ,ch.NewValue
From
CaseHistory ch
Where exists (Select * from @ignoreStatus where s = ch.OldValue )
and exists (select * From dbo.[CASE] c Where c.Id = ch.CaseId and c.cs_OpenSince__c<= ch.CreatedDate )
)
Select
Ignores.CaseID CaseID
,ISNULL(SUM( dbo.getWeekdayDiff( Ignores.fromdate, Ignores.todate ) ), 0) daysOnIgnore
From
(
select
c.Id CaseID
,dbo.GetLocalDateTimeDST(isnull(h.CreatedDate, GETDATE()), u.TimeZoneSIDKey ) fromdate
,dbo.GetLocalDateTimeDST(
isNULL( ( select MIN(h2.createddate) from OffIgnores h2 where h2.CreatedDate > h.CreatedDate AND h2.caseid = h.caseid ), GETDATE() )
, u.TimeZoneSIDKey ) todate
from
[CASE] c
Inner Join [user] u (nolock) on c.ownerid = u.Id
LEFT OUTER JOIN OnIgnores h ON h.CaseId = c.Id
-- add in extra row for cases originally opened as OnIgnore
UNION ALL
select
c.Id CaseID
,dbo.GetLocalDateTimeDST(c.CreatedDate, u.TimeZoneSIDKey ) fromdate
,dbo.GetLocalDateTimeDST(h.CreatedDate, u.TimeZoneSIDKey ) todate
from
dbo.[CASE] c
Inner Join [user] u (nolock) on c.ownerid = u.Id
OUTER APPLY (
SELECT TOP 1 *
FROM (
select * from OffIgnores ofh where c.Id = ofh.CaseId
UNION ALL
select * from OnIgnores onh where c.Id = onh.CaseId
) oh
where exists (Select * from @ignoreStatus where s = oh.OldValue)
ORDER BY
oh.CreatedDate Asc
) h
) Ignores
Group By Ignores.CaseID
);
No comments:
Post a Comment