-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathsql_5.sql
113 lines (91 loc) · 2.88 KB
/
sql_5.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
1.find the loans that have not been returned:
select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns);
2.find the ones that have been returned:
select * from Loans L join Returns R on R.loan = L.loan_id order by customer;
3.join loan and return for returned records and unreturned records:
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR0
order by customer;
4. find the loans with at least one that does not overlap based on same customer number:
select * from
(
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR1
)LR5
where not exists
(
select * from
(
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR2
)LR6
where LR5.customer = LR6.customer and
LR5.start < LR6.date and
LR5.date > LR6.start and
(LR5.start <> LR6.start or LR5.date <> LR6.date)
)
order by customer;
5.deal with 32 (2 loans with same start and date):
select * from
(
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR1
)LR5
where not exists
(
select * from
(
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR2
)LR6
where LR5.customer = LR6.customer and
LR5.start < LR6.date and
LR5.date > LR6.start and
((LR5.start <> LR6.start or LR5.date <> LR6.date) or (LR5.loan_id <> LR6.loan_id))
)
order by customer;
select L.customer, max(L.start) from Loans L
where not customer in
(select customer from (
select * from
(
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR1
)LR5
where not exists
(
select * from
(
select * from (
(select *, '9999-12-31' as date from Loans L where L.loan_id not in (select loan from Returns))
UNION
(select loan_id, copy, customer, start, due, date from Loans L join Returns R on R.loan = L.loan_id)
) LR2
)LR6
where LR5.customer = LR6.customer and
LR5.start < LR6.date and
LR5.date > LR6.start and
((LR5.start <> LR6.start or LR5.date <> LR6.date) or (LR5.loan_id <> LR6.loan_id))
)
order by customer
)A2)
group by customer
order by customer;