質問編集履歴
4
記載内容の見直し
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -3,537 +3,3 @@
|
|
|
3
3
|
以下SQLのLEFTJOINしている箇所を見直したいです。
|
|
4
4
|
|
|
5
5
|
良い書き方があればご教授いただきたいです。
|
|
6
|
-
|
|
7
|
-
|
|
8
|
-
|
|
9
|
-
```
|
|
10
|
-
|
|
11
|
-
・SQL
|
|
12
|
-
|
|
13
|
-
SELECT
|
|
14
|
-
|
|
15
|
-
ROW_NUMBER() OVER(ORDER BY SCH.YYYID,SCH.StartYmd,KH.XXXID) as row_num, --テーブル変数格納用ID
|
|
16
|
-
|
|
17
|
-
SCH.YYYID,SCH.ScheduleID,KH.XXXID,
|
|
18
|
-
|
|
19
|
-
KH.DutyYmd01,KH.DataKbn01,KH.DutyID01,
|
|
20
|
-
|
|
21
|
-
KH.DutyYmd02,KH.DataKbn02,KH.DutyID02,
|
|
22
|
-
|
|
23
|
-
KH.DutyYmd03,KH.DataKbn03,KH.DutyID03,
|
|
24
|
-
|
|
25
|
-
KH.DutyYmd04,KH.DataKbn04,KH.DutyID04,
|
|
26
|
-
|
|
27
|
-
KH.DutyYmd05,KH.DataKbn05,KH.DutyID05,
|
|
28
|
-
|
|
29
|
-
KH.DutyYmd06,KH.DataKbn06,KH.DutyID06,
|
|
30
|
-
|
|
31
|
-
KH.DutyYmd07,KH.DataKbn07,KH.DutyID07,
|
|
32
|
-
|
|
33
|
-
KH.DutyYmd08,KH.DataKbn08,KH.DutyID08,
|
|
34
|
-
|
|
35
|
-
KH.DutyYmd09,KH.DataKbn09,KH.DutyID09,
|
|
36
|
-
|
|
37
|
-
KH.DutyYmd10,KH.DataKbn10,KH.DutyID10,
|
|
38
|
-
|
|
39
|
-
KH.DutyYmd11,KH.DataKbn11,KH.DutyID11,
|
|
40
|
-
|
|
41
|
-
KH.DutyYmd12,KH.DataKbn12,KH.DutyID12,
|
|
42
|
-
|
|
43
|
-
KH.DutyYmd13,KH.DataKbn13,KH.DutyID13,
|
|
44
|
-
|
|
45
|
-
KH.DutyYmd14,KH.DataKbn14,KH.DutyID14,
|
|
46
|
-
|
|
47
|
-
KH.DutyYmd15,KH.DataKbn15,KH.DutyID15,
|
|
48
|
-
|
|
49
|
-
KH.DutyYmd16,KH.DataKbn16,KH.DutyID16,
|
|
50
|
-
|
|
51
|
-
KH.DutyYmd17,KH.DataKbn17,KH.DutyID17,
|
|
52
|
-
|
|
53
|
-
KH.DutyYmd18,KH.DataKbn18,KH.DutyID18,
|
|
54
|
-
|
|
55
|
-
KH.DutyYmd19,KH.DataKbn19,KH.DutyID19,
|
|
56
|
-
|
|
57
|
-
KH.DutyYmd20,KH.DataKbn20,KH.DutyID20,
|
|
58
|
-
|
|
59
|
-
KH.DutyYmd21,KH.DataKbn21,KH.DutyID21,
|
|
60
|
-
|
|
61
|
-
KH.DutyYmd22,KH.DataKbn22,KH.DutyID22,
|
|
62
|
-
|
|
63
|
-
KH.DutyYmd23,KH.DataKbn23,KH.DutyID23,
|
|
64
|
-
|
|
65
|
-
KH.DutyYmd24,KH.DataKbn24,KH.DutyID24,
|
|
66
|
-
|
|
67
|
-
KH.DutyYmd25,KH.DataKbn25,KH.DutyID25,
|
|
68
|
-
|
|
69
|
-
KH.DutyYmd26,KH.DataKbn26,KH.DutyID26,
|
|
70
|
-
|
|
71
|
-
KH.DutyYmd27,KH.DataKbn27,KH.DutyID27,
|
|
72
|
-
|
|
73
|
-
KH.DutyYmd28,KH.DataKbn28,KH.DutyID28,
|
|
74
|
-
|
|
75
|
-
KH.DutyYmd29,KH.DataKbn29,KH.DutyID29,
|
|
76
|
-
|
|
77
|
-
KH.DutyYmd30,KH.DataKbn30,KH.DutyID30,
|
|
78
|
-
|
|
79
|
-
KH.DutyYmd31,KH.DataKbn31,KH.DutyID31,
|
|
80
|
-
|
|
81
|
-
ST01.XXXID AS StaffID01,ST02.XXXID AS StaffID02,ST03.XXXID AS StaffID03,ST04.XXXID AS StaffID04,ST05.XXXID AS StaffID05,
|
|
82
|
-
|
|
83
|
-
ST06.XXXID AS StaffID06,ST07.XXXID AS StaffID07,ST08.XXXID AS StaffID08,ST09.XXXID AS StaffID09,ST10.XXXID AS StaffID10,
|
|
84
|
-
|
|
85
|
-
ST11.XXXID AS StaffID11,ST12.XXXID AS StaffID12,ST13.XXXID AS StaffID13,ST14.XXXID AS StaffID14,ST15.XXXID AS StaffID15,
|
|
86
|
-
|
|
87
|
-
ST16.XXXID AS StaffID16,ST17.XXXID AS StaffID17,ST18.XXXID AS StaffID18,ST19.XXXID AS StaffID19,ST20.XXXID AS StaffID20,
|
|
88
|
-
|
|
89
|
-
ST21.XXXID AS StaffID21,ST22.XXXID AS StaffID22,ST23.XXXID AS StaffID23,ST24.XXXID AS StaffID24,ST25.XXXID AS StaffID25,
|
|
90
|
-
|
|
91
|
-
ST26.XXXID AS StaffID26,ST27.XXXID AS StaffID27,ST28.XXXID AS StaffID28,ST29.XXXID AS StaffID29,ST30.XXXID AS StaffID30,
|
|
92
|
-
|
|
93
|
-
ST31.XXXID AS StaffID31
|
|
94
|
-
|
|
95
|
-
FROM
|
|
96
|
-
|
|
97
|
-
(
|
|
98
|
-
|
|
99
|
-
SELECT
|
|
100
|
-
|
|
101
|
-
ROW_NUMBER() OVER(PARTITION BY SCH.YYYID,SCH.StartYmd ORDER BY SCH.ScheduleSts DESC ,SCH.UpdDtTm DESC,SCH.ScheduleID DESC) AS sch_num,
|
|
102
|
-
|
|
103
|
-
SCH.YYYID,SCH.ScheduleID,SCH.ScheduleSts,SCH.StartYmd,SCH.UpdDtTm
|
|
104
|
-
|
|
105
|
-
FROM
|
|
106
|
-
|
|
107
|
-
M_Schedule SCH
|
|
108
|
-
|
|
109
|
-
WHERE
|
|
110
|
-
|
|
111
|
-
--(SCH.StartYmd >= @cRangeDate)
|
|
112
|
-
|
|
113
|
-
(SCH.StartYmd >= @inStartDate) AND
|
|
114
|
-
|
|
115
|
-
(SCH.StartYmd <= @inEndDate)
|
|
116
|
-
|
|
117
|
-
) SCH
|
|
118
|
-
|
|
119
|
-
INNER JOIN [Servis].dbo.D_Kinmuhyo KH ON
|
|
120
|
-
|
|
121
|
-
(KH.YYYID = SCH.YYYID) AND
|
|
122
|
-
|
|
123
|
-
(KH.ScheduleID = SCH.ScheduleID)
|
|
124
|
-
|
|
125
|
-
LEFT JOIN XXXX_TBL ST01 ON
|
|
126
|
-
|
|
127
|
-
(ST01.XXXID = KH.XXXID) AND (ST01.XXXCode = KH.YYYID) AND (ST01.HisDate <= KH.DutyYmd01) AND (KH.DutyYmd01 <= dbo.F_GET_HISDATETO(ST01.XXXID,ST01.HisNo))
|
|
128
|
-
|
|
129
|
-
～省略～
|
|
130
|
-
|
|
131
|
-
LEFT JOIN XXXX_TBL ST31 ON
|
|
132
|
-
|
|
133
|
-
(ST31.XXXID = KH.XXXID) AND (ST31.XXXCode = KH.YYYID) AND (ST31.HisDate <= KH.DutyYmd31) AND (KH.DutyYmd31 <= dbo.F_GET_HISDATETO(ST31.XXXID,ST31.HisNo))
|
|
134
|
-
|
|
135
|
-
WHERE
|
|
136
|
-
|
|
137
|
-
(SCH.sch_num = 1)
|
|
138
|
-
|
|
139
|
-
AND EXISTS(
|
|
140
|
-
|
|
141
|
-
SELECT S.*
|
|
142
|
-
|
|
143
|
-
FROM XXXX_TBL S
|
|
144
|
-
|
|
145
|
-
WHERE
|
|
146
|
-
|
|
147
|
-
(S.XXXID = KH.XXXID) AND
|
|
148
|
-
|
|
149
|
-
(SUBSTRING(CAST(S.HisDate AS VARCHAR),1,6) <= SUBSTRING(CAST(@inEndDate AS VARCHAR),1,6)) AND
|
|
150
|
-
|
|
151
|
-
(SUBSTRING(CAST(@inStartDate AS VARCHAR),1,6) <= SUBSTRING(CAST(dbo.F_GET_HISDATETO(S.XXXID,S.HisNo) AS VARCHAR),1,6)) AND
|
|
152
|
-
|
|
153
|
-
(
|
|
154
|
-
|
|
155
|
-
((@inSectionID = 0) AND (1=1)) OR
|
|
156
|
-
|
|
157
|
-
((@inSectionID <> 0) AND (S.XXXCode = @inSectionID))
|
|
158
|
-
|
|
159
|
-
)
|
|
160
|
-
|
|
161
|
-
)
|
|
162
|
-
|
|
163
|
-
```
|
|
164
|
-
|
|
165
|
-
|
|
166
|
-
|
|
167
|
-
```
|
|
168
|
-
|
|
169
|
-
テーブル定義
|
|
170
|
-
|
|
171
|
-
・XXXX_TBL
|
|
172
|
-
|
|
173
|
-
CREATE TABLE [dbo].[XXXX_TBL](
|
|
174
|
-
|
|
175
|
-
[StaffID] [int] NOT NULL,
|
|
176
|
-
|
|
177
|
-
[HisNo] [int] NOT NULL,
|
|
178
|
-
|
|
179
|
-
[StaffCode] [nvarchar](20) NULL,
|
|
180
|
-
|
|
181
|
-
[HisDate] [int] NULL,
|
|
182
|
-
|
|
183
|
-
[StaffName] [nvarchar](40) NULL,
|
|
184
|
-
|
|
185
|
-
[KinmuByoutouCode] [int] NULL,
|
|
186
|
-
|
|
187
|
-
[KinmuByoutou] [nvarchar](30) NOT NULL,
|
|
188
|
-
|
|
189
|
-
[ShozokuByoutouCode] [int] NULL,
|
|
190
|
-
|
|
191
|
-
[ShozokuByoutou] [nvarchar](30) NOT NULL,
|
|
192
|
-
|
|
193
|
-
[ShikakuCode] [int] NULL,
|
|
194
|
-
|
|
195
|
-
[Shikaku] [nvarchar](20) NOT NULL,
|
|
196
|
-
|
|
197
|
-
[ShokuseiCode] [int] NULL,
|
|
198
|
-
|
|
199
|
-
[Shokusei] [nvarchar](20) NOT NULL,
|
|
200
|
-
|
|
201
|
-
[Seibetsu] [nvarchar](1) NOT NULL,
|
|
202
|
-
|
|
203
|
-
[ChangeReasonKbn] [int] NULL,
|
|
204
|
-
|
|
205
|
-
[TOUROKUYMD] [datetime] NULL,
|
|
206
|
-
|
|
207
|
-
[TOUROKUCD] [nvarchar](50) NULL,
|
|
208
|
-
|
|
209
|
-
[KOUSHINYMD] [datetime] NULL,
|
|
210
|
-
|
|
211
|
-
[KOUSHINCD] [nvarchar](50) NULL,
|
|
212
|
-
|
|
213
|
-
CONSTRAINT [PK_M_Staff_His] PRIMARY KEY CLUSTERED
|
|
214
|
-
|
|
215
|
-
(
|
|
216
|
-
|
|
217
|
-
[StaffID] ASC,
|
|
218
|
-
|
|
219
|
-
[HisNo] ASC
|
|
220
|
-
|
|
221
|
-
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
222
|
-
|
|
223
|
-
) ON [PRIMARY]
|
|
224
|
-
|
|
225
|
-
|
|
226
|
-
|
|
227
|
-
|
|
228
|
-
|
|
229
|
-
|
|
230
|
-
|
|
231
|
-
----------
|
|
232
|
-
|
|
233
|
-
・D_Kinmuhyo
|
|
234
|
-
|
|
235
|
-
CREATE VIEW [dbo].[D_Kinmuhyo]
|
|
236
|
-
|
|
237
|
-
AS
|
|
238
|
-
|
|
239
|
-
SELECT DISTINCT
|
|
240
|
-
|
|
241
|
-
sd.SectionID
|
|
242
|
-
|
|
243
|
-
, sd.ScheduleID
|
|
244
|
-
|
|
245
|
-
, sd.StaffID
|
|
246
|
-
|
|
247
|
-
, ss.UpdDtTm
|
|
248
|
-
|
|
249
|
-
, ss.ScheduleSts
|
|
250
|
-
|
|
251
|
-
, ss.StaffChangesCnt
|
|
252
|
-
|
|
253
|
-
, sd.DutyYmd01
|
|
254
|
-
|
|
255
|
-
, sd.DataKbn01
|
|
256
|
-
|
|
257
|
-
, sd.DutyID01
|
|
258
|
-
|
|
259
|
-
, sd.DutyYmd02
|
|
260
|
-
|
|
261
|
-
, sd.DataKbn02
|
|
262
|
-
|
|
263
|
-
, sd.DutyID02
|
|
264
|
-
|
|
265
|
-
, sd.DutyYmd03
|
|
266
|
-
|
|
267
|
-
, sd.DataKbn03
|
|
268
|
-
|
|
269
|
-
, sd.DutyID03
|
|
270
|
-
|
|
271
|
-
, sd.DutyYmd04
|
|
272
|
-
|
|
273
|
-
, sd.DataKbn04
|
|
274
|
-
|
|
275
|
-
, sd.DutyID04
|
|
276
|
-
|
|
277
|
-
, sd.DutyYmd05
|
|
278
|
-
|
|
279
|
-
, sd.DataKbn05
|
|
280
|
-
|
|
281
|
-
, sd.DutyID05
|
|
282
|
-
|
|
283
|
-
, sd.DutyYmd06
|
|
284
|
-
|
|
285
|
-
, sd.DataKbn06
|
|
286
|
-
|
|
287
|
-
, sd.DutyID06
|
|
288
|
-
|
|
289
|
-
, sd.DutyYmd07
|
|
290
|
-
|
|
291
|
-
, sd.DataKbn07
|
|
292
|
-
|
|
293
|
-
, sd.DutyID07
|
|
294
|
-
|
|
295
|
-
, sd.DutyYmd08
|
|
296
|
-
|
|
297
|
-
, sd.DataKbn08
|
|
298
|
-
|
|
299
|
-
, sd.DutyID08
|
|
300
|
-
|
|
301
|
-
, sd.DutyYmd09
|
|
302
|
-
|
|
303
|
-
, sd.DataKbn09
|
|
304
|
-
|
|
305
|
-
, sd.DutyID09
|
|
306
|
-
|
|
307
|
-
, sd.DutyYmd10
|
|
308
|
-
|
|
309
|
-
, sd.DataKbn10
|
|
310
|
-
|
|
311
|
-
, sd.DutyID10
|
|
312
|
-
|
|
313
|
-
, sd.DutyYmd11
|
|
314
|
-
|
|
315
|
-
, sd.DataKbn11
|
|
316
|
-
|
|
317
|
-
, sd.DutyID11
|
|
318
|
-
|
|
319
|
-
, sd.DutyYmd12
|
|
320
|
-
|
|
321
|
-
, sd.DataKbn12
|
|
322
|
-
|
|
323
|
-
, sd.DutyID12
|
|
324
|
-
|
|
325
|
-
, sd.DutyYmd13
|
|
326
|
-
|
|
327
|
-
, sd.DataKbn13
|
|
328
|
-
|
|
329
|
-
, sd.DutyID13
|
|
330
|
-
|
|
331
|
-
, sd.DutyYmd14
|
|
332
|
-
|
|
333
|
-
, sd.DataKbn14
|
|
334
|
-
|
|
335
|
-
, sd.DutyID14
|
|
336
|
-
|
|
337
|
-
, sd.DutyYmd15
|
|
338
|
-
|
|
339
|
-
, sd.DataKbn15
|
|
340
|
-
|
|
341
|
-
, sd.DutyID15
|
|
342
|
-
|
|
343
|
-
, sd.DutyYmd16
|
|
344
|
-
|
|
345
|
-
, sd.DataKbn16
|
|
346
|
-
|
|
347
|
-
, sd.DutyID16
|
|
348
|
-
|
|
349
|
-
, sd.DutyYmd17
|
|
350
|
-
|
|
351
|
-
, sd.DataKbn17
|
|
352
|
-
|
|
353
|
-
, sd.DutyID17
|
|
354
|
-
|
|
355
|
-
, sd.DutyYmd18
|
|
356
|
-
|
|
357
|
-
, sd.DataKbn18
|
|
358
|
-
|
|
359
|
-
, sd.DutyID18
|
|
360
|
-
|
|
361
|
-
, sd.DutyYmd19
|
|
362
|
-
|
|
363
|
-
, sd.DataKbn19
|
|
364
|
-
|
|
365
|
-
, sd.DutyID19
|
|
366
|
-
|
|
367
|
-
, sd.DutyYmd20
|
|
368
|
-
|
|
369
|
-
, sd.DataKbn20
|
|
370
|
-
|
|
371
|
-
, sd.DutyID20
|
|
372
|
-
|
|
373
|
-
, sd.DutyYmd21
|
|
374
|
-
|
|
375
|
-
, sd.DataKbn21
|
|
376
|
-
|
|
377
|
-
, sd.DutyID21
|
|
378
|
-
|
|
379
|
-
, sd.DutyYmd22
|
|
380
|
-
|
|
381
|
-
, sd.DataKbn22
|
|
382
|
-
|
|
383
|
-
, sd.DutyID22
|
|
384
|
-
|
|
385
|
-
, sd.DutyYmd23
|
|
386
|
-
|
|
387
|
-
, sd.DataKbn23
|
|
388
|
-
|
|
389
|
-
, sd.DutyID23
|
|
390
|
-
|
|
391
|
-
, sd.DutyYmd24
|
|
392
|
-
|
|
393
|
-
, sd.DataKbn24
|
|
394
|
-
|
|
395
|
-
, sd.DutyID24
|
|
396
|
-
|
|
397
|
-
, sd.DutyYmd25
|
|
398
|
-
|
|
399
|
-
, sd.DataKbn25
|
|
400
|
-
|
|
401
|
-
, sd.DutyID25
|
|
402
|
-
|
|
403
|
-
, sd.DutyYmd26
|
|
404
|
-
|
|
405
|
-
, sd.DataKbn26
|
|
406
|
-
|
|
407
|
-
, sd.DutyID26
|
|
408
|
-
|
|
409
|
-
, sd.DutyYmd27
|
|
410
|
-
|
|
411
|
-
, sd.DataKbn27
|
|
412
|
-
|
|
413
|
-
, sd.DutyID27
|
|
414
|
-
|
|
415
|
-
, sd.DutyYmd28
|
|
416
|
-
|
|
417
|
-
, sd.DataKbn28
|
|
418
|
-
|
|
419
|
-
, sd.DutyID28
|
|
420
|
-
|
|
421
|
-
, sd.DutyYmd29
|
|
422
|
-
|
|
423
|
-
, sd.DataKbn29
|
|
424
|
-
|
|
425
|
-
, sd.DutyID29
|
|
426
|
-
|
|
427
|
-
, sd.DutyYmd30
|
|
428
|
-
|
|
429
|
-
, sd.DataKbn30
|
|
430
|
-
|
|
431
|
-
, sd.DutyID30
|
|
432
|
-
|
|
433
|
-
, sd.DutyYmd31
|
|
434
|
-
|
|
435
|
-
, sd.DataKbn31
|
|
436
|
-
|
|
437
|
-
, sd.DutyID31
|
|
438
|
-
|
|
439
|
-
, ISNULL(sdo.StaffDispOrder, ISNULL(dbo.M_StaffRangeOrder.StaffDispOrder, 999)) AS StaffDispOrder
|
|
440
|
-
|
|
441
|
-
, sd.UpdCnt
|
|
442
|
-
|
|
443
|
-
FROM
|
|
444
|
-
|
|
445
|
-
dbo.ScheduleDetail AS sd
|
|
446
|
-
|
|
447
|
-
INNER JOIN dbo.Schedule AS ss
|
|
448
|
-
|
|
449
|
-
ON sd.SectionID = ss.SectionID
|
|
450
|
-
|
|
451
|
-
AND sd.ScheduleID = ss.ScheduleID
|
|
452
|
-
|
|
453
|
-
LEFT OUTER JOIN dbo.M_StaffRangeOrder
|
|
454
|
-
|
|
455
|
-
ON sd.StaffID = dbo.M_StaffRangeOrder.StaffID
|
|
456
|
-
|
|
457
|
-
AND (
|
|
458
|
-
|
|
459
|
-
ss.StartYmd <= dbo.M_StaffRangeOrder.AppliStYmdFrom
|
|
460
|
-
|
|
461
|
-
AND dbo.M_StaffRangeOrder.AppliStYmdFrom <= CONVERT(
|
|
462
|
-
|
|
463
|
-
int
|
|
464
|
-
|
|
465
|
-
, CONVERT(
|
|
466
|
-
|
|
467
|
-
varchar (8)
|
|
468
|
-
|
|
469
|
-
, CONVERT(datetime, CONVERT(varchar (8), ss.StartYmd)) + ss.DutyDayCnt - 1
|
|
470
|
-
|
|
471
|
-
, 112
|
|
472
|
-
|
|
473
|
-
)
|
|
474
|
-
|
|
475
|
-
)
|
|
476
|
-
|
|
477
|
-
OR ss.StartYmd <= dbo.M_StaffRangeOrder.AppliStYmdTo
|
|
478
|
-
|
|
479
|
-
AND dbo.M_StaffRangeOrder.AppliStYmdTo <= CONVERT(
|
|
480
|
-
|
|
481
|
-
int
|
|
482
|
-
|
|
483
|
-
, CONVERT(
|
|
484
|
-
|
|
485
|
-
varchar (8)
|
|
486
|
-
|
|
487
|
-
, CONVERT(datetime, CONVERT(varchar (8), ss.StartYmd)) + ss.DutyDayCnt - 1
|
|
488
|
-
|
|
489
|
-
, 112
|
|
490
|
-
|
|
491
|
-
)
|
|
492
|
-
|
|
493
|
-
)
|
|
494
|
-
|
|
495
|
-
OR dbo.M_StaffRangeOrder.AppliStYmdFrom <= ss.StartYmd
|
|
496
|
-
|
|
497
|
-
AND CONVERT(
|
|
498
|
-
|
|
499
|
-
int
|
|
500
|
-
|
|
501
|
-
, CONVERT(
|
|
502
|
-
|
|
503
|
-
varchar (8)
|
|
504
|
-
|
|
505
|
-
, CONVERT(datetime, CONVERT(varchar (8), ss.StartYmd)) + ss.DutyDayCnt - 1
|
|
506
|
-
|
|
507
|
-
, 112
|
|
508
|
-
|
|
509
|
-
)
|
|
510
|
-
|
|
511
|
-
) <= dbo.M_StaffRangeOrder.AppliStYmdTo
|
|
512
|
-
|
|
513
|
-
)
|
|
514
|
-
|
|
515
|
-
AND ss.SectionID = dbo.M_StaffRangeOrder.SectionID
|
|
516
|
-
|
|
517
|
-
LEFT OUTER JOIN dbo.StaffDispOrder AS sdo
|
|
518
|
-
|
|
519
|
-
ON sdo.HospitalID = ss.HospitalID
|
|
520
|
-
|
|
521
|
-
AND sdo.SectionID = ss.SectionID
|
|
522
|
-
|
|
523
|
-
AND sdo.StaffID = sd.StaffID
|
|
524
|
-
|
|
525
|
-
AND sdo.StartYmd = ss.StartYmd
|
|
526
|
-
|
|
527
|
-
```
|
|
528
|
-
|
|
529
|
-
|
|
530
|
-
|
|
531
|
-
|
|
532
|
-
|
|
533
|
-
実行プラン
|
|
534
|
-
|
|
535
|
-
|
|
536
|
-
|
|
537
|
-
|
|
538
|
-
|
|
539
|
-
|
3
テーブル定義、実行プランを更新しました。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -2,12 +2,14 @@
|
|
|
2
2
|
|
|
3
3
|
以下SQLのLEFTJOINしている箇所を見直したいです。
|
|
4
4
|
|
|
5
|
-
実際は同じ記載方法で31個JOINしています。（処理速度遅延の原因の1つと思っています）
|
|
6
|
-
|
|
7
5
|
良い書き方があればご教授いただきたいです。
|
|
8
6
|
|
|
9
7
|
|
|
10
8
|
|
|
9
|
+
```
|
|
10
|
+
|
|
11
|
+
・SQL
|
|
12
|
+
|
|
11
13
|
SELECT
|
|
12
14
|
|
|
13
15
|
ROW_NUMBER() OVER(ORDER BY SCH.YYYID,SCH.StartYmd,KH.XXXID) as row_num, --テーブル変数格納用ID
|
|
@@ -124,121 +126,7 @@
|
|
|
124
126
|
|
|
125
127
|
(ST01.XXXID = KH.XXXID) AND (ST01.XXXCode = KH.YYYID) AND (ST01.HisDate <= KH.DutyYmd01) AND (KH.DutyYmd01 <= dbo.F_GET_HISDATETO(ST01.XXXID,ST01.HisNo))
|
|
126
128
|
|
|
127
|
-
|
|
129
|
+
～省略～
|
|
128
|
-
|
|
129
|
-
(ST02.XXXID = KH.XXXID) AND (ST02.XXXCode = KH.YYYID) AND (ST02.HisDate <= KH.DutyYmd02) AND (KH.DutyYmd02 <= dbo.F_GET_HISDATETO(ST02.XXXID,ST02.HisNo))
|
|
130
|
-
|
|
131
|
-
LEFT JOIN XXXX_TBL ST03 ON
|
|
132
|
-
|
|
133
|
-
(ST03.XXXID = KH.XXXID) AND (ST03.XXXCode = KH.YYYID) AND (ST03.HisDate <= KH.DutyYmd03) AND (KH.DutyYmd03 <= dbo.F_GET_HISDATETO(ST03.XXXID,ST03.HisNo))
|
|
134
|
-
|
|
135
|
-
LEFT JOIN XXXX_TBL ST04 ON
|
|
136
|
-
|
|
137
|
-
(ST04.XXXID = KH.XXXID) AND (ST04.XXXCode = KH.YYYID) AND (ST04.HisDate <= KH.DutyYmd04) AND (KH.DutyYmd04 <= dbo.F_GET_HISDATETO(ST04.XXXID,ST04.HisNo))
|
|
138
|
-
|
|
139
|
-
LEFT JOIN XXXX_TBL ST05 ON
|
|
140
|
-
|
|
141
|
-
(ST05.XXXID = KH.XXXID) AND (ST05.XXXCode = KH.YYYID) AND (ST05.HisDate <= KH.DutyYmd05) AND (KH.DutyYmd05 <= dbo.F_GET_HISDATETO(ST05.XXXID,ST05.HisNo))
|
|
142
|
-
|
|
143
|
-
LEFT JOIN XXXX_TBL ST06 ON
|
|
144
|
-
|
|
145
|
-
(ST06.XXXID = KH.XXXID) AND (ST06.XXXCode = KH.YYYID) AND (ST06.HisDate <= KH.DutyYmd06) AND (KH.DutyYmd06 <= dbo.F_GET_HISDATETO(ST06.XXXID,ST06.HisNo))
|
|
146
|
-
|
|
147
|
-
LEFT JOIN XXXX_TBL ST07 ON
|
|
148
|
-
|
|
149
|
-
(ST07.XXXID = KH.XXXID) AND (ST07.XXXCode = KH.YYYID) AND (ST07.HisDate <= KH.DutyYmd07) AND (KH.DutyYmd07 <= dbo.F_GET_HISDATETO(ST07.XXXID,ST07.HisNo))
|
|
150
|
-
|
|
151
|
-
LEFT JOIN XXXX_TBL ST08 ON
|
|
152
|
-
|
|
153
|
-
(ST08.XXXID = KH.XXXID) AND (ST08.XXXCode = KH.YYYID) AND (ST08.HisDate <= KH.DutyYmd08) AND (KH.DutyYmd08 <= dbo.F_GET_HISDATETO(ST08.XXXID,ST08.HisNo))
|
|
154
|
-
|
|
155
|
-
LEFT JOIN XXXX_TBL ST09 ON
|
|
156
|
-
|
|
157
|
-
(ST09.XXXID = KH.XXXID) AND (ST09.XXXCode = KH.YYYID) AND (ST09.HisDate <= KH.DutyYmd09) AND (KH.DutyYmd09 <= dbo.F_GET_HISDATETO(ST09.XXXID,ST09.HisNo))
|
|
158
|
-
|
|
159
|
-
LEFT JOIN XXXX_TBL ST10 ON
|
|
160
|
-
|
|
161
|
-
(ST10.XXXID = KH.XXXID) AND (ST10.XXXCode = KH.YYYID) AND (ST10.HisDate <= KH.DutyYmd10) AND (KH.DutyYmd10 <= dbo.F_GET_HISDATETO(ST10.XXXID,ST10.HisNo))
|
|
162
|
-
|
|
163
|
-
LEFT JOIN XXXX_TBL ST11 ON
|
|
164
|
-
|
|
165
|
-
(ST11.XXXID = KH.XXXID) AND (ST11.XXXCode = KH.YYYID) AND (ST11.HisDate <= KH.DutyYmd11) AND (KH.DutyYmd11 <= dbo.F_GET_HISDATETO(ST11.XXXID,ST11.HisNo))
|
|
166
|
-
|
|
167
|
-
LEFT JOIN XXXX_TBL ST12 ON
|
|
168
|
-
|
|
169
|
-
(ST12.XXXID = KH.XXXID) AND (ST12.XXXCode = KH.YYYID) AND (ST12.HisDate <= KH.DutyYmd12) AND (KH.DutyYmd12 <= dbo.F_GET_HISDATETO(ST12.XXXID,ST12.HisNo))
|
|
170
|
-
|
|
171
|
-
LEFT JOIN XXXX_TBL ST13 ON
|
|
172
|
-
|
|
173
|
-
(ST13.XXXID = KH.XXXID) AND (ST13.XXXCode = KH.YYYID) AND (ST13.HisDate <= KH.DutyYmd13) AND (KH.DutyYmd13 <= dbo.F_GET_HISDATETO(ST13.XXXID,ST13.HisNo))
|
|
174
|
-
|
|
175
|
-
LEFT JOIN XXXX_TBL ST14 ON
|
|
176
|
-
|
|
177
|
-
(ST14.XXXID = KH.XXXID) AND (ST14.XXXCode = KH.YYYID) AND (ST14.HisDate <= KH.DutyYmd14) AND (KH.DutyYmd14 <= dbo.F_GET_HISDATETO(ST14.XXXID,ST14.HisNo))
|
|
178
|
-
|
|
179
|
-
LEFT JOIN XXXX_TBL ST15 ON
|
|
180
|
-
|
|
181
|
-
(ST15.XXXID = KH.XXXID) AND (ST15.XXXCode = KH.YYYID) AND (ST15.HisDate <= KH.DutyYmd15) AND (KH.DutyYmd15 <= dbo.F_GET_HISDATETO(ST15.XXXID,ST15.HisNo))
|
|
182
|
-
|
|
183
|
-
LEFT JOIN XXXX_TBL ST16 ON
|
|
184
|
-
|
|
185
|
-
(ST16.XXXID = KH.XXXID) AND (ST16.XXXCode = KH.YYYID) AND (ST16.HisDate <= KH.DutyYmd16) AND (KH.DutyYmd16 <= dbo.F_GET_HISDATETO(ST16.XXXID,ST16.HisNo))
|
|
186
|
-
|
|
187
|
-
LEFT JOIN XXXX_TBL ST17 ON
|
|
188
|
-
|
|
189
|
-
(ST17.XXXID = KH.XXXID) AND (ST17.XXXCode = KH.YYYID) AND (ST17.HisDate <= KH.DutyYmd17) AND (KH.DutyYmd17 <= dbo.F_GET_HISDATETO(ST17.XXXID,ST17.HisNo))
|
|
190
|
-
|
|
191
|
-
LEFT JOIN XXXX_TBL ST18 ON
|
|
192
|
-
|
|
193
|
-
(ST18.XXXID = KH.XXXID) AND (ST18.XXXCode = KH.YYYID) AND (ST18.HisDate <= KH.DutyYmd18) AND (KH.DutyYmd18 <= dbo.F_GET_HISDATETO(ST18.XXXID,ST18.HisNo))
|
|
194
|
-
|
|
195
|
-
LEFT JOIN XXXX_TBL ST19 ON
|
|
196
|
-
|
|
197
|
-
(ST19.XXXID = KH.XXXID) AND (ST19.XXXCode = KH.YYYID) AND (ST19.HisDate <= KH.DutyYmd19) AND (KH.DutyYmd19 <= dbo.F_GET_HISDATETO(ST19.XXXID,ST19.HisNo))
|
|
198
|
-
|
|
199
|
-
LEFT JOIN XXXX_TBL ST20 ON
|
|
200
|
-
|
|
201
|
-
(ST20.XXXID = KH.XXXID) AND (ST20.XXXCode = KH.YYYID) AND (ST20.HisDate <= KH.DutyYmd20) AND (KH.DutyYmd20 <= dbo.F_GET_HISDATETO(ST20.XXXID,ST20.HisNo))
|
|
202
|
-
|
|
203
|
-
LEFT JOIN XXXX_TBL ST21 ON
|
|
204
|
-
|
|
205
|
-
(ST21.XXXID = KH.XXXID) AND (ST21.XXXCode = KH.YYYID) AND (ST21.HisDate <= KH.DutyYmd21) AND (KH.DutyYmd21 <= dbo.F_GET_HISDATETO(ST21.XXXID,ST21.HisNo))
|
|
206
|
-
|
|
207
|
-
LEFT JOIN XXXX_TBL ST22 ON
|
|
208
|
-
|
|
209
|
-
(ST22.XXXID = KH.XXXID) AND (ST22.XXXCode = KH.YYYID) AND (ST22.HisDate <= KH.DutyYmd22) AND (KH.DutyYmd22 <= dbo.F_GET_HISDATETO(ST22.XXXID,ST22.HisNo))
|
|
210
|
-
|
|
211
|
-
LEFT JOIN XXXX_TBL ST23 ON
|
|
212
|
-
|
|
213
|
-
(ST23.XXXID = KH.XXXID) AND (ST23.XXXCode = KH.YYYID) AND (ST23.HisDate <= KH.DutyYmd23) AND (KH.DutyYmd23 <= dbo.F_GET_HISDATETO(ST23.XXXID,ST23.HisNo))
|
|
214
|
-
|
|
215
|
-
LEFT JOIN XXXX_TBL ST24 ON
|
|
216
|
-
|
|
217
|
-
(ST24.XXXID = KH.XXXID) AND (ST24.XXXCode = KH.YYYID) AND (ST24.HisDate <= KH.DutyYmd24) AND (KH.DutyYmd24 <= dbo.F_GET_HISDATETO(ST24.XXXID,ST24.HisNo))
|
|
218
|
-
|
|
219
|
-
LEFT JOIN XXXX_TBL ST25 ON
|
|
220
|
-
|
|
221
|
-
(ST25.XXXID = KH.XXXID) AND (ST25.XXXCode = KH.YYYID) AND (ST25.HisDate <= KH.DutyYmd25) AND (KH.DutyYmd25 <= dbo.F_GET_HISDATETO(ST25.XXXID,ST25.HisNo))
|
|
222
|
-
|
|
223
|
-
LEFT JOIN XXXX_TBL ST26 ON
|
|
224
|
-
|
|
225
|
-
(ST26.XXXID = KH.XXXID) AND (ST26.XXXCode = KH.YYYID) AND (ST26.HisDate <= KH.DutyYmd26) AND (KH.DutyYmd26 <= dbo.F_GET_HISDATETO(ST26.XXXID,ST26.HisNo))
|
|
226
|
-
|
|
227
|
-
LEFT JOIN XXXX_TBL ST27 ON
|
|
228
|
-
|
|
229
|
-
(ST27.XXXID = KH.XXXID) AND (ST27.XXXCode = KH.YYYID) AND (ST27.HisDate <= KH.DutyYmd27) AND (KH.DutyYmd27 <= dbo.F_GET_HISDATETO(ST27.XXXID,ST27.HisNo))
|
|
230
|
-
|
|
231
|
-
LEFT JOIN XXXX_TBL ST28 ON
|
|
232
|
-
|
|
233
|
-
(ST28.XXXID = KH.XXXID) AND (ST28.XXXCode = KH.YYYID) AND (ST28.HisDate <= KH.DutyYmd28) AND (KH.DutyYmd28 <= dbo.F_GET_HISDATETO(ST28.XXXID,ST28.HisNo))
|
|
234
|
-
|
|
235
|
-
LEFT JOIN XXXX_TBL ST29 ON
|
|
236
|
-
|
|
237
|
-
(ST29.XXXID = KH.XXXID) AND (ST29.XXXCode = KH.YYYID) AND (ST29.HisDate <= KH.DutyYmd29) AND (KH.DutyYmd29 <= dbo.F_GET_HISDATETO(ST29.XXXID,ST29.HisNo))
|
|
238
|
-
|
|
239
|
-
LEFT JOIN XXXX_TBL ST30 ON
|
|
240
|
-
|
|
241
|
-
(ST30.XXXID = KH.XXXID) AND (ST30.XXXCode = KH.YYYID) AND (ST30.HisDate <= KH.DutyYmd30) AND (KH.DutyYmd30 <= dbo.F_GET_HISDATETO(ST30.XXXID,ST30.HisNo))
|
|
242
130
|
|
|
243
131
|
LEFT JOIN XXXX_TBL ST31 ON
|
|
244
132
|
|
|
@@ -272,30 +160,380 @@
|
|
|
272
160
|
|
|
273
161
|
)
|
|
274
162
|
|
|
275
|
-
|
|
276
|
-
|
|
277
|
-
|
|
278
|
-
|
|
279
|
-
|
|
163
|
+
```
|
|
164
|
+
|
|
165
|
+
|
|
166
|
+
|
|
167
|
+
```
|
|
280
168
|
|
|
281
169
|
テーブル定義
|
|
282
170
|
|
|
171
|
+
・XXXX_TBL
|
|
172
|
+
|
|
173
|
+
CREATE TABLE [dbo].[XXXX_TBL](
|
|
174
|
+
|
|
175
|
+
[StaffID] [int] NOT NULL,
|
|
176
|
+
|
|
177
|
+
[HisNo] [int] NOT NULL,
|
|
178
|
+
|
|
179
|
+
[StaffCode] [nvarchar](20) NULL,
|
|
180
|
+
|
|
181
|
+
[HisDate] [int] NULL,
|
|
182
|
+
|
|
183
|
+
[StaffName] [nvarchar](40) NULL,
|
|
184
|
+
|
|
185
|
+
[KinmuByoutouCode] [int] NULL,
|
|
186
|
+
|
|
187
|
+
[KinmuByoutou] [nvarchar](30) NOT NULL,
|
|
188
|
+
|
|
189
|
+
[ShozokuByoutouCode] [int] NULL,
|
|
190
|
+
|
|
191
|
+
[ShozokuByoutou] [nvarchar](30) NOT NULL,
|
|
192
|
+
|
|
193
|
+
[ShikakuCode] [int] NULL,
|
|
194
|
+
|
|
195
|
+
[Shikaku] [nvarchar](20) NOT NULL,
|
|
196
|
+
|
|
197
|
+
[ShokuseiCode] [int] NULL,
|
|
198
|
+
|
|
199
|
+
[Shokusei] [nvarchar](20) NOT NULL,
|
|
200
|
+
|
|
201
|
+
[Seibetsu] [nvarchar](1) NOT NULL,
|
|
202
|
+
|
|
203
|
+
[ChangeReasonKbn] [int] NULL,
|
|
204
|
+
|
|
205
|
+
[TOUROKUYMD] [datetime] NULL,
|
|
206
|
+
|
|
207
|
+
[TOUROKUCD] [nvarchar](50) NULL,
|
|
208
|
+
|
|
209
|
+
[KOUSHINYMD] [datetime] NULL,
|
|
210
|
+
|
|
211
|
+
[KOUSHINCD] [nvarchar](50) NULL,
|
|
212
|
+
|
|
283
|
-
|
|
213
|
+
CONSTRAINT [PK_M_Staff_His] PRIMARY KEY CLUSTERED
|
|
214
|
+
|
|
284
|
-
|
|
215
|
+
(
|
|
216
|
+
|
|
285
|
-
|
|
217
|
+
[StaffID] ASC,
|
|
218
|
+
|
|
286
|
-
|
|
219
|
+
[HisNo] ASC
|
|
220
|
+
|
|
221
|
+
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
|
|
222
|
+
|
|
223
|
+
) ON [PRIMARY]
|
|
224
|
+
|
|
225
|
+
|
|
226
|
+
|
|
227
|
+
|
|
228
|
+
|
|
229
|
+
|
|
230
|
+
|
|
287
|
-
---------
|
|
231
|
+
----------
|
|
232
|
+
|
|
233
|
+
・D_Kinmuhyo
|
|
234
|
+
|
|
235
|
+
CREATE VIEW [dbo].[D_Kinmuhyo]
|
|
236
|
+
|
|
237
|
+
AS
|
|
238
|
+
|
|
239
|
+
SELECT DISTINCT
|
|
240
|
+
|
|
241
|
+
sd.SectionID
|
|
242
|
+
|
|
243
|
+
, sd.ScheduleID
|
|
244
|
+
|
|
245
|
+
, sd.StaffID
|
|
246
|
+
|
|
247
|
+
, ss.UpdDtTm
|
|
248
|
+
|
|
249
|
+
, ss.ScheduleSts
|
|
250
|
+
|
|
251
|
+
, ss.StaffChangesCnt
|
|
252
|
+
|
|
253
|
+
, sd.DutyYmd01
|
|
254
|
+
|
|
255
|
+
, sd.DataKbn01
|
|
256
|
+
|
|
257
|
+
, sd.DutyID01
|
|
258
|
+
|
|
259
|
+
, sd.DutyYmd02
|
|
260
|
+
|
|
261
|
+
, sd.DataKbn02
|
|
262
|
+
|
|
263
|
+
, sd.DutyID02
|
|
264
|
+
|
|
265
|
+
, sd.DutyYmd03
|
|
266
|
+
|
|
267
|
+
, sd.DataKbn03
|
|
268
|
+
|
|
269
|
+
, sd.DutyID03
|
|
270
|
+
|
|
271
|
+
, sd.DutyYmd04
|
|
272
|
+
|
|
273
|
+
, sd.DataKbn04
|
|
274
|
+
|
|
275
|
+
, sd.DutyID04
|
|
276
|
+
|
|
277
|
+
, sd.DutyYmd05
|
|
278
|
+
|
|
279
|
+
, sd.DataKbn05
|
|
280
|
+
|
|
281
|
+
, sd.DutyID05
|
|
282
|
+
|
|
283
|
+
, sd.DutyYmd06
|
|
284
|
+
|
|
285
|
+
, sd.DataKbn06
|
|
286
|
+
|
|
287
|
+
, sd.DutyID06
|
|
288
|
+
|
|
289
|
+
, sd.DutyYmd07
|
|
290
|
+
|
|
291
|
+
, sd.DataKbn07
|
|
292
|
+
|
|
293
|
+
, sd.DutyID07
|
|
294
|
+
|
|
295
|
+
, sd.DutyYmd08
|
|
296
|
+
|
|
297
|
+
, sd.DataKbn08
|
|
298
|
+
|
|
299
|
+
, sd.DutyID08
|
|
300
|
+
|
|
301
|
+
, sd.DutyYmd09
|
|
302
|
+
|
|
303
|
+
, sd.DataKbn09
|
|
304
|
+
|
|
305
|
+
, sd.DutyID09
|
|
306
|
+
|
|
307
|
+
, sd.DutyYmd10
|
|
308
|
+
|
|
309
|
+
, sd.DataKbn10
|
|
310
|
+
|
|
311
|
+
, sd.DutyID10
|
|
312
|
+
|
|
313
|
+
, sd.DutyYmd11
|
|
314
|
+
|
|
315
|
+
, sd.DataKbn11
|
|
316
|
+
|
|
317
|
+
, sd.DutyID11
|
|
318
|
+
|
|
319
|
+
, sd.DutyYmd12
|
|
320
|
+
|
|
321
|
+
, sd.DataKbn12
|
|
322
|
+
|
|
323
|
+
, sd.DutyID12
|
|
324
|
+
|
|
325
|
+
, sd.DutyYmd13
|
|
326
|
+
|
|
327
|
+
, sd.DataKbn13
|
|
328
|
+
|
|
329
|
+
, sd.DutyID13
|
|
330
|
+
|
|
331
|
+
, sd.DutyYmd14
|
|
332
|
+
|
|
333
|
+
, sd.DataKbn14
|
|
334
|
+
|
|
335
|
+
, sd.DutyID14
|
|
336
|
+
|
|
337
|
+
, sd.DutyYmd15
|
|
338
|
+
|
|
339
|
+
, sd.DataKbn15
|
|
340
|
+
|
|
341
|
+
, sd.DutyID15
|
|
342
|
+
|
|
343
|
+
, sd.DutyYmd16
|
|
344
|
+
|
|
345
|
+
, sd.DataKbn16
|
|
346
|
+
|
|
347
|
+
, sd.DutyID16
|
|
348
|
+
|
|
349
|
+
, sd.DutyYmd17
|
|
350
|
+
|
|
351
|
+
, sd.DataKbn17
|
|
352
|
+
|
|
353
|
+
, sd.DutyID17
|
|
354
|
+
|
|
355
|
+
, sd.DutyYmd18
|
|
356
|
+
|
|
357
|
+
, sd.DataKbn18
|
|
358
|
+
|
|
359
|
+
, sd.DutyID18
|
|
360
|
+
|
|
361
|
+
, sd.DutyYmd19
|
|
362
|
+
|
|
363
|
+
, sd.DataKbn19
|
|
364
|
+
|
|
365
|
+
, sd.DutyID19
|
|
366
|
+
|
|
367
|
+
, sd.DutyYmd20
|
|
368
|
+
|
|
369
|
+
, sd.DataKbn20
|
|
370
|
+
|
|
371
|
+
, sd.DutyID20
|
|
372
|
+
|
|
373
|
+
, sd.DutyYmd21
|
|
374
|
+
|
|
375
|
+
, sd.DataKbn21
|
|
376
|
+
|
|
377
|
+
, sd.DutyID21
|
|
378
|
+
|
|
379
|
+
, sd.DutyYmd22
|
|
380
|
+
|
|
381
|
+
, sd.DataKbn22
|
|
382
|
+
|
|
383
|
+
, sd.DutyID22
|
|
384
|
+
|
|
385
|
+
, sd.DutyYmd23
|
|
386
|
+
|
|
387
|
+
, sd.DataKbn23
|
|
388
|
+
|
|
389
|
+
, sd.DutyID23
|
|
390
|
+
|
|
391
|
+
, sd.DutyYmd24
|
|
392
|
+
|
|
393
|
+
, sd.DataKbn24
|
|
394
|
+
|
|
395
|
+
, sd.DutyID24
|
|
396
|
+
|
|
397
|
+
, sd.DutyYmd25
|
|
398
|
+
|
|
399
|
+
, sd.DataKbn25
|
|
400
|
+
|
|
401
|
+
, sd.DutyID25
|
|
402
|
+
|
|
403
|
+
, sd.DutyYmd26
|
|
404
|
+
|
|
405
|
+
, sd.DataKbn26
|
|
406
|
+
|
|
407
|
+
, sd.DutyID26
|
|
408
|
+
|
|
409
|
+
, sd.DutyYmd27
|
|
410
|
+
|
|
411
|
+
, sd.DataKbn27
|
|
412
|
+
|
|
413
|
+
, sd.DutyID27
|
|
414
|
+
|
|
415
|
+
, sd.DutyYmd28
|
|
416
|
+
|
|
417
|
+
, sd.DataKbn28
|
|
418
|
+
|
|
419
|
+
, sd.DutyID28
|
|
420
|
+
|
|
421
|
+
, sd.DutyYmd29
|
|
422
|
+
|
|
423
|
+
, sd.DataKbn29
|
|
424
|
+
|
|
425
|
+
, sd.DutyID29
|
|
426
|
+
|
|
427
|
+
, sd.DutyYmd30
|
|
428
|
+
|
|
429
|
+
, sd.DataKbn30
|
|
430
|
+
|
|
431
|
+
, sd.DutyID30
|
|
432
|
+
|
|
433
|
+
, sd.DutyYmd31
|
|
434
|
+
|
|
435
|
+
, sd.DataKbn31
|
|
436
|
+
|
|
437
|
+
, sd.DutyID31
|
|
438
|
+
|
|
439
|
+
, ISNULL(sdo.StaffDispOrder, ISNULL(dbo.M_StaffRangeOrder.StaffDispOrder, 999)) AS StaffDispOrder
|
|
440
|
+
|
|
441
|
+
, sd.UpdCnt
|
|
442
|
+
|
|
443
|
+
FROM
|
|
444
|
+
|
|
445
|
+
dbo.ScheduleDetail AS sd
|
|
446
|
+
|
|
447
|
+
INNER JOIN dbo.Schedule AS ss
|
|
448
|
+
|
|
449
|
+
ON sd.SectionID = ss.SectionID
|
|
450
|
+
|
|
451
|
+
AND sd.ScheduleID = ss.ScheduleID
|
|
452
|
+
|
|
453
|
+
LEFT OUTER JOIN dbo.M_StaffRangeOrder
|
|
454
|
+
|
|
455
|
+
ON sd.StaffID = dbo.M_StaffRangeOrder.StaffID
|
|
456
|
+
|
|
457
|
+
AND (
|
|
458
|
+
|
|
459
|
+
ss.StartYmd <= dbo.M_StaffRangeOrder.AppliStYmdFrom
|
|
460
|
+
|
|
461
|
+
AND dbo.M_StaffRangeOrder.AppliStYmdFrom <= CONVERT(
|
|
462
|
+
|
|
463
|
+
int
|
|
464
|
+
|
|
465
|
+
, CONVERT(
|
|
466
|
+
|
|
467
|
+
varchar (8)
|
|
468
|
+
|
|
469
|
+
, CONVERT(datetime, CONVERT(varchar (8), ss.StartYmd)) + ss.DutyDayCnt - 1
|
|
470
|
+
|
|
471
|
+
, 112
|
|
472
|
+
|
|
473
|
+
)
|
|
474
|
+
|
|
475
|
+
)
|
|
476
|
+
|
|
477
|
+
OR ss.StartYmd <= dbo.M_StaffRangeOrder.AppliStYmdTo
|
|
478
|
+
|
|
479
|
+
AND dbo.M_StaffRangeOrder.AppliStYmdTo <= CONVERT(
|
|
480
|
+
|
|
481
|
+
int
|
|
482
|
+
|
|
483
|
+
, CONVERT(
|
|
484
|
+
|
|
485
|
+
varchar (8)
|
|
486
|
+
|
|
487
|
+
, CONVERT(datetime, CONVERT(varchar (8), ss.StartYmd)) + ss.DutyDayCnt - 1
|
|
488
|
+
|
|
489
|
+
, 112
|
|
490
|
+
|
|
491
|
+
)
|
|
492
|
+
|
|
493
|
+
)
|
|
494
|
+
|
|
495
|
+
OR dbo.M_StaffRangeOrder.AppliStYmdFrom <= ss.StartYmd
|
|
496
|
+
|
|
497
|
+
AND CONVERT(
|
|
498
|
+
|
|
499
|
+
int
|
|
500
|
+
|
|
501
|
+
, CONVERT(
|
|
502
|
+
|
|
503
|
+
varchar (8)
|
|
504
|
+
|
|
505
|
+
, CONVERT(datetime, CONVERT(varchar (8), ss.StartYmd)) + ss.DutyDayCnt - 1
|
|
506
|
+
|
|
507
|
+
, 112
|
|
508
|
+
|
|
509
|
+
)
|
|
510
|
+
|
|
511
|
+
) <= dbo.M_StaffRangeOrder.AppliStYmdTo
|
|
512
|
+
|
|
513
|
+
)
|
|
514
|
+
|
|
515
|
+
AND ss.SectionID = dbo.M_StaffRangeOrder.SectionID
|
|
516
|
+
|
|
517
|
+
LEFT OUTER JOIN dbo.StaffDispOrder AS sdo
|
|
518
|
+
|
|
519
|
+
ON sdo.HospitalID = ss.HospitalID
|
|
520
|
+
|
|
521
|
+
AND sdo.SectionID = ss.SectionID
|
|
522
|
+
|
|
523
|
+
AND sdo.StaffID = sd.StaffID
|
|
524
|
+
|
|
525
|
+
AND sdo.StartYmd = ss.StartYmd
|
|
526
|
+
|
|
527
|
+
```
|
|
528
|
+
|
|
529
|
+
|
|
530
|
+
|
|
531
|
+
|
|
288
532
|
|
|
289
533
|
実行プラン
|
|
290
534
|
|
|
291
|
-
|
|
292
|
-
|
|
293
|
-
|
|
294
|
-
|
|
295
|
-
|
|
296
|
-
|
|
536
|
+
|
|
297
|
-
|
|
298
|
-
|
|
538
|
+
|
|
299
|
-
|
|
300
|
-
|
|
301
|
-
---------
|
2
テーブル定義、実行計画を追記しました。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -2,7 +2,7 @@
|
|
|
2
2
|
|
|
3
3
|
以下SQLのLEFTJOINしている箇所を見直したいです。
|
|
4
4
|
|
|
5
|
-
実際は同じ記載方法で
|
|
5
|
+
実際は同じ記載方法で31個JOINしています。（処理速度遅延の原因の1つと思っています）
|
|
6
6
|
|
|
7
7
|
良い書き方があればご教授いただきたいです。
|
|
8
8
|
|
|
@@ -274,12 +274,28 @@
|
|
|
274
274
|
|
|
275
275
|
|
|
276
276
|
|
|
277
|
+
|
|
278
|
+
|
|
277
|
-
|
|
279
|
+
---------
|
|
280
|
+
|
|
278
|
-
|
|
281
|
+
テーブル定義
|
|
282
|
+
|
|
279
|
-
|
|
283
|
+
|
|
280
|
-
|
|
284
|
+
|
|
285
|
+
|
|
286
|
+
|
|
281
|
-
|
|
287
|
+
---------
|
|
288
|
+
|
|
282
|
-
|
|
289
|
+
実行プラン
|
|
290
|
+
|
|
283
|
-
|
|
291
|
+
|
|
292
|
+
|
|
284
|
-
|
|
293
|
+
|
|
294
|
+
|
|
295
|
+
|
|
296
|
+
|
|
297
|
+
|
|
298
|
+
|
|
299
|
+
|
|
300
|
+
|
|
285
|
-
|
|
301
|
+
---------
|
1
SQL文を省略せず記載しました。
test
CHANGED
|
File without changes
|
test
CHANGED
|
@@ -8,43 +8,269 @@
|
|
|
8
8
|
|
|
9
9
|
|
|
10
10
|
|
|
11
|
-
|
|
11
|
+
SELECT
|
|
12
|
+
|
|
12
|
-
|
|
13
|
+
ROW_NUMBER() OVER(ORDER BY SCH.YYYID,SCH.StartYmd,KH.XXXID) as row_num, --テーブル変数格納用ID
|
|
14
|
+
|
|
15
|
+
SCH.YYYID,SCH.ScheduleID,KH.XXXID,
|
|
16
|
+
|
|
17
|
+
KH.DutyYmd01,KH.DataKbn01,KH.DutyID01,
|
|
18
|
+
|
|
19
|
+
KH.DutyYmd02,KH.DataKbn02,KH.DutyID02,
|
|
20
|
+
|
|
21
|
+
KH.DutyYmd03,KH.DataKbn03,KH.DutyID03,
|
|
22
|
+
|
|
23
|
+
KH.DutyYmd04,KH.DataKbn04,KH.DutyID04,
|
|
24
|
+
|
|
25
|
+
KH.DutyYmd05,KH.DataKbn05,KH.DutyID05,
|
|
26
|
+
|
|
27
|
+
KH.DutyYmd06,KH.DataKbn06,KH.DutyID06,
|
|
28
|
+
|
|
29
|
+
KH.DutyYmd07,KH.DataKbn07,KH.DutyID07,
|
|
30
|
+
|
|
31
|
+
KH.DutyYmd08,KH.DataKbn08,KH.DutyID08,
|
|
32
|
+
|
|
33
|
+
KH.DutyYmd09,KH.DataKbn09,KH.DutyID09,
|
|
34
|
+
|
|
35
|
+
KH.DutyYmd10,KH.DataKbn10,KH.DutyID10,
|
|
36
|
+
|
|
37
|
+
KH.DutyYmd11,KH.DataKbn11,KH.DutyID11,
|
|
38
|
+
|
|
39
|
+
KH.DutyYmd12,KH.DataKbn12,KH.DutyID12,
|
|
40
|
+
|
|
41
|
+
KH.DutyYmd13,KH.DataKbn13,KH.DutyID13,
|
|
42
|
+
|
|
43
|
+
KH.DutyYmd14,KH.DataKbn14,KH.DutyID14,
|
|
44
|
+
|
|
45
|
+
KH.DutyYmd15,KH.DataKbn15,KH.DutyID15,
|
|
46
|
+
|
|
47
|
+
KH.DutyYmd16,KH.DataKbn16,KH.DutyID16,
|
|
48
|
+
|
|
49
|
+
KH.DutyYmd17,KH.DataKbn17,KH.DutyID17,
|
|
50
|
+
|
|
51
|
+
KH.DutyYmd18,KH.DataKbn18,KH.DutyID18,
|
|
52
|
+
|
|
53
|
+
KH.DutyYmd19,KH.DataKbn19,KH.DutyID19,
|
|
54
|
+
|
|
55
|
+
KH.DutyYmd20,KH.DataKbn20,KH.DutyID20,
|
|
56
|
+
|
|
57
|
+
KH.DutyYmd21,KH.DataKbn21,KH.DutyID21,
|
|
58
|
+
|
|
59
|
+
KH.DutyYmd22,KH.DataKbn22,KH.DutyID22,
|
|
60
|
+
|
|
61
|
+
KH.DutyYmd23,KH.DataKbn23,KH.DutyID23,
|
|
62
|
+
|
|
63
|
+
KH.DutyYmd24,KH.DataKbn24,KH.DutyID24,
|
|
64
|
+
|
|
65
|
+
KH.DutyYmd25,KH.DataKbn25,KH.DutyID25,
|
|
66
|
+
|
|
67
|
+
KH.DutyYmd26,KH.DataKbn26,KH.DutyID26,
|
|
68
|
+
|
|
69
|
+
KH.DutyYmd27,KH.DataKbn27,KH.DutyID27,
|
|
70
|
+
|
|
71
|
+
KH.DutyYmd28,KH.DataKbn28,KH.DutyID28,
|
|
72
|
+
|
|
73
|
+
KH.DutyYmd29,KH.DataKbn29,KH.DutyID29,
|
|
74
|
+
|
|
75
|
+
KH.DutyYmd30,KH.DataKbn30,KH.DutyID30,
|
|
76
|
+
|
|
77
|
+
KH.DutyYmd31,KH.DataKbn31,KH.DutyID31,
|
|
78
|
+
|
|
79
|
+
ST01.XXXID AS StaffID01,ST02.XXXID AS StaffID02,ST03.XXXID AS StaffID03,ST04.XXXID AS StaffID04,ST05.XXXID AS StaffID05,
|
|
80
|
+
|
|
81
|
+
ST06.XXXID AS StaffID06,ST07.XXXID AS StaffID07,ST08.XXXID AS StaffID08,ST09.XXXID AS StaffID09,ST10.XXXID AS StaffID10,
|
|
82
|
+
|
|
83
|
+
ST11.XXXID AS StaffID11,ST12.XXXID AS StaffID12,ST13.XXXID AS StaffID13,ST14.XXXID AS StaffID14,ST15.XXXID AS StaffID15,
|
|
84
|
+
|
|
85
|
+
ST16.XXXID AS StaffID16,ST17.XXXID AS StaffID17,ST18.XXXID AS StaffID18,ST19.XXXID AS StaffID19,ST20.XXXID AS StaffID20,
|
|
86
|
+
|
|
87
|
+
ST21.XXXID AS StaffID21,ST22.XXXID AS StaffID22,ST23.XXXID AS StaffID23,ST24.XXXID AS StaffID24,ST25.XXXID AS StaffID25,
|
|
88
|
+
|
|
89
|
+
ST26.XXXID AS StaffID26,ST27.XXXID AS StaffID27,ST28.XXXID AS StaffID28,ST29.XXXID AS StaffID29,ST30.XXXID AS StaffID30,
|
|
90
|
+
|
|
13
|
-
|
|
91
|
+
ST31.XXXID AS StaffID31
|
|
92
|
+
|
|
93
|
+
FROM
|
|
94
|
+
|
|
95
|
+
(
|
|
96
|
+
|
|
97
|
+
SELECT
|
|
98
|
+
|
|
99
|
+
ROW_NUMBER() OVER(PARTITION BY SCH.YYYID,SCH.StartYmd ORDER BY SCH.ScheduleSts DESC ,SCH.UpdDtTm DESC,SCH.ScheduleID DESC) AS sch_num,
|
|
100
|
+
|
|
101
|
+
SCH.YYYID,SCH.ScheduleID,SCH.ScheduleSts,SCH.StartYmd,SCH.UpdDtTm
|
|
14
102
|
|
|
15
103
|
FROM
|
|
16
104
|
|
|
17
|
-
|
|
105
|
+
M_Schedule SCH
|
|
18
106
|
|
|
19
107
|
WHERE
|
|
20
108
|
|
|
21
|
-
(a
|
|
22
|
-
|
|
23
|
-
(a
|
|
24
|
-
|
|
25
|
-
|
|
26
|
-
|
|
27
|
-
|
|
28
|
-
|
|
29
|
-
|
|
30
|
-
|
|
31
|
-
(
|
|
32
|
-
|
|
33
|
-
|
|
34
|
-
|
|
35
|
-
|
|
36
|
-
|
|
37
|
-
|
|
38
|
-
|
|
39
|
-
|
|
40
|
-
|
|
41
|
-
|
|
42
|
-
|
|
43
|
-
|
|
44
|
-
|
|
45
|
-
|
|
46
|
-
|
|
47
|
-
|
|
109
|
+
--(SCH.StartYmd >= @cRangeDate)
|
|
110
|
+
|
|
111
|
+
(SCH.StartYmd >= @inStartDate) AND
|
|
112
|
+
|
|
113
|
+
(SCH.StartYmd <= @inEndDate)
|
|
114
|
+
|
|
115
|
+
) SCH
|
|
116
|
+
|
|
117
|
+
INNER JOIN [Servis].dbo.D_Kinmuhyo KH ON
|
|
118
|
+
|
|
119
|
+
(KH.YYYID = SCH.YYYID) AND
|
|
120
|
+
|
|
121
|
+
(KH.ScheduleID = SCH.ScheduleID)
|
|
122
|
+
|
|
123
|
+
LEFT JOIN XXXX_TBL ST01 ON
|
|
124
|
+
|
|
125
|
+
(ST01.XXXID = KH.XXXID) AND (ST01.XXXCode = KH.YYYID) AND (ST01.HisDate <= KH.DutyYmd01) AND (KH.DutyYmd01 <= dbo.F_GET_HISDATETO(ST01.XXXID,ST01.HisNo))
|
|
126
|
+
|
|
127
|
+
LEFT JOIN XXXX_TBL ST02 ON
|
|
128
|
+
|
|
129
|
+
(ST02.XXXID = KH.XXXID) AND (ST02.XXXCode = KH.YYYID) AND (ST02.HisDate <= KH.DutyYmd02) AND (KH.DutyYmd02 <= dbo.F_GET_HISDATETO(ST02.XXXID,ST02.HisNo))
|
|
130
|
+
|
|
131
|
+
LEFT JOIN XXXX_TBL ST03 ON
|
|
132
|
+
|
|
133
|
+
(ST03.XXXID = KH.XXXID) AND (ST03.XXXCode = KH.YYYID) AND (ST03.HisDate <= KH.DutyYmd03) AND (KH.DutyYmd03 <= dbo.F_GET_HISDATETO(ST03.XXXID,ST03.HisNo))
|
|
134
|
+
|
|
135
|
+
LEFT JOIN XXXX_TBL ST04 ON
|
|
136
|
+
|
|
137
|
+
(ST04.XXXID = KH.XXXID) AND (ST04.XXXCode = KH.YYYID) AND (ST04.HisDate <= KH.DutyYmd04) AND (KH.DutyYmd04 <= dbo.F_GET_HISDATETO(ST04.XXXID,ST04.HisNo))
|
|
138
|
+
|
|
139
|
+
LEFT JOIN XXXX_TBL ST05 ON
|
|
140
|
+
|
|
141
|
+
(ST05.XXXID = KH.XXXID) AND (ST05.XXXCode = KH.YYYID) AND (ST05.HisDate <= KH.DutyYmd05) AND (KH.DutyYmd05 <= dbo.F_GET_HISDATETO(ST05.XXXID,ST05.HisNo))
|
|
142
|
+
|
|
143
|
+
LEFT JOIN XXXX_TBL ST06 ON
|
|
144
|
+
|
|
145
|
+
(ST06.XXXID = KH.XXXID) AND (ST06.XXXCode = KH.YYYID) AND (ST06.HisDate <= KH.DutyYmd06) AND (KH.DutyYmd06 <= dbo.F_GET_HISDATETO(ST06.XXXID,ST06.HisNo))
|
|
146
|
+
|
|
147
|
+
LEFT JOIN XXXX_TBL ST07 ON
|
|
148
|
+
|
|
149
|
+
(ST07.XXXID = KH.XXXID) AND (ST07.XXXCode = KH.YYYID) AND (ST07.HisDate <= KH.DutyYmd07) AND (KH.DutyYmd07 <= dbo.F_GET_HISDATETO(ST07.XXXID,ST07.HisNo))
|
|
150
|
+
|
|
151
|
+
LEFT JOIN XXXX_TBL ST08 ON
|
|
152
|
+
|
|
153
|
+
(ST08.XXXID = KH.XXXID) AND (ST08.XXXCode = KH.YYYID) AND (ST08.HisDate <= KH.DutyYmd08) AND (KH.DutyYmd08 <= dbo.F_GET_HISDATETO(ST08.XXXID,ST08.HisNo))
|
|
154
|
+
|
|
155
|
+
LEFT JOIN XXXX_TBL ST09 ON
|
|
156
|
+
|
|
157
|
+
(ST09.XXXID = KH.XXXID) AND (ST09.XXXCode = KH.YYYID) AND (ST09.HisDate <= KH.DutyYmd09) AND (KH.DutyYmd09 <= dbo.F_GET_HISDATETO(ST09.XXXID,ST09.HisNo))
|
|
158
|
+
|
|
159
|
+
LEFT JOIN XXXX_TBL ST10 ON
|
|
160
|
+
|
|
161
|
+
(ST10.XXXID = KH.XXXID) AND (ST10.XXXCode = KH.YYYID) AND (ST10.HisDate <= KH.DutyYmd10) AND (KH.DutyYmd10 <= dbo.F_GET_HISDATETO(ST10.XXXID,ST10.HisNo))
|
|
162
|
+
|
|
163
|
+
LEFT JOIN XXXX_TBL ST11 ON
|
|
164
|
+
|
|
165
|
+
(ST11.XXXID = KH.XXXID) AND (ST11.XXXCode = KH.YYYID) AND (ST11.HisDate <= KH.DutyYmd11) AND (KH.DutyYmd11 <= dbo.F_GET_HISDATETO(ST11.XXXID,ST11.HisNo))
|
|
166
|
+
|
|
167
|
+
LEFT JOIN XXXX_TBL ST12 ON
|
|
168
|
+
|
|
169
|
+
(ST12.XXXID = KH.XXXID) AND (ST12.XXXCode = KH.YYYID) AND (ST12.HisDate <= KH.DutyYmd12) AND (KH.DutyYmd12 <= dbo.F_GET_HISDATETO(ST12.XXXID,ST12.HisNo))
|
|
170
|
+
|
|
171
|
+
LEFT JOIN XXXX_TBL ST13 ON
|
|
172
|
+
|
|
173
|
+
(ST13.XXXID = KH.XXXID) AND (ST13.XXXCode = KH.YYYID) AND (ST13.HisDate <= KH.DutyYmd13) AND (KH.DutyYmd13 <= dbo.F_GET_HISDATETO(ST13.XXXID,ST13.HisNo))
|
|
174
|
+
|
|
175
|
+
LEFT JOIN XXXX_TBL ST14 ON
|
|
176
|
+
|
|
177
|
+
(ST14.XXXID = KH.XXXID) AND (ST14.XXXCode = KH.YYYID) AND (ST14.HisDate <= KH.DutyYmd14) AND (KH.DutyYmd14 <= dbo.F_GET_HISDATETO(ST14.XXXID,ST14.HisNo))
|
|
178
|
+
|
|
179
|
+
LEFT JOIN XXXX_TBL ST15 ON
|
|
180
|
+
|
|
181
|
+
(ST15.XXXID = KH.XXXID) AND (ST15.XXXCode = KH.YYYID) AND (ST15.HisDate <= KH.DutyYmd15) AND (KH.DutyYmd15 <= dbo.F_GET_HISDATETO(ST15.XXXID,ST15.HisNo))
|
|
182
|
+
|
|
183
|
+
LEFT JOIN XXXX_TBL ST16 ON
|
|
184
|
+
|
|
185
|
+
(ST16.XXXID = KH.XXXID) AND (ST16.XXXCode = KH.YYYID) AND (ST16.HisDate <= KH.DutyYmd16) AND (KH.DutyYmd16 <= dbo.F_GET_HISDATETO(ST16.XXXID,ST16.HisNo))
|
|
186
|
+
|
|
187
|
+
LEFT JOIN XXXX_TBL ST17 ON
|
|
188
|
+
|
|
189
|
+
(ST17.XXXID = KH.XXXID) AND (ST17.XXXCode = KH.YYYID) AND (ST17.HisDate <= KH.DutyYmd17) AND (KH.DutyYmd17 <= dbo.F_GET_HISDATETO(ST17.XXXID,ST17.HisNo))
|
|
190
|
+
|
|
191
|
+
LEFT JOIN XXXX_TBL ST18 ON
|
|
192
|
+
|
|
193
|
+
(ST18.XXXID = KH.XXXID) AND (ST18.XXXCode = KH.YYYID) AND (ST18.HisDate <= KH.DutyYmd18) AND (KH.DutyYmd18 <= dbo.F_GET_HISDATETO(ST18.XXXID,ST18.HisNo))
|
|
194
|
+
|
|
195
|
+
LEFT JOIN XXXX_TBL ST19 ON
|
|
196
|
+
|
|
197
|
+
(ST19.XXXID = KH.XXXID) AND (ST19.XXXCode = KH.YYYID) AND (ST19.HisDate <= KH.DutyYmd19) AND (KH.DutyYmd19 <= dbo.F_GET_HISDATETO(ST19.XXXID,ST19.HisNo))
|
|
198
|
+
|
|
199
|
+
LEFT JOIN XXXX_TBL ST20 ON
|
|
200
|
+
|
|
201
|
+
(ST20.XXXID = KH.XXXID) AND (ST20.XXXCode = KH.YYYID) AND (ST20.HisDate <= KH.DutyYmd20) AND (KH.DutyYmd20 <= dbo.F_GET_HISDATETO(ST20.XXXID,ST20.HisNo))
|
|
202
|
+
|
|
203
|
+
LEFT JOIN XXXX_TBL ST21 ON
|
|
204
|
+
|
|
205
|
+
(ST21.XXXID = KH.XXXID) AND (ST21.XXXCode = KH.YYYID) AND (ST21.HisDate <= KH.DutyYmd21) AND (KH.DutyYmd21 <= dbo.F_GET_HISDATETO(ST21.XXXID,ST21.HisNo))
|
|
206
|
+
|
|
207
|
+
LEFT JOIN XXXX_TBL ST22 ON
|
|
208
|
+
|
|
209
|
+
(ST22.XXXID = KH.XXXID) AND (ST22.XXXCode = KH.YYYID) AND (ST22.HisDate <= KH.DutyYmd22) AND (KH.DutyYmd22 <= dbo.F_GET_HISDATETO(ST22.XXXID,ST22.HisNo))
|
|
210
|
+
|
|
211
|
+
LEFT JOIN XXXX_TBL ST23 ON
|
|
212
|
+
|
|
213
|
+
(ST23.XXXID = KH.XXXID) AND (ST23.XXXCode = KH.YYYID) AND (ST23.HisDate <= KH.DutyYmd23) AND (KH.DutyYmd23 <= dbo.F_GET_HISDATETO(ST23.XXXID,ST23.HisNo))
|
|
214
|
+
|
|
215
|
+
LEFT JOIN XXXX_TBL ST24 ON
|
|
216
|
+
|
|
217
|
+
(ST24.XXXID = KH.XXXID) AND (ST24.XXXCode = KH.YYYID) AND (ST24.HisDate <= KH.DutyYmd24) AND (KH.DutyYmd24 <= dbo.F_GET_HISDATETO(ST24.XXXID,ST24.HisNo))
|
|
218
|
+
|
|
219
|
+
LEFT JOIN XXXX_TBL ST25 ON
|
|
220
|
+
|
|
221
|
+
(ST25.XXXID = KH.XXXID) AND (ST25.XXXCode = KH.YYYID) AND (ST25.HisDate <= KH.DutyYmd25) AND (KH.DutyYmd25 <= dbo.F_GET_HISDATETO(ST25.XXXID,ST25.HisNo))
|
|
222
|
+
|
|
223
|
+
LEFT JOIN XXXX_TBL ST26 ON
|
|
224
|
+
|
|
225
|
+
(ST26.XXXID = KH.XXXID) AND (ST26.XXXCode = KH.YYYID) AND (ST26.HisDate <= KH.DutyYmd26) AND (KH.DutyYmd26 <= dbo.F_GET_HISDATETO(ST26.XXXID,ST26.HisNo))
|
|
226
|
+
|
|
227
|
+
LEFT JOIN XXXX_TBL ST27 ON
|
|
228
|
+
|
|
229
|
+
(ST27.XXXID = KH.XXXID) AND (ST27.XXXCode = KH.YYYID) AND (ST27.HisDate <= KH.DutyYmd27) AND (KH.DutyYmd27 <= dbo.F_GET_HISDATETO(ST27.XXXID,ST27.HisNo))
|
|
230
|
+
|
|
231
|
+
LEFT JOIN XXXX_TBL ST28 ON
|
|
232
|
+
|
|
233
|
+
(ST28.XXXID = KH.XXXID) AND (ST28.XXXCode = KH.YYYID) AND (ST28.HisDate <= KH.DutyYmd28) AND (KH.DutyYmd28 <= dbo.F_GET_HISDATETO(ST28.XXXID,ST28.HisNo))
|
|
234
|
+
|
|
235
|
+
LEFT JOIN XXXX_TBL ST29 ON
|
|
236
|
+
|
|
237
|
+
(ST29.XXXID = KH.XXXID) AND (ST29.XXXCode = KH.YYYID) AND (ST29.HisDate <= KH.DutyYmd29) AND (KH.DutyYmd29 <= dbo.F_GET_HISDATETO(ST29.XXXID,ST29.HisNo))
|
|
238
|
+
|
|
239
|
+
LEFT JOIN XXXX_TBL ST30 ON
|
|
240
|
+
|
|
241
|
+
(ST30.XXXID = KH.XXXID) AND (ST30.XXXCode = KH.YYYID) AND (ST30.HisDate <= KH.DutyYmd30) AND (KH.DutyYmd30 <= dbo.F_GET_HISDATETO(ST30.XXXID,ST30.HisNo))
|
|
242
|
+
|
|
243
|
+
LEFT JOIN XXXX_TBL ST31 ON
|
|
244
|
+
|
|
245
|
+
(ST31.XXXID = KH.XXXID) AND (ST31.XXXCode = KH.YYYID) AND (ST31.HisDate <= KH.DutyYmd31) AND (KH.DutyYmd31 <= dbo.F_GET_HISDATETO(ST31.XXXID,ST31.HisNo))
|
|
246
|
+
|
|
247
|
+
WHERE
|
|
248
|
+
|
|
249
|
+
(SCH.sch_num = 1)
|
|
250
|
+
|
|
251
|
+
AND EXISTS(
|
|
252
|
+
|
|
253
|
+
SELECT S.*
|
|
254
|
+
|
|
255
|
+
FROM XXXX_TBL S
|
|
256
|
+
|
|
257
|
+
WHERE
|
|
258
|
+
|
|
259
|
+
(S.XXXID = KH.XXXID) AND
|
|
260
|
+
|
|
261
|
+
(SUBSTRING(CAST(S.HisDate AS VARCHAR),1,6) <= SUBSTRING(CAST(@inEndDate AS VARCHAR),1,6)) AND
|
|
262
|
+
|
|
263
|
+
(SUBSTRING(CAST(@inStartDate AS VARCHAR),1,6) <= SUBSTRING(CAST(dbo.F_GET_HISDATETO(S.XXXID,S.HisNo) AS VARCHAR),1,6)) AND
|
|
264
|
+
|
|
265
|
+
(
|
|
266
|
+
|
|
267
|
+
((@inSectionID = 0) AND (1=1)) OR
|
|
268
|
+
|
|
269
|
+
((@inSectionID <> 0) AND (S.XXXCode = @inSectionID))
|
|
270
|
+
|
|
271
|
+
)
|
|
272
|
+
|
|
273
|
+
)
|
|
48
274
|
|
|
49
275
|
|
|
50
276
|
|