· 9 years ago · Oct 08, 2016, 08:30 PM
1--> SCHEMA CREATE:
2IF NOT EXISTS (SELECT schema_name
3 FROM information_schema.schemata
4 WHERE schema_name = 'Hackaton' )
5BEGIN
6EXEC sp_executesql N'CREATE SCHEMA Hackaton'
7END
8
9--> DDL:
10IF OBJECT_ID('Hackaton.Konto','U') IS NULL
11 CREATE TABLE Hackaton.Konto
12 (
13 ID int identity(1,1),
14 Data DATETIME,
15 KlientId int,
16 Operacja VARCHAR(3),
17 Kwota DECIMAL (10,2)
18 );
19
20
21--> DML:
22INSERT INTO Hackaton.Konto (Data,KlientId,Operacja,Kwota)
23 SELECT '2016-01-01',1,'IN',1000 UNION ALL
24 SELECT '2016-01-01',2,'IN',1250 UNION ALL
25 SELECT '2016-01-03',1,'IN',50 UNION ALL
26 SELECT '2016-01-03',1,'OUT',999 UNION ALL
27 SELECT '2016-01-03',1,'IN',1000 UNION ALL
28 SELECT '2016-01-04',2,'OUT',850 UNION ALL
29 SELECT '2016-01-04',1,'OUT',1
30
31
32--> DDL - CREATE VIEW:
33CREATE VIEW Hackaton.GetAccountOperationHistory
34AS
35WITH cte_Konto_RunningTotal (Data, KlientId, Operacja, Kwota, StanKonta)
36AS
37(
38 SELECT
39 CAST(k.Data AS date),
40 k.KlientId,
41 k.Operacja,
42 k.Kwota,
43 SUM(CASE
44 WHEN k.Operacja = 'IN' THEN k.Kwota
45 WHEN k.Operacja = 'OUT' THEN -k.Kwota
46 END) OVER (PARTITION BY k.KlientId ORDER BY k.Id)
47 FROM Hackaton.Konto k
48)
49SELECT
50 t.Data,
51 t.KlientId,
52 t.Operacja,
53 t.Kwota,
54 t.StanKonta
55FROM cte_Konto_RunningTotal t;
56
57
58
59--> VIEW SELECT WITH ORDER BY DATE:
60SELECT *
61FROM Hackaton.GetAccountOperationHistory h
62ORDER BY h.Data ASC;
63
64--Data KlientId Operacja Kwota StanKonta
65--2016-01-01 1 IN 1000.00 1000.00
66--2016-01-01 2 IN 1250.00 1250.00
67--2016-01-03 1 IN 50.00 1050.00
68--2016-01-03 1 OUT 999.00 51.00
69--2016-01-03 1 IN 1000.00 1051.00
70--2016-01-04 1 OUT 1.00 1050.00
71--2016-01-04 2 OUT 850.00 400.00
72
73
74--> DDL - CREATE SP:
75-- =============================================
76-- Author: SQL Kato Crew
77-- Create date: 08.10.2016
78-- Description: Add client account operation
79-- =============================================
80CREATE PROCEDURE [Hackaton].[AddAccountOperation]
81 @IdClient INT,
82 @Operation NVARCHAR(3),
83 @Amount DECIMAL (10,2)
84AS
85BEGIN
86 SET NOCOUNT ON;
87
88 IF (ISNULL(@IdClient, 0) = 0)
89 BEGIN
90 RAISERROR('Invalid parameter: @IdClient cannot be NULL or zero', 18, 0)
91 RETURN
92 END
93
94 IF (ISNULL(@Operation, '') = '')
95 BEGIN
96 RAISERROR('Invalid parameter: @Operation cannot be NULL or empty', 18, 0)
97 RETURN
98 END
99
100 IF (ISNULL(@Amount, 0) = 0)
101 BEGIN
102 RAISERROR('Invalid parameter: @Amount cannot be NULL or zero', 18, 0)
103 RETURN
104 END
105
106 INSERT INTO Hackaton.Konto VALUES (GETDATE(), @IdClient, @Operation, @Amount);
107
108 RETURN 1;
109END
110
111
112
113--> EXEC:
114EXEC [Hackaton].[AddAccountOperation] 1, 'IN', 200;
115
116
117--> VIEW SELECT WITH ORDER BY DATE:
118SELECT *
119FROM Hackaton.GetAccountOperationHistory h
120ORDER BY h.Data ASC;
121
122--Data KlientId Operacja Kwota StanKonta
123--2016-01-01 1 IN 1000.00 1000.00
124--2016-01-01 2 IN 1250.00 1250.00
125--2016-01-03 1 IN 50.00 1050.00
126--2016-01-03 1 OUT 999.00 51.00
127--2016-01-03 1 IN 1000.00 1051.00
128--2016-01-04 1 OUT 1.00 1050.00
129--2016-01-04 2 OUT 850.00 400.00
130--2016-10-08 1 IN 200.00 1250.00