1
00:01.640 --> 00:06.280
So let's solve one real time SQL scenario.

2
00:07.960 --> 00:15.680
So we are using this scenario in our company for so many times for comparison between the current month

3
00:15.680 --> 00:24.040
data with the previous month data or the current year data with the previous year data for some specific

4
00:24.040 --> 00:25.680
products or category wise.

5
00:26.320 --> 00:28.920
So I will show you one solution over here.

6
00:29.720 --> 00:37.400
So in this is a problem statement in the finance accounting scenarios previous month, ending balance

7
00:38.400 --> 00:42.560
should be equal to the next or current month beginning balance.

8
00:43.760 --> 00:50.360
We have to identify the records where previous month ending balance is not equals to the current oblique.

9
00:50.360 --> 00:51.360
Next month.

10
00:51.400 --> 00:59.760
Beginning balance and give those records to the management or leadership team to check to further check

11
00:59.760 --> 01:03.480
on via reporting or dashboard or some kind of thing.

12
01:03.480 --> 01:03.920
Right?

13
01:04.520 --> 01:12.640
So for that purpose, I have created this particular table month category sales table.

14
01:12.640 --> 01:14.600
In which fiscal period is there.

15
01:15.320 --> 01:18.680
Category is there beginning balance and ending balance is there right.

16
01:19.280 --> 01:28.820
So I have so I have currently two months of data for different categories like March 2nd 025 and April

17
01:28.820 --> 01:29.920
2nd 025.

18
01:30.040 --> 01:39.600
And I want to compare March data with April data for each category wise, where I want to compare the

19
01:40.480 --> 01:46.880
March ending balance with the beginning balance of April for each category.

20
01:47.800 --> 01:53.480
Right March ending balance with the beginning balance of April for each category.

21
01:54.040 --> 01:57.120
So this is my problem statement.

22
01:57.600 --> 01:59.210
Now I have solved this.

23
02:00.890 --> 02:03.130
Let me show you the data over here.

24
02:04.730 --> 02:06.530
So this is my data.

25
02:06.970 --> 02:09.330
So I have solved this particular.

26
02:12.210 --> 02:17.890
Problem with three different patterns or three different solutions.

27
02:18.770 --> 02:19.210
Right.

28
02:20.130 --> 02:22.250
So this is my first solution.

29
02:22.250 --> 02:25.050
So I will show you all the solution one by one.

30
02:25.890 --> 02:27.610
So what I am doing over here.

31
02:29.570 --> 02:40.450
So my problem statement is I want to compare the ending balance of the march ending balance of the march.

32
02:40.970 --> 02:48.050
So see with the beginning balance of the April ending balance of the march with the beginning balance

33
02:48.050 --> 02:48.890
of the April.

34
02:49.370 --> 02:51.770
So what I am doing over here, this is my.

35
02:54.570 --> 02:55.650
I am doing over.

36
02:55.690 --> 02:59.130
This is the scenario I am solving with the union all statement.

37
02:59.530 --> 03:01.810
So over here I have mentioned the category.

38
03:02.330 --> 03:08.250
Then for March my main thing is not the beginning balance.

39
03:08.250 --> 03:13.730
My main thing is ending balance USD right for March and for April.

40
03:14.370 --> 03:23.570
My main required thing is beginning balance of USD with not the ending balance, right?

41
03:23.890 --> 03:27.850
Because I want to compare with the ending balance with beginning balance.

42
03:29.050 --> 03:33.210
So all the columns in the union all will be unique, right?

43
03:33.210 --> 03:33.690
One.

44
03:34.290 --> 03:35.330
This is my second column.

45
03:35.330 --> 03:36.690
This is my third column.

46
03:37.410 --> 03:38.330
So over here.

47
03:40.770 --> 03:43.810
So if I run one minute.

48
03:46.970 --> 03:52.210
Now if I run this particular thing, let's see what result it is giving.

49
03:52.250 --> 03:52.650
See?

50
03:55.940 --> 03:59.580
This is the perfect data center wise.

51
03:59.580 --> 04:00.940
Two rows are coming, right?

52
04:01.300 --> 04:02.980
So this is my beginning balance.

53
04:02.980 --> 04:03.940
And this is my.

54
04:08.620 --> 04:10.180
Da finance ending balance.

55
04:10.180 --> 04:12.540
So c t final ending balance.

56
04:13.140 --> 04:15.740
And this is my t finance beginning balance.

57
04:16.540 --> 04:26.020
But I want to plug it into one single row because I don't want 14 records I want only seven records.

58
04:26.020 --> 04:35.020
So what I am doing now outside this query, I'm again doing the sum of this particular column.

59
04:35.020 --> 04:42.580
Beginning balance and ending balance USD based upon category means group by.

60
04:42.580 --> 04:44.820
I want to take the category right.

61
04:45.180 --> 04:46.740
I want to take the category.

62
04:47.220 --> 04:50.140
So now if I run this specific query.

63
04:53.340 --> 05:00.900
So it will give me the give me one record for each category, one record for each category.

64
05:01.100 --> 05:09.620
So for if we consider t finance, then it will be t finance 200 beginning balance and t finance 200

65
05:09.660 --> 05:10.460
ending balance.

66
05:10.740 --> 05:16.580
And then I'm comparing ending balance minus beginning balance as difference.

67
05:16.580 --> 05:20.180
Sum of ending balance minus sum of beginning balance as difference.

68
05:24.180 --> 05:24.660
Right.

69
05:25.380 --> 05:27.740
So it will give me specific difference.

70
05:28.300 --> 05:30.100
So now let's see.

71
05:31.780 --> 05:37.740
So if we see over here for this uh test finance.

72
05:38.140 --> 05:42.340
Test finance see 1st March 400 is ending balance.

73
05:42.340 --> 05:47.500
And 1st April 450 is 0.5 T is beginning balance.

74
05:47.900 --> 05:49.620
So this is not equal right.

75
05:50.820 --> 05:55.270
So difference would be we can say 50.50 minus.

76
05:55.950 --> 06:02.230
And for the corp assets also you can see 200 and 450.

77
06:03.390 --> 06:06.230
So 200.50 will be my.

78
06:08.510 --> 06:09.030
Difference.

79
06:09.030 --> 06:11.030
That is also in the minus right.

80
06:12.550 --> 06:14.190
That is also in the minus.

81
06:18.550 --> 06:20.110
So now if I execute this.

82
06:22.710 --> 06:29.350
Query I am getting the exact thing what I have told you right.

83
06:33.070 --> 06:38.750
So normally we are comparing the two different uh result set using this.

84
06:40.990 --> 06:41.870
Union hall.

85
06:46.950 --> 06:47.510
Close.

86
06:47.510 --> 06:47.950
Right.

87
06:49.590 --> 06:57.230
Now, another thing we can say is we can compare the same data with lead and lead lag function as well.

88
06:58.030 --> 07:02.470
So let me so see.

89
07:02.990 --> 07:10.350
Now if I run this particular query I want to compare.

90
07:12.430 --> 07:13.630
The ending balance.

91
07:15.230 --> 07:16.870
Ending balance of the.

92
07:21.150 --> 07:23.110
March ending balance of the march.

93
07:23.110 --> 07:25.350
So let me execute this particular query.

94
07:25.990 --> 07:28.470
Now I want to compare.

95
07:30.550 --> 07:32.150
Ending balance.

96
07:39.390 --> 07:41.910
I want to compare this ending balance.

97
07:47.310 --> 07:50.560
Here I want to compare the beginning balance.

98
07:52.760 --> 07:54.600
Or beginning balance of the.

99
07:56.640 --> 07:57.640
Next month.

100
07:57.640 --> 08:02.560
Beginning balance of the next month with the ending balance of the previous month.

101
08:03.000 --> 08:06.760
So that is why I have used over here lead and lag function.

102
08:07.280 --> 08:16.360
So if you can see over here I have used the lead and lag function lead of beginning balance USD.

103
08:16.880 --> 08:21.760
So now what will give me the lead of beginning balance USD in this particular case?

104
08:22.520 --> 08:22.760
Right.

105
08:22.760 --> 08:26.200
This is my first month and this is my second month.

106
08:26.560 --> 08:29.880
So over here I want to compare this 400.

107
08:32.080 --> 08:35.480
Point 50 with lag of.

108
08:38.960 --> 08:41.040
Lag of ending balance USD.

109
08:41.520 --> 08:43.200
So lag means previous month.

110
08:43.200 --> 08:45.320
Value lag means previous month value.

111
08:45.640 --> 08:50.880
So if this is my current month, then it will take this particular previous month value, right?

112
08:50.920 --> 08:53.560
So that will be and that is also ending balance.

113
08:54.120 --> 08:56.120
So 200.00.

114
08:56.440 --> 08:58.040
And if we consider.

115
09:00.640 --> 09:02.640
The difference then it will be.

116
09:05.680 --> 09:10.000
200 -400.55 which is -200.5.

117
09:10.040 --> 09:10.480
Right.

118
09:11.560 --> 09:14.560
So that is why I have used over here lead and lag function.

119
09:14.560 --> 09:20.200
So if we run this particular query and I have done the partition by category.

120
09:20.240 --> 09:20.640
Why.

121
09:21.120 --> 09:28.480
Because I want to check for each category only for corporate assets.

122
09:28.480 --> 09:30.920
It will uh check separately.

123
09:30.920 --> 09:35.360
Then for views it will check separately for t finance it will check separately.

124
09:35.360 --> 09:39.400
Beginning balance of ending balance of previous month with the.

125
09:41.600 --> 09:43.640
Beginning balance of the next month.

126
09:44.800 --> 09:48.400
So that is why I have used over here lead and leg functions.

127
09:49.400 --> 09:52.640
So now if you see this will also give me the same result.

128
09:53.080 --> 09:54.640
What the above query is given.

129
09:54.640 --> 09:57.520
So this is the solution pattern two.

130
09:57.560 --> 10:02.880
We can say that and we have done over here we have used the derived table.

131
10:02.920 --> 10:11.040
Because if we are seeing over here we cannot do directly sum over here we cannot do directly sum over

132
10:11.040 --> 10:12.040
here sum of lead.

133
10:12.040 --> 10:18.240
And then all this thing and sum of leg and then all this thing, because that is not allowed in SQL,

134
10:18.280 --> 10:20.240
that is not allowed in SQL, right.

135
10:21.320 --> 10:26.760
So that is why in the derived table concept I have used and then done the.

136
10:30.840 --> 10:32.080
Required calculation.

137
10:32.080 --> 10:33.480
Same as the above query.

138
10:34.840 --> 10:39.440
Over here I have used the union, whereas over here I have used the lead and leg functions.

139
10:39.800 --> 10:40.880
So what is my.

140
10:45.050 --> 10:54.450
My, uh, way of trying to convince you is that we can solve one particular problem with multiple solutions

141
10:54.450 --> 10:56.890
or multiple ways, right?

142
10:57.650 --> 10:59.130
So this is my second way.

143
10:59.530 --> 11:03.450
Now, the third way is multiple cities concept.

144
11:03.930 --> 11:06.090
So this is very simple one.

145
11:07.290 --> 11:08.570
This is very simple one.

146
11:08.570 --> 11:12.570
In the first city I have taken the March data.

147
11:12.610 --> 11:15.770
Then in the second city I have taken the April data.

148
11:15.770 --> 11:16.210
Right.

149
11:16.610 --> 11:24.890
And then in the third, uh, third one I am just doing the join and join based upon the category.

150
11:24.890 --> 11:25.330
Right.

151
11:25.810 --> 11:27.370
Join based upon the category.

152
11:27.810 --> 11:31.250
Because in each month I have only one single.

153
11:33.290 --> 11:36.210
I have unique categories for all the March data.

154
11:36.250 --> 11:38.770
I have unique categories for all the April data.

155
11:39.250 --> 11:43.570
No category is repeating multiple times in the March or April write.

156
11:44.170 --> 11:47.490
No category is repeating multiple times in March or April.

157
11:47.490 --> 11:54.530
If you see this particular Excel, then data center is coming only once in March, and data center or

158
11:54.690 --> 11:58.730
data center is coming only once in April as well.

159
11:59.570 --> 12:07.170
And I have copied the result of all these three different ways, all solutions, so that you can see

160
12:07.170 --> 12:08.730
we have solved the problems.

161
12:09.810 --> 12:12.130
One common problem with three different ways.

162
12:12.410 --> 12:14.490
Now I am just comparing category.

163
12:15.210 --> 12:15.970
Ending balance.

164
12:15.970 --> 12:17.010
Beginning balance.

165
12:19.050 --> 12:20.370
Now ending balance of.

166
12:22.810 --> 12:23.530
March month.

167
12:23.530 --> 12:23.850
Right.

168
12:23.850 --> 12:26.850
So March is coming in the city.

169
12:27.170 --> 12:30.690
So that is why city dot ending balance and city two.

170
12:31.930 --> 12:33.850
So April month beginning balance.

171
12:34.810 --> 12:38.130
So that is why this will also give us the same result.

172
12:40.940 --> 12:43.220
This will also give us the same result, right?

173
12:48.860 --> 12:55.660
So this is how we can say that one problem we can solve in the multiple ways.

174
12:57.780 --> 12:58.380
In our.

175
13:01.780 --> 13:07.340
Company mostly we are using the union all scenario.

176
13:07.940 --> 13:09.700
But you can.

177
13:12.460 --> 13:22.100
Prefer other scenarios as well as per your company how they are approaching or what they are mostly

178
13:22.100 --> 13:22.660
using.

179
13:23.820 --> 13:26.940
Either they are using the city, or either they are using the union.

180
13:26.980 --> 13:29.420
All like that, right?

181
13:30.420 --> 13:33.820
But in our company, most of the time we are using the union all.

182
13:34.060 --> 13:41.620
So that is why I have kept this union all scenario at first, right.

183
13:46.860 --> 13:49.060
And over here if you see.

184
13:51.420 --> 13:52.940
For the data center.

185
13:52.940 --> 13:53.420
Right.

186
13:53.460 --> 13:55.220
So if you see for the data center.

187
13:55.820 --> 13:59.580
Ending balance is 24th March.

188
14:00.260 --> 14:09.340
Now for this same data center beginning balance you have to see that should should be 20 right.

189
14:09.580 --> 14:11.060
So there is no difference.

190
14:11.700 --> 14:16.140
That is why in terms of data center it is coming zero everywhere right.

191
14:18.180 --> 14:21.340
But now if you see for the test fin.

192
14:22.380 --> 14:29.580
So this test fin let's see this March data 400 is ending balance.

193
14:30.540 --> 14:31.740
And uh.

194
14:41.110 --> 14:45.390
Beginning balances for 15th April.

195
14:45.430 --> 14:46.750
Right in March.

196
14:46.790 --> 14:51.790
Ending balance is 400, so it will be a difference, right?

197
14:54.550 --> 14:57.430
That will be 50 difference 400.

198
14:58.190 --> 14:59.630
-450.

199
15:00.670 --> 15:02.910
So 50.5 right.

200
15:03.390 --> 15:07.990
So that our carry is also showing the same thing.

201
15:14.150 --> 15:14.630
Right.

202
15:15.110 --> 15:16.630
So thank you so much.

203
15:16.630 --> 15:24.670
This is how real time scenarios we are using in the uh, kind of finance and accounting projects for

204
15:24.670 --> 15:31.790
comparison between various months that are over various years data or various days data as well.

205
15:33.990 --> 15:34.750
Thank you.