· 8 years ago · Mar 22, 2018, 12:40 PM
1declare @p1 datetime
2
3set @p1 = GETDATE()
4select @p1
5
6--select DATEPART(yyyy,@p1)
7
8create table Users
9(
10 Id bigint primary key identity(1,1)
11 ,Name nvarchar(200)
12 ,UserName Varchar(30)
13 ,[Password] Varchar(30)
14 ,CashAccountId Bigint
15 ,ClientId Bigint
16)
17
18create index asd
19on Users(Name)
20
21create index bsd
22on Users(UserName)
23
24create index xxxx
25on Users(Password)
26
27drop index xxxx on Users
28
29sp_help Users
30
31select * from sys.indexes
32
33select * from users
34
35insert into Users
36values ('klient1', 'username1', 'aabbcc1', 2, 2)
37 , ('klient2', 'username2', 'aabbcc2', 2, 2)
38 , ('klient3', 'username3', 'aabbcc3', 2, 2)
39
40
41alter procedure dbo.Test
42 @balance decimal(19,2),
43 @accounttype varchar(50)
44as
45begin
46 select max(@balance) as min,max(@balance) as max,avg(@balance) as avg
47 from Accounts a join AccountTypes at on a.typeid=at.id
48 where at.name='name1'
49end
50go
51
52exec dbo.Test 21.3, 'name1'
53
54create table Clients
55(
56 Id bigint primary key identity(1,1)
57 ,Name Nvarchar(200)
58 ,[Type] Bit
59 ,IdentificationNumber Varchar(36)
60 ,[Address] Varchar(100)
61 ,Tel Varchar(30)
62 ,Mob Varchar(30)
63 ,Email Varchar(30)
64 ,Web Varchar(100)
65)
66
67insert into Clients
68values ('name1', 1, '123456', 'aaaaaa', '235569', '07511111', 'asdsad.@gmail.com', 'biteme.com'),
69 ('name2', 1, '12345634', 'sdfdfsd', '325436', '07514511', 'asdsad.@hotmail.com', 'dontbiteme.com')
70
71select * from clients
72
73create view virt_c(name, [type], [address], tel, mob, email)
74as select name, [type], [address], tel, mob, email from clients
75select * from virt_c
76
77drop view virt_c
78
79create index asd
80on Clients(Name)
81
82create table Currencies
83(
84 Currency Varchar(3) not null primary key
85 ,Name Varchar(30)
86 ,Symbol Nvarchar(5)
87 ,[Image] Varbinary(8000)
88)
89
90insert into Currencies
91values (123, 'dollar', 'usd', null),
92 (60, 'euro', 'euro', null)
93
94select * from Currencies
95
96create index adsad
97on Currencies(Name)
98
99create table #tempCurrencies
100(
101 Name Varchar(30)
102 ,Symbol Nvarchar(5)
103 ,[Description] Nvarchar(100)
104)
105
106insert into #tempCurrencies
107values ('sfsfsdcf', 'a', 'adsafsdfdsvgdsvgdsvs'),
108 ('sfsfsf', 'e', 'sfhgsdsdfggfdsaf')
109
110select * from #tempCurrencies
111
112create table AccountClasses
113(
114 Id Bigint primary key identity(1,1)
115 ,Name Varchar(30)
116 ,[Description] Nvarchar(100)
117 ,[Type] Int unique check([Type] in (1,2,3,4))
118 ,IsCashAccount as case when [Type] = 1 then 1 else 0 end
119 ,IsCurrentAccount as case when [Type] = 2 then 1 else 0 end
120 ,IsDepositAccount as case when [Type] = 3 then 1 else 0 end
121 ,IsCreditAccount as case when [Type] = 4 then 1 else 0 end
122)
123
124insert into AccountClasses
125values ('name1', 'adasfasf', 1),
126 ('name2', 'sdfsdfsdf', 2),
127 ('name3', 'asfsdfsdgsd', 4)
128
129select * from AccountClasses
130
131create index asdhfd
132on AccountClasses(Name)
133
134create table AccountTypes
135(
136 Id Bigint primary key identity(1,1)
137 ,ClassId bigint foreign key references AccountClasses(Id)
138 ,Name Varchar(30)
139 ,[Description] Nvarchar(100)
140)
141
142drop table AccountTypes
143
144create index asdxcvb
145on AccountTypes(Name)
146
147insert into AccountTypes
148values (1, 'name1', 'adasfasf'),
149 (2, 'name2', 'sdfsdfsdf'),
150 (3, 'name3', 'asfsdfsdgsd')
151
152select * from AccountTypes
153
154create table Accounts
155(
156 Id Bigint primary key identity(1,1)
157 ,TypeId bigint foreign key references AccountTypes(Id)
158 ,AccountNumber Varchar(30) unique
159 ,Currency varchar(3) foreign key references Currencies(Currency)
160 ,ClientId bigint foreign key references Clients(Id)
161 ,[Status] Varchar(1) check ([Status] in ('A','O','C'))
162 ,Blocked bit
163 ,Balance Decimal(19,2)
164 ,OverdraftLimit Decimal(19,2)
165 ,AmountOnHold Decimal(19,2)
166 ,AvailableBalance as Balance+OverdraftLimit-AmountOnHold
167)
168
169insert into Accounts
170values (11, '124343', '123', 1, 'A', 2, 21.3, 34.1, 555.99),
171 (12, '457657', '60', 2, 'O', 1, 11.4, 56.01, 5643.87)
172
173select * from Accounts
174
175select max(balance) as min,max(balance) as max,avg(balance) as avg
176from Accounts a join AccountTypes at on a.typeid=at.id
177where at.name='name1'
178
179create index asdxcvbwqe
180on Accounts(Balance)
181
182create table TransactionTypes
183(
184 Id Bigint primary key identity(1,1)
185 ,Name Varchar(30)
186 ,[Description] Nvarchar(100)
187 ,AccountClassDebit bigint foreign key references AccountClasses(Id)
188 ,AccountClassCredit bigint foreign key references AccountClasses(Id)
189 ,ReflectsAmountOnHold bit default 0
190 ,HasFee bit
191)
192
193insert into TransactionTypes
194values ('aaaaaaa', 'asdghhgffhj', 1, 2, 9, 8),
195 ('bbbbbbb', 'sfdgfgfdgdf', 2, 3, 9, 8),
196 ('ccccccc', 'safdghjhhj', 2, 3, 9, 8)
197
198select * from TransactionTypes
199
200create index vbwqe
201on TransactionTypes(Name)
202
203create table Transactions
204(
205 Id Bigint primary key identity(1,1)
206 ,TransactionTypeId bigint foreign key references TransactionTypes(Id)
207 ,UserId bigint foreign key references Users(Id)
208 ,EntryDate Date default getdate()
209 ,[Date] Date
210 ,Currency varchar(3) foreign key references Currencies(Currency)
211 ,AccountIdDebit Bigint foreign key references Accounts(Id)
212 ,AccountIdCredit Bigint foreign key references Accounts(Id)
213 ,Fee Decimal(19,2)
214 ,Amount Decimal(19,2)
215 ,[Description] Nvarchar(100)
216)
217
218insert into Transactions
219values (3, 4, '1986-05-30', '1986-07-30', 123, 3, 4, 55.32, 33.1, 'asfdsfds'),
220 (4, 5, '1994-12-15', '1986-08-30', 60, 4, 3, 666.66, 99.06, 'safsdfd')
221
222select * from Transactions
223
224create table PostingEntries
225(
226 Id Bigint primary key identity(1,1)
227 ,TransactionId bigint foreign key references Transactions(Id)
228 ,AccountId bigint foreign key references Accounts(Id)
229 ,DebitCredit SmallInt check (DebitCredit in (-1,0,1))
230 ,ContraAccountId bigint foreign key references Accounts(Id)
231 ,Currency varchar(3) foreign key references Currencies(Currency)
232 ,Amount Decimal(19,2)
233 ,StatementNo int
234)
235
236
237insert into PostingEntries
238values (2, 3, -1, 4, 123, 0.5, 5),
239 (3, 4, 1, 3, 60, 0.9, 5)
240
241select * from PostingEntries
242
243
244alter procedure dbo.Test1
245(
246 @Id bigint,
247 @Class varchar(100)
248)
249as
250begin
251 declare @temp varchar(max)
252
253 set @temp = '
254 if exists (select * from ' + @Class + ' where Id = ' + cast(@Id as varchar(10)) + ')
255 delete from ' + @Class + ' where Id = ' + cast(@Id as varchar(10))
256
257 print @temp
258 exec (@temp)
259end
260
261exec dbo.Test1 2, 'Users'