Forum Discussion
Solved
Calculate days elapsed between 2 duration dates based on 2 cut off dates on a month
Hi,
I'm working on calculating days between date duration based on 2 cutoff dates on a specific month. I just need the days between duration and cut off based on month in row $B$ to be filled on empty cell on blue table. I hope that it just use 1 general formula and can be draged down.
I don't have any idea to combine some fomulas to solve that problem. Can someone is helping me out on this?
I'm using excel 2013.
Really appreciate your answer.
Thank you.
=IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",MAX(MIN(Table1[@[Finish]:[Finish]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11))-MAX(Table1[@[Start]:[Start]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-12)),0))
In G12:
=IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",ROUNDUP(MAX(MIN(Table1[@[Finish]:[Finish]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11))-MAX(Table1[@[Start]:[Start]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-12)),0),0))
Format as General.
Fill to the right, and down if Excel doesn't do that automatically.
Thank you for the solution, apreciate it.
But I need precision calculation results of each date duration in table1 (blue).
e.g the duration is:
12/10/2022 16.10 to 09/12/2022 10.00
so the result should be 6.417 days on 1st week December.
Can you help me with this problem?=IF(INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11)="","",MAX(MIN(Table1[@[Finish]:[Finish]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-11))-MAX(Table1[@[Start]:[Start]],INDEX(Table2,MATCH(LEFT(Table1[@[Month]:[Month]],3),Table2[[Column1]:[Column1]],0),2*COLUMN()-12)),0))
do you mean to expand activit all days and match days between open and closed?
id Activity Open days workday
1 A 2021-10-26 0 2021-10-26
1 A 2021-10-26 1 2021-10-27
1 A 2021-10-26 2 2021-10-28
1 A 2021-10-26 3 2021-10-29
1 A 2021-10-26 4 2021-10-30
1 A 2021-10-26 5 2021-10-31
1 A 2021-10-26 6 2021-11-01
1 A 2021-10-26 7 2021-11-02
1 A 2021-10-26 8 2021-11-03
1 A 2021-10-26 9 2021-11-04
1 A 2021-10-26 10 2021-11-05
1 A 2021-10-26 11 2021-11-06
1 A 2021-10-26 12 2021-11-07
1 A 2021-10-26 13 2021-11-08
1 A 2021-10-26 14 2021-11-09
1 A 2021-10-26 15 2021-11-10
1 A 2021-10-26 16 2021-11-11
1 A 2021-10-26 17 2021-11-12
1 A 2021-10-26 18 2021-11-13
1 A 2021-10-26 19 2021-11-14
1 A 2021-10-26 20 2021-11-15
1 A 2021-10-26 21 2021-11-16
1 A 2021-10-26 22 2021-11-17
1 A 2021-10-26 23 2021-11-18
1 A 2021-10-26 24 2021-11-19
1 A 2021-10-26 25 2021-11-20
1 A 2021-10-26 26 2021-11-21
1 A 2021-10-26 27 2021-11-22
1 A 2021-10-26 28 2021-11-23
1 A 2021-10-26 29 2021-11-24
1 A 2021-10-26 30 2021-11-25
1 A 2021-10-26 31 2021-11-26
1 A 2021-10-26 32 2021-11-27
1 A 2021-10-26 33 2021-11-28
1 A 2021-10-26 34 2021-11-29
1 A 2021-10-26 35 2021-11-30
1 A 2021-10-26 36 2021-12-01
1 A 2021-10-26 37 2021-12-02
1 A 2021-10-26 38 2021-12-03
1 A 2021-10-26 39 2021-12-04
1 A 2021-10-26 40 2021-12-05
1 A 2021-10-26 41 2021-12-06
1 A 2021-10-26 42 2021-12-07
1 A 2021-10-26 43 2021-12-08
1 A 2021-10-26 44 2021-12-09
1 A 2021-10-26 45 2021-12-10
1 A 2021-10-26 46 2021-12-11
1 A 2021-10-26 47 2021-12-12
1 A 2021-10-26 48 2021-12-13
1 A 2021-10-26 49 2021-12-14
1 A 2021-10-26 50 2021-12-15
1 A 2021-10-26 51 2021-12-16
1 A 2021-10-26 52 2021-12-17
1 A 2021-10-26 53 2021-12-18
1 A 2021-10-26 54 2021-12-19
1 A 2021-10-26 55 2021-12-20
1 A 2021-10-26 56 2021-12-21
1 A 2021-10-26 57 2021-12-22
1 A 2021-10-26 58 2021-12-23
1 A 2021-10-26 59 2021-12-24
1 A 2021-10-26 60 2021-12-25
1 A 2021-10-26 61 2021-12-26
1 A 2021-10-26 62 2021-12-27
1 A 2021-10-26 63 2021-12-28
1 A 2021-10-26 64 2021-12-29
1 A 2021-10-26 65 2021-12-30
1 A 2021-10-26 66 2021-12-31
1 A 2021-10-26 67 2022-01-01
1 A 2021-10-26 68 2022-01-02
1 A 2021-10-26 69 2022-01-03
1 A 2021-10-26 70 2022-01-04
1 A 2021-10-26 71 2022-01-05
1 A 2021-10-26 72 2022-01-06
1 A 2021-10-26 73 2022-01-07
1 A 2021-10-26 74 2022-01-08
1 A 2021-10-26 75 2022-01-09
1 A 2021-10-26 76 2022-01-10
1 A 2021-10-26 77 2022-01-11
1 A 2021-10-26 78 2022-01-12
1 A 2021-10-26 79 2022-01-13
1 A 2021-10-26 80 2022-01-14
1 A 2021-10-26 81 2022-01-15
1 A 2021-10-26 82 2022-01-16
1 A 2021-10-26 83 2022-01-17
1 A 2021-10-26 84 2022-01-18
1 A 2021-10-26 85 2022-01-19
1 A 2021-10-26 86 2022-01-20
1 A 2021-10-26 87 2022-01-21
1 A 2021-10-26 88 2022-01-22
1 A 2021-10-26 89 2022-01-23
1 A 2021-10-26 90 2022-01-24
1 A 2021-10-26 91 2022-01-25
1 A 2021-10-26 92 2022-01-26
1 A 2021-10-26 93 2022-01-27
1 A 2021-10-26 94 2022-01-28
1 A 2021-10-26 95 2022-01-29
1 A 2021-10-26 96 2022-01-30
1 A 2021-10-26 97 2022-01-31
1 A 2021-10-26 98 2022-02-01
1 A 2021-10-26 99 2022-02-02
1 A 2021-10-26 100 2022-02-03
1 A 2021-10-26 101 2022-02-04
1 A 2021-10-26 102 2022-02-05
1 A 2021-10-26 103 2022-02-06
1 A 2021-10-26 104 2022-02-07
1 A 2021-10-26 105 2022-02-08
1 A 2021-10-26 106 2022-02-09
1 A 2021-10-26 107 2022-02-10
1 A 2021-10-26 108 2022-02-11
1 A 2021-10-26 109 2022-02-12
1 A 2021-10-26 110 2022-02-13
1 A 2021-10-26 111 2022-02-14
1 A 2021-10-26 112 2022-02-15
1 A 2021-10-26 113 2022-02-16
1 A 2021-10-26 114 2022-02-17
1 A 2021-10-26 115 2022-02-18
1 A 2021-10-26 116 2022-02-19
1 A 2021-10-26 117 2022-02-20
1 A 2021-10-26 118 2022-02-21
1 A 2021-10-26 119 2022-02-22
1 A 2021-10-26 120 2022-02-23
1 A 2021-10-26 121 2022-02-24
1 A 2021-10-26 122 2022-02-25
1 A 2021-10-26 123 2022-02-26
1 A 2021-10-26 124 2022-02-27
1 A 2021-10-26 125 2022-02-28
1 A 2021-10-26 126 2022-03-01
1 A 2021-10-26 127 2022-03-02
1 A 2021-10-26 128 2022-03-03
1 A 2021-10-26 129 2022-03-04
1 A 2021-10-26 130 2022-03-05
1 A 2021-10-26 131 2022-03-06
1 A 2021-10-26 132 2022-03-07
1 A 2021-10-26 133 2022-03-08
1 A 2021-10-26 134 2022-03-09
1 A 2021-10-26 135 2022-03-10
1 A 2021-10-26 136 2022-03-11
1 A 2021-10-26 137 2022-03-12
1 A 2021-10-26 138 2022-03-13
1 A 2021-10-26 139 2022-03-14
1 A 2021-10-26 140 2022-03-15
1 A 2021-10-26 141 2022-03-16
1 A 2021-10-26 142 2022-03-17
1 A 2021-10-26 143 2022-03-18
1 A 2021-10-26 144 2022-03-19
1 A 2021-10-26 145 2022-03-20
1 A 2021-10-26 146 2022-03-21
1 A 2021-10-26 147 2022-03-22
1 A 2021-10-26 148 2022-03-23
1 A 2021-10-26 149 2022-03-24
1 A 2021-10-26 150 2022-03-25
1 A 2021-10-26 151 2022-03-26
1 A 2021-10-26 152 2022-03-27
1 A 2021-10-26 153 2022-03-28
1 A 2021-10-26 154 2022-03-29
1 A 2021-10-26 155 2022-03-30
2 B 2021-10-05 0 2021-10-05
2 B 2021-10-05 1 2021-10-06
2 B 2021-10-05 2 2021-10-07
2 B 2021-10-05 3 2021-10-08
2 B 2021-10-05 4 2021-10-09
2 B 2021-10-05 5 2021-10-10
2 B 2021-10-05 6 2021-10-11
2 B 2021-10-05 7 2021-10-12
2 B 2021-10-05 8 2021-10-13
2 B 2021-10-05 9 2021-10-14
2 B 2021-10-05 10 2021-10-15
2 B 2021-10-05 11 2021-10-16
2 B 2021-10-05 12 2021-10-17
2 B 2021-10-05 13 2021-10-18
2 B 2021-10-05 14 2021-10-19
2 B 2021-10-05 15 2021-10-20
2 B 2021-10-05 16 2021-10-21
2 B 2021-10-05 17 2021-10-22
2 B 2021-10-05 18 2021-10-23
2 B 2021-10-05 19 2021-10-24
2 B 2021-10-05 20 2021-10-25
2 B 2021-10-05 21 2021-10-26
2 B 2021-10-05 22 2021-10-27
2 B 2021-10-05 23 2021-10-28
2 B 2021-10-05 24 2021-10-29
2 B 2021-10-05 25 2021-10-30
2 B 2021-10-05 26 2021-10-31
2 B 2021-10-05 27 2021-11-01
2 B 2021-10-05 28 2021-11-02
2 B 2021-10-05 29 2021-11-03
2 B 2021-10-05 30 2021-11-04
2 B 2021-10-05 31 2021-11-05
2 B 2021-10-05 32 2021-11-06
2 B 2021-10-05 33 2021-11-07
2 B 2021-10-05 34 2021-11-08
2 B 2021-10-05 35 2021-11-09
2 B 2021-10-05 36 2021-11-10
2 B 2021-10-05 37 2021-11-11
2 B 2021-10-05 38 2021-11-12
2 B 2021-10-05 39 2021-11-13
2 B 2021-10-05 40 2021-11-14
2 B 2021-10-05 41 2021-11-15
2 B 2021-10-05 42 2021-11-16
2 B 2021-10-05 43 2021-11-17
id old_rowid Open 上层属性 days workday
1 1 2022-01-01 1 0 2022-01-01
1 1 2022-01-01 1 1 2022-01-02
1 1 2022-01-01 1 2 2022-01-03
1 1 2022-01-01 1 3 2022-01-04
1 1 2022-01-01 1 4 2022-01-05
1 1 2022-01-01 1 5 2022-01-06
1 1 2022-01-01 1 6 2022-01-07
2 1 2022-01-08 2 0 2022-01-08
2 1 2022-01-08 2 1 2022-01-09
2 1 2022-01-08 2 2 2022-01-10
2 1 2022-01-08 2 3 2022-01-11
2 1 2022-01-08 2 4 2022-01-12
2 1 2022-01-08 2 5 2022-01-13
2 1 2022-01-08 2 6 2022-01-14
3 1 2022-01-15 3 0 2022-01-15
3 1 2022-01-15 3 1 2022-01-16
3 1 2022-01-15 3 2 2022-01-17
3 1 2022-01-15 3 3 2022-01-18
3 1 2022-01-15 3 4 2022-01-19
3 1 2022-01-15 3 5 2022-01-20
3 1 2022-01-15 3 6 2022-01-21
4 1 2022-01-22 4 0 2022-01-22
4 1 2022-01-22 4 1 2022-01-23
4 1 2022-01-22 4 2 2022-01-24
4 1 2022-01-22 4 3 2022-01-25
4 1 2022-01-22 4 4 2022-01-26
4 1 2022-01-22 4 5 2022-01-27
4 1 2022-01-22 4 6 2022-01-28
5 2 2022-01-29 1 0 2022-01-29
5 2 2022-01-29 1 1 2022-01-30
5 2 2022-01-29 1 2 2022-01-31
5 2 2022-01-29 1 3 2022-02-01
5 2 2022-01-29 1 4 2022-02-02
5 2 2022-01-29 1 5 2022-02-03
5 2 2022-01-29 1 6 2022-02-04
6 2 2022-02-05 2 0 2022-02-05
6 2 2022-02-05 2 1 2022-02-06
6 2 2022-02-05 2 2 2022-02-07
6 2 2022-02-05 2 3 2022-02-08
6 2 2022-02-05 2 4 2022-02-09
6 2 2022-02-05 2 5 2022-02-10
6 2 2022-02-05 2 6 2022-02-11
7 2 2022-02-12 3 0 2022-02-12
7 2 2022-02-12 3 1 2022-02-13
7 2 2022-02-12 3 2 2022-02-14
7 2 2022-02-12 3 3 2022-02-15
7 2 2022-02-12 3 4 2022-02-16
7 2 2022-02-12 3 5 2022-02-17
7 2 2022-02-12 3 6 2022-02-18
8 2 2022-02-19 4 0 2022-02-19
8 2 2022-02-19 4 1 2022-02-20
8 2 2022-02-19 4 2 2022-02-21
8 2 2022-02-19 4 3 2022-02-22
8 2 2022-02-19 4 4 2022-02-23
8 2 2022-02-19 4 5 2022-02-24
8 2 2022-02-19 4 6 2022-02-25
9 3 2022-02-26 1 0 2022-02-26
9 3 2022-02-26 1 1 2022-02-27
9 3 2022-02-26 1 2 2022-02-28
9 3 2022-02-26 1 3 2022-03-01
9 3 2022-02-26 1 4 2022-03-02
9 3 2022-02-26 1 5 2022-03-03
9 3 2022-02-26 1 6 2022-03-04
10 3 2022-03-05 2 0 2022-03-05
10 3 2022-03-05 2 1 2022-03-06
10 3 2022-03-05 2 2 2022-03-07
10 3 2022-03-05 2 3 2022-03-08
10 3 2022-03-05 2 4 2022-03-09
10 3 2022-03-05 2 5 2022-03-10
10 3 2022-03-05 2 6 2022-03-11
11 3 2022-03-12 3 0 2022-03-12
11 3 2022-03-12 3 1 2022-03-13
11 3 2022-03-12 3 2 2022-03-14
11 3 2022-03-12 3 3 2022-03-15
11 3 2022-03-12 3 4 2022-03-16
11 3 2022-03-12 3 5 2022-03-17
11 3 2022-03-12 3 6 2022-03-18
12 3 2022-03-19 4 0 2022-03-19
12 3 2022-03-19 4 1 2022-03-20
12 3 2022-03-19 4 2 2022-03-21
12 3 2022-03-19 4 3 2022-03-22
12 3 2022-03-19 4 4 2022-03-23
12 3 2022-03-19 4 5 2022-03-24
12 3 2022-03-19 4 6 2022-03-25
13 3 2022-03-26 5 0 2022-03-26
13 3 2022-03-26 5 1 2022-03-27
13 3 2022-03-26 5 2 2022-03-28
13 3 2022-03-26 5 3 2022-03-29
13 3 2022-03-26 5 4 2022-03-30
13 3 2022-03-26 5 5 2022-03-31
13 3 2022-03-26 5 6 2022-04-01
14 4 2022-04-02 1 0 2022-04-02
14 4 2022-04-02 1 1 2022-04-03
14 4 2022-04-02 1 2 2022-04-04
14 4 2022-04-02 1 3 2022-04-05
14 4 2022-04-02 1 4 2022-04-06
14 4 2022-04-02 1 5 2022-04-07
14 4 2022-04-02 1 6 2022-04-08
15 4 2022-04-09 2 0 2022-04-09
15 4 2022-04-09 2 1 2022-04-10
15 4 2022-04-09 2 2 2022-04-11
15 4 2022-04-09 2 3 2022-04-12
15 4 2022-04-09 2 4 2022-04-13
15 4 2022-04-09 2 5 2022-04-14
15 4 2022-04-09 2 6 2022-04-15
16 4 2022-04-16 3 0 2022-04-16
16 4 2022-04-16 3 1 2022-04-17
16 4 2022-04-16 3 2 2022-04-18
16 4 2022-04-16 3 3 2022-04-19
16 4 2022-04-16 3 4 2022-04-20
16 4 2022-04-16 3 5 2022-04-21
16 4 2022-04-16 3 6 2022-04-22
17 4 2022-04-23 4 0 2022-04-23
17 4 2022-04-23 4 1 2022-04-24
17 4 2022-04-23 4 2 2022-04-25
17 4 2022-04-23 4 3 2022-04-26
17 4 2022-04-23 4 4 2022-04-27
17 4 2022-04-23 4 5 2022-04-28
17 4 2022-04-23 4 6 2022-04-29
18 5 2022-04-30 1 0 2022-04-30
18 5 2022-04-30 1 1 2022-05-01
18 5 2022-04-30 1 2 2022-05-02
18 5 2022-04-30 1 3 2022-05-03
18 5 2022-04-30 1 4 2022-05-04
18 5 2022-04-30 1 5 2022-05-05
18 5 2022-04-30 1 6 2022-05-06
19 5 2022-05-07 2 0 2022-05-07
19 5 2022-05-07 2 1 2022-05-08
19 5 2022-05-07 2 2 2022-05-09
19 5 2022-05-07 2 3 2022-05-10
19 5 2022-05-07 2 4 2022-05-11
19 5 2022-05-07 2 5 2022-05-12
19 5 2022-05-07 2 6 2022-05-13
20 5 2022-05-14 3 0 2022-05-14
20 5 2022-05-14 3 1 2022-05-15
20 5 2022-05-14 3 2 2022-05-16
20 5 2022-05-14 3 3 2022-05-17
20 5 2022-05-14 3 4 2022-05-18
20 5 2022-05-14 3 5 2022-05-19
20 5 2022-05-14 3 6 2022-05-20
21 5 2022-05-21 4 0 2022-05-21
21 5 2022-05-21 4 1 2022-05-22
21 5 2022-05-21 4 2 2022-05-23
21 5 2022-05-21 4 3 2022-05-24
21 5 2022-05-21 4 4 2022-05-25
21 5 2022-05-21 4 5 2022-05-26
21 5 2022-05-21 4 6 2022-05-27
22 6 2022-05-28 1 0 2022-05-28
22 6 2022-05-28 1 1 2022-05-29
22 6 2022-05-28 1 2 2022-05-30
22 6 2022-05-28 1 3 2022-05-31
22 6 2022-05-28 1 4 2022-06-01
22 6 2022-05-28 1 5 2022-06-02
22 6 2022-05-28 1 6 2022-06-03
23 6 2022-06-04 2 0 2022-06-04
23 6 2022-06-04 2 1 2022-06-05
23 6 2022-06-04 2 2 2022-06-06
23 6 2022-06-04 2 3 2022-06-07
23 6 2022-06-04 2 4 2022-06-08
23 6 2022-06-04 2 5 2022-06-09
23 6 2022-06-04 2 6 2022-06-10
24 6 2022-06-11 3 0 2022-06-11
24 6 2022-06-11 3 1 2022-06-12
24 6 2022-06-11 3 2 2022-06-13
24 6 2022-06-11 3 3 2022-06-14
24 6 2022-06-11 3 4 2022-06-15
24 6 2022-06-11 3 5 2022-06-16
24 6 2022-06-11 3 6 2022-06-17
25 6 2022-06-18 4 0 2022-06-18
25 6 2022-06-18 4 1 2022-06-19
25 6 2022-06-18 4 2 2022-06-20
25 6 2022-06-18 4 3 2022-06-21
25 6 2022-06-18 4 4 2022-06-22
25 6 2022-06-18 4 5 2022-06-23
25 6 2022-06-18 4 6 2022-06-24
26 6 2022-06-25 5 0 2022-06-25
26 6 2022-06-25 5 1 2022-06-26
26 6 2022-06-25 5 2 2022-06-27
26 6 2022-06-25 5 3 2022-06-28
26 6 2022-06-25 5 4 2022-06-29
26 6 2022-06-25 5 5 2022-06-30
26 6 2022-06-25 5 6 2022-07-01
27 7 2022-07-02 1 0 2022-07-02
27 7 2022-07-02 1 1 2022-07-03
27 7 2022-07-02 1 2 2022-07-04
27 7 2022-07-02 1 3 2022-07-05
27 7 2022-07-02 1 4 2022-07-06
27 7 2022-07-02 1 5 2022-07-07
27 7 2022-07-02 1 6 2022-07-08
28 7 2022-07-09 2 0 2022-07-09
28 7 2022-07-09 2 1 2022-07-10
28 7 2022-07-09 2 2 2022-07-11
28 7 2022-07-09 2 3 2022-07-12
28 7 2022-07-09 2 4 2022-07-13
28 7 2022-07-09 2 5 2022-07-14
28 7 2022-07-09 2 6 2022-07-15
29 7 2022-07-16 3 0 2022-07-16
29 7 2022-07-16 3 1 2022-07-17
29 7 2022-07-16 3 2 2022-07-18
29 7 2022-07-16 3 3 2022-07-19
Activity month week1 week2 week3 week4 week5
A 1 7 7 7 7
A 2 7 7 7 7
A 3 7 7 7 7 5
B 1 7 7 4
C 2 7 7 7
C 3 7 7 7 7 7
C 4 7 7 3
D 10 3 7 7 7
D 11 7 7 7 7 7
D 12 7 3 7
create temp table aa as select rowid old_rowid,* from calculating_days_between_date_duration; cli_unstack~aa~Open,Close; create temp table bb as select old_rowid,Open,上层属性,'0-'||((udf_strtotime(close)-udf_strtotime(Open))/60/60/24) days from aa_unstack order by old_rowid,上层属性; create temp table cc as select old_rowid,Open,上层属性,split_num(days) days from bb; cli_split_data~cc~,~days; create temp table dd as select *,date(Open,'+'||days||' days') workday from ccsplit; create temp table aaa as select Activity,Start Open,Finish Close from activity_start_finish; create temp table bbb as select Activity, Open,'0-'||((udf_strtotime(close)-udf_strtotime(Open))/60/60/24) days from aaa order by Activity; create temp table ccc as select Activity,Open,split_num(days) days from bbb; cli_split_data~ccc~,~days; create temp table ddd as select *,date(Open,'+'||days||' days') workday from cccsplit; select * from ddd limit 200; select * from dd limit 200; create temp table aaaa as select Activity,old_rowid,上层属性, workday from ddd join dd using(workday); create temp table bbbb as select Activity,old_rowid month,'week'||上层属性 week ,count(*) c_wkdays from aaaa group by Activity,old_rowid,上层属性; cli_create_two_dim~bbbb~week~c_wkdays; select * from bbbb_two_dim;if so,some sql for your reference.
- waw thank you. I still haven't mastered sql but I'll try since you gave an example
