5ZWSHq0B

1execute('
2
3SET NOCOUNT ON
4
5DECLARE @SD date,
6        @ED date,
7        @QD date
8
9
10SET @SD = ''2018-01-01''
11SET @ED = getdate();
12SET @QD = dateadd(mm, 3, @SD)
13
14IF OBJECT_ID(''tempdb.dbo.#results'', ''U'') IS NOT NULL
15DROP TABLE #results ;
16Create Table #results
17(
18        QuarterDateEND     date NULL,
19        Metric      decimal(10, 8),
20        Metrictype    nvarchar(50)
21
22)
23
24While @SD <= @ED
25
26Begin
27INSERT INTO #results
28
29  SELECT dateadd(mm, 3, @SD), (CAST(SUM(CASE WHEN b.acceptedDttm > DATEADD(dd,42,a.todttm) THEN 0 ELSE 1 END) as decimal(10,2)) / count(b.id)) As ''Bills'', ''%Final Bills Within 6 Weeks'' as ''MetricType''
30
31      FROM Bill b
32
33        INNER JOIN Account a on b.accountFk = a.id
34
35              WHERE  b.acceptedDttm > @SD
36	           	AND b.acceptedDttm <= @QD
37		          AND b.finalFl = ''Y'' 
38		          AND b.supersededFl = ''N''
39	           	AND b.status = ''Accepted''
40	           	AND a.cancelledDttm is null
41	            AND a.toDttm < GETDATE()
42	            AND EXISTS 
43
44			                	(SELECT 1 
45
46				                    	FROM Ticket t 
47
48						                            inner join TicketDefinition td on t.ticketDefinitionFk = td.id
49							                            AND td.keyPrefix = ''G4SCUSTLOSS''
50						                            INNER JOIN TicketEntity te on t.id = te.ticketFk
51						                            INNER JOIN EntityTbl e on te.entityTblFk = e.id
52							                            AND e.entity = ''ACCOUNT''
53						
54						                                WHERE te.entityId = a.id)
55		
56						set @SD = dateadd(mm, 3, @SD)
57						set @QD = dateadd(mm, 3, @SD)
58						
59						end;
60						
61						select * from #results
62						
63						')