· 9 years ago · Jan 29, 2017, 04:54 PM
1use practice_2
2go
3
4drop table consults;
5drop table medics;
6drop table patients;
7drop table specs;
8drop table diagnostics;
9go
10
11create table specs
12 (ID int primary key,
13 name varchar(100))
14go
15
16create table diagnostics
17 (ID int primary key,
18 name varchar(100),
19 descrip varchar(100))
20go
21
22create table patients
23 (ID int primary key,
24 name varchar(100),
25 surname varchar(100),
26 addr varchar(100))
27go
28
29create table medics
30 (ID int primary key,
31 name varchar(100),
32 surname varchar(100),
33 spec_ID int references specs(ID))
34go
35
36create table consults
37 (medic_ID int references medics(ID),
38 patient_ID int references patients(ID),
39 PRIMARY KEY(medic_ID,patient_ID),
40 diag_ID int references diagnostics(ID),
41 cDate date,
42 cTime time,
43 obs varchar(100))
44go
45
46--
47
48insert specs(ID,name)
49values (1,'spec1'),(2,'spec2');
50go
51
52insert diagnostics(ID,name,descrip)
53values (1,'diag1','diag1'), (2,'diag2','diag2'), (3,'diag3','diag3')
54go
55
56insert medics(ID,name,surname,spec_ID)
57values (3,'m3','m3',2),(1,'m1','m1',1),(2,'m2','m2',1);
58go
59
60insert patients(ID,name,surname,addr)
61values (1,'p1','p1','addr1'),(2,'p2','p2','addr2'),(3,'p3','p3','addr3'),(4,'p4','p4','addr4'),(5,'p5','p5','addr5');
62go
63
64insert consults(medic_ID,patient_ID,diag_ID,cDate,cTime,obs)
65values (1,1,1,'2017-01-29','10:00AM','obs'),(1,2,1,'2017-01-29','10:20AM','obs'),(1,5,2,'2017-01-15','11:00AM','obs'),
66 (1,4,3,'2017-01-29','12:00AM','obs'),(2,1,1,'2017-03-29','10:00AM','obs'),(2,2,3,'2016-04-29','10:00AM','obs'),
67 (3,1,1,'2016-05-29','10:00AM','obs'),(3,5,2,'2017-01-14','10:00AM','obs'),(3,4,2,'2017-01-29','10:56AM','obs');
68go
69
70alter procedure addConsult @medID int, @patID int, @diagID int, @da date, @ti time, @obs varchar(100)
71as
72if not exists (select * from consults where medic_ID = @medID and patient_ID = @patID and cDate = @da)
73 insert consults values (@medID,@patID,@diagID,@da,@ti,@obs)
74else
75 update consults
76 set cTime = @ti
77 where medic_ID = @medID and patient_ID = @patID and cDate = @da
78go
79
80--exec addConsult @medID = 1, @patID = 3, @diagID = 1, @da = '2016-02-29', @ti = '10:30PM', @obs = 'obs1'
81--go
82
83alter view view1
84as
85select name,surname
86from medics
87where ID in
88 (select medic_ID
89 from consults
90 where MONTH(cDate) = MONTH(getdate())
91 group by medic_ID
92 having COUNT(*) > 1)
93go
94
95--select * from view1
96--order by name DESC, surname DESC
97--go
98
99alter function getMedics(@da date, @ti time)
100returns table
101as
102return
103select *
104from medics
105where ID in
106 (select medic_ID
107 from consults
108 where cDate = @da and @ti between cTime and DATEADD(MINUTE, 30, cTime)
109 group by medic_ID
110 having COUNT(*) > 1)
111go
112
113select * from getMedics('2017-01-29','10:20AM');