· 7 years ago · Dec 24, 2018, 03:10 PM
1use Lab5DB;
2go
3
4if object_id('dbo.Recent', 'U') is not null
5 drop table dbo.Recent;
6
7IF object_id('dbo.Archived', 'U') is not null
8 drop table dbo.Archived;
9
10IF object_id('dbo.CallStats', 'U') is not null
11 drop table dbo.CallStats;
12
13create table Recent(
14 id bigint identity(1,1) not null primary key,
15 call_datime datetime2,
16 caller_id bigint,
17 cost decimal(19,2),
18 call_duration bigint);
19go
20
21create table Archived(
22 id bigint identity(1,1) not null primary key,
23 call_datime datetime2,
24 caller_id bigint,
25 cost decimal(19,2),
26 call_duration bigint);
27go
28
29create table CallStats(
30 id bigint identity(1,1) not null primary key,
31 caller_id bigint,
32 total_cost decimal(19,2),
33 total_duration bigint);
34go
35
36--Procedures
37
38
39--getRecent
40if exists (select * from sys.objects where type = 'P' and name = 'getRecent')
41 drop procedure getRecent
42go
43
44create procedure getRecent
45 as select * from Recent;
46go
47
48--getArchived
49if exists (select * from sys.objects where type = 'P' and name = 'getArchived')
50 drop procedure getArchived
51go
52
53create procedure getArchived
54 as select * from Archived;
55go
56
57--getCallStats
58if exists (select * from sys.objects where type = 'P' and name = 'getCallStats')
59 drop procedure getCallStats
60go
61
62create procedure getCallStats
63 as select * from CallStats;
64go
65
66--getAll
67if exists (select * from sys.objects where type = 'P' and name = 'getAll')
68 drop procedure getAll
69go
70
71create procedure getAll
72 as begin
73 exec getRecent
74 exec getArchived
75 exec getCallStats
76 end
77go
78
79--deleteAll
80if exists (select * from sys.objects where type = 'P' and name = 'deleteAll')
81 drop procedure deleteAll
82go
83
84create procedure deleteAll
85 as begin
86 delete from Recent;
87 delete from Archived;
88 delete from CallStats;
89 end
90go
91
92--addCall
93if exists (select * from sys.objects where type = 'P' and name = 'addCall')
94 drop procedure addCall
95go
96
97create procedure addCall @datime datetime2, @caller_id bigint, @cost decimal(19,2), @call_duration bigint
98 as insert into Recent values(@datime, @caller_id, @cost, @call_duration)
99go
100
101--moveToArchived
102--not all the data from previous month but only service info that expired
103--30 days passed since adding
104--To be performed on daily basis.
105if exists (select * from sys.objects where type = 'P' and name = 'moveToArchived')
106 drop procedure moveToArchived
107go
108
109create procedure moveToArchived @current_date datetime2
110 as begin tran
111 begin try
112 insert into Archived(call_datime, caller_id, cost, call_duration)
113 select Recent.call_datime, Recent.caller_id, Recent.cost, Recent.call_duration from Recent
114 where datediff(day, Recent.call_datime, @current_date) >= 30;
115 delete from Recent
116 where datediff(day, Recent.call_datime, @current_date) >= 30
117 commit tran
118 end try
119 begin catch
120 rollback tran
121 end catch;
122go
123
124--movetoStats.
125--in 12 months after moving to archive record is deleted and its data is added to total records for specific user
126--To be performed on daily basis.
127if exists (select * from sys.objects where type = 'P' and name = 'moveToStats')
128 drop procedure moveToStats
129go
130
131create procedure moveToStats @current_date datetime2
132 as begin tran
133 begin try
134 declare @ca_id bigint, @cost decimal(19,2), @ca_durat bigint
135 declare arch_cursor cursor for
136 select caller_id, cost, call_duration from Archived
137 where datediff(month, Archived.call_datime, @current_date) >= 12
138 open arch_cursor
139 fetch next from arch_cursor into @ca_id, @cost, @ca_durat;
140 while @@fetch_status = 0
141 begin
142 if exists(select id from CallStats where CallStats.caller_id = @ca_id)
143 update CallStats
144 set total_cost += @cost,
145 total_duration += @ca_durat
146 where CallStats.caller_id = @ca_id
147 else insert into CallStats(caller_id, total_cost, total_duration)
148 values(@ca_id, @cost, @ca_durat);
149 fetch next from arch_cursor into @ca_id, @cost, @ca_durat;
150 end
151 close arch_cursor;
152 deallocate arch_cursor
153
154 delete from Archived
155 where datediff(month, Archived.call_datime, @current_date) >= 12
156 commit tran
157 end try
158 begin catch
159 rollback tran
160 end catch;
161go
162
163--Testing
164
165exec deleteAll;
166
167exec addCall '2017-11-20T19:58:47', 3, 2, 10;
168exec addCall '2017-10-20T19:58:47', 3, 2, 10;
169exec addCall '2017-09-20T19:58:47', 4, 2.33, 13;
170exec addCall '2018-11-20T19:58:47', 1, 6.50, 40;
171exec addCall '2018-11-22T19:58:47', 1, 10.44, 95;
172--shouldn't be moved to archive:
173exec addCall '2018-11-27T19:58:47', 2, 23.44, 190;
174exec addCall '2018-12-01T19:58:47', 2, 1.44, 12;
175
176exec getRecent;
177exec getArchived;
178
179--Archiving
180exec moveToArchived '2018-12-22T19:58:47'
181exec getAll;
182
183--Statistics
184exec moveToStats '2018-11-20T19:58:47';
185exec getAll;
186
187--We can add one more call from caller_id 1:
188exec addCall '2017-08-20T19:58:47', 3, 2, 10;
189exec moveToArchived '2018-12-22T19:58:47';
190exec moveToStats '2018-11-20T19:58:47';
191exec getAll;