记录运单状态及异常的查询方法及语句
1 SELECT 2 zs01.[到站], 3 bm.OrgAttributes AS '部门类型', 4 zs01.[运单总数], 5 ydh01.[已到货], 6 zcdh01.[正常到货], 7 mdwfcyc01.[门店未发车异常], 8 kqwqyc01.[库区未签异常], 9 kqwfyc01.[库区未发异常], 10 mdwfc01.[门店未发车], 11 mdfczt01.[门店发车在途], 12 kqdh01.[库区到货], 13 kqfczt01.[库区发车在途] 14 FROM 15 ( 16 SELECT---运单总数 17 xy.[到站], 18 COUNT ( xy.[到站] ) AS '运单总数' 19 FROM 20 ( 21 SELECT 22 x.Hdbh AS '单号', 23 x.qz AS '起站', 24 x.qzbm AS '起站部门类型', 25 x.dz AS '到站', 26 x.dzbm AS '到站部门类型', 27 x.BillDate AS '收件时间', 28 x.CreateTime AS '门店收件时间', 29 x.StartTime AS '门店发车时间', 30 x.EndTime AS '库区到货时间', 31 y.StartTime AS '库区发车时间', 32 y.EndTime AS '网点到货时间', 33 x.TransferTime AS '网点中转时间', 34 x.SignTime AS '签收时间', 35 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 36 FROM 37 ( 38 SELECT 39 a.Hdbh, 40 d.Name AS qz, 41 d.OrgAttributes AS qzbm, 42 e.Name AS dz, 43 e.OrgAttributes AS dzbm, 44 a.BillDate, 45 a.CreateTime, 46 c.StartTime, 47 c.EndTime, 48 f.TransferTime, 49 g.SignTime 50 FROM 51 shipment_bill a WITH ( NOLOCK ) 52 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 53 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 54 AND c.EndDepartId= 172 55 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 56 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 57 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 58 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 59 WHERE 60 a.IsValid= 1 61 AND a.BillDate= '2019-8-30' 62 ) x 63 LEFT JOIN ( 64 SELECT 65 a.Hdbh, 66 c.StartTime, 67 c.EndTime 68 FROM 69 shipment_bill a WITH ( NOLOCK ) 70 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 71 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 72 AND c.StartDepartId= 172 73 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 74 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 75 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 76 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 77 WHERE 78 a.IsValid= 1 79 AND a.BillDate= '2019-8-30' 80 ) y ON x.Hdbh = y.Hdbh 81 ) xy 82 GROUP BY 83 xy.[到站] 84 ) zs01 85 LEFT JOIN ( 86 SELECT---已到货 87 ydh.[到站], 88 COUNT ( ydh.[到站] ) AS '已到货' 89 FROM 90 ( 91 SELECT 92 x.Hdbh AS '单号', 93 x.qz AS '起站', 94 x.qzbm AS '起站部门类型', 95 x.dz AS '到站', 96 x.dzbm AS '到站部门类型', 97 x.BillDate AS '收件时间', 98 x.CreateTime AS '门店收件时间', 99 x.StartTime AS '门店发车时间', 100 x.EndTime AS '库区到货时间', 101 y.StartTime AS '库区发车时间', 102 y.EndTime AS '网点到货时间', 103 x.TransferTime AS '网点中转时间', 104 x.SignTime AS '签收时间', 105 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 106 FROM 107 ( 108 SELECT 109 a.Hdbh, 110 d.Name AS qz, 111 d.OrgAttributes AS qzbm, 112 e.Name AS dz, 113 e.OrgAttributes AS dzbm, 114 a.BillDate, 115 a.CreateTime, 116 c.StartTime, 117 c.EndTime, 118 f.TransferTime, 119 g.SignTime 120 FROM 121 shipment_bill a WITH ( NOLOCK ) 122 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 123 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 124 AND c.EndDepartId= 172 125 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 126 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 127 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 128 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 129 WHERE 130 a.IsValid= 1 131 AND a.BillDate= '2019-8-30' 132 ) x 133 LEFT JOIN ( 134 SELECT 135 a.Hdbh, 136 c.StartTime, 137 c.EndTime 138 FROM 139 shipment_bill a WITH ( NOLOCK ) 140 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 141 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 142 AND c.StartDepartId= 172 143 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 144 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 145 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 146 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 147 WHERE 148 a.IsValid= 1 149 AND a.BillDate= '2019-8-30' 150 ) y ON x.Hdbh = y.Hdbh 151 ) ydh 152 WHERE 153 ydh.[网点到货时间] > '1970-01-01' 154 GROUP BY 155 ydh.[到站] 156 ) ydh01 ON zs01.[到站] = ydh01.[到站] 157 LEFT JOIN ( 158 SELECT---- 正常到货 159 zcdh.到站, 160 COUNT ( zcdh.到站 ) AS '正常到货' 161 FROM 162 ( 163 SELECT 164 x.Hdbh AS '单号', 165 x.qz AS '起站', 166 x.qzbm AS '起站部门类型', 167 x.dz AS '到站', 168 x.dzbm AS '到站部门类型', 169 x.BillDate AS '收件时间', 170 x.CreateTime AS '门店收件时间', 171 x.StartTime AS '门店发车时间', 172 x.EndTime AS '库区到货时间', 173 y.StartTime AS '库区发车时间', 174 y.EndTime AS '网点到货时间', 175 x.TransferTime AS '网点中转时间', 176 x.SignTime AS '签收时间', 177 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 178 FROM 179 ( 180 SELECT 181 a.Hdbh, 182 d.Name AS qz, 183 d.OrgAttributes AS qzbm, 184 e.Name AS dz, 185 e.OrgAttributes AS dzbm, 186 a.BillDate, 187 a.CreateTime, 188 c.StartTime, 189 c.EndTime, 190 f.TransferTime, 191 g.SignTime 192 FROM 193 shipment_bill a WITH ( NOLOCK ) 194 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 195 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 196 AND c.EndDepartId= 172 197 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 198 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 199 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 200 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 201 WHERE 202 a.IsValid= 1 203 AND a.BillDate= '2019-8-30' 204 ) x 205 LEFT JOIN ( 206 SELECT 207 a.Hdbh, 208 c.StartTime, 209 c.EndTime 210 FROM 211 shipment_bill a WITH ( NOLOCK ) 212 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 213 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 214 AND c.StartDepartId= 172 215 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 216 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 217 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 218 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 219 WHERE 220 a.IsValid= 1 221 AND a.BillDate= '2019-8-30' 222 ) y ON x.Hdbh = y.Hdbh 223 ) zcdh 224 WHERE 225 zcdh.门店发车时间 > '1970-01-01' 226 AND zcdh.库区到货时间 > '1970-01-01' 227 AND zcdh.库区发车时间 > '1970-01-01' 228 AND zcdh.网点到货时间 > '1970-01-01' 229 GROUP BY 230 zcdh.[到站] 231 ) zcdh01 ON zs01.[到站] = zcdh01.[到站] 232 LEFT JOIN ( 233 SELECT----门店未发车异常 234 mdwfcyc.到站, 235 COUNT ( mdwfcyc.到站 ) AS '门店未发车异常' 236 FROM 237 ( 238 SELECT 239 x.Hdbh AS '单号', 240 x.qz AS '起站', 241 x.qzbm AS '起站部门类型', 242 x.dz AS '到站', 243 x.dzbm AS '到站部门类型', 244 x.BillDate AS '收件时间', 245 x.CreateTime AS '门店收件时间', 246 x.StartTime AS '门店发车时间', 247 x.EndTime AS '库区到货时间', 248 y.StartTime AS '库区发车时间', 249 y.EndTime AS '网点到货时间', 250 x.TransferTime AS '网点中转时间', 251 x.SignTime AS '签收时间', 252 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 253 FROM 254 ( 255 SELECT 256 a.Hdbh, 257 d.Name AS qz, 258 d.OrgAttributes AS qzbm, 259 e.Name AS dz, 260 e.OrgAttributes AS dzbm, 261 a.BillDate, 262 a.CreateTime, 263 c.StartTime, 264 c.EndTime, 265 f.TransferTime, 266 g.SignTime 267 FROM 268 shipment_bill a WITH ( NOLOCK ) 269 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 270 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 271 AND c.EndDepartId= 172 272 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 273 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 274 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 275 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 276 WHERE 277 a.IsValid= 1 278 AND a.BillDate= '2019-8-30' 279 ) x 280 LEFT JOIN ( 281 SELECT 282 a.Hdbh, 283 c.StartTime, 284 c.EndTime 285 FROM 286 shipment_bill a WITH ( NOLOCK ) 287 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 288 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 289 AND c.StartDepartId= 172 290 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 291 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 292 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 293 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 294 WHERE 295 a.IsValid= 1 296 AND a.BillDate= '2019-8-30' 297 ) y ON x.Hdbh = y.Hdbh 298 ) mdwfcyc 299 WHERE 300 mdwfcyc.网点到货时间 > '1970-01-01' 301 AND mdwfcyc.门店发车时间 = '1970-01-01' 302 GROUP BY 303 mdwfcyc.到站 304 ) mdwfcyc01 ON zs01.[到站] = mdwfcyc01.到站 305 LEFT JOIN ( 306 SELECT 307 kqwqyc.到站, 308 COUNT ( kqwqyc.到站 ) AS '库区未签异常' 309 FROM 310 ( 311 SELECT 312 x.Hdbh AS '单号', 313 x.qz AS '起站', 314 x.qzbm AS '起站部门类型', 315 x.dz AS '到站', 316 x.dzbm AS '到站部门类型', 317 x.BillDate AS '收件时间', 318 x.CreateTime AS '门店收件时间', 319 x.StartTime AS '门店发车时间', 320 x.EndTime AS '库区到货时间', 321 y.StartTime AS '库区发车时间', 322 y.EndTime AS '网点到货时间', 323 x.TransferTime AS '网点中转时间', 324 x.SignTime AS '签收时间', 325 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 326 FROM 327 ( 328 SELECT 329 a.Hdbh, 330 d.Name AS qz, 331 d.OrgAttributes AS qzbm, 332 e.Name AS dz, 333 e.OrgAttributes AS dzbm, 334 a.BillDate, 335 a.CreateTime, 336 c.StartTime, 337 c.EndTime, 338 f.TransferTime, 339 g.SignTime 340 FROM 341 shipment_bill a WITH ( NOLOCK ) 342 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 343 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 344 AND c.EndDepartId= 172 345 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 346 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 347 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 348 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 349 WHERE 350 a.IsValid= 1 351 AND a.BillDate= '2019-8-30' 352 ) x 353 LEFT JOIN ( 354 SELECT 355 a.Hdbh, 356 c.StartTime, 357 c.EndTime 358 FROM 359 shipment_bill a WITH ( NOLOCK ) 360 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 361 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 362 AND c.StartDepartId= 172 363 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 364 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 365 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 366 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 367 WHERE 368 a.IsValid= 1 369 AND a.BillDate= '2019-8-30' 370 ) y ON x.Hdbh = y.Hdbh 371 ) kqwqyc 372 WHERE 373 kqwqyc.库区到货时间 = '1970-01-01' 374 AND kqwqyc.网点到货时间 > '1970-01-01' 375 GROUP BY 376 kqwqyc.到站 377 ) kqwqyc01 ON zs01.到站 = kqwqyc01.到站 378 LEFT JOIN ( 379 SELECT 380 kqwfyc.到站, 381 COUNT ( kqwfyc.到站 ) AS '库区未发异常' 382 FROM 383 ( 384 SELECT 385 x.Hdbh AS '单号', 386 x.qz AS '起站', 387 x.qzbm AS '起站部门类型', 388 x.dz AS '到站', 389 x.dzbm AS '到站部门类型', 390 x.BillDate AS '收件时间', 391 x.CreateTime AS '门店收件时间', 392 x.StartTime AS '门店发车时间', 393 x.EndTime AS '库区到货时间', 394 y.StartTime AS '库区发车时间', 395 y.EndTime AS '网点到货时间', 396 x.TransferTime AS '网点中转时间', 397 x.SignTime AS '签收时间', 398 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 399 FROM 400 ( 401 SELECT 402 a.Hdbh, 403 d.Name AS qz, 404 d.OrgAttributes AS qzbm, 405 e.Name AS dz, 406 e.OrgAttributes AS dzbm, 407 a.BillDate, 408 a.CreateTime, 409 c.StartTime, 410 c.EndTime, 411 f.TransferTime, 412 g.SignTime 413 FROM 414 shipment_bill a WITH ( NOLOCK ) 415 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 416 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 417 AND c.EndDepartId= 172 418 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 419 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 420 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 421 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 422 WHERE 423 a.IsValid= 1 424 AND a.BillDate= '2019-8-30' 425 ) x 426 LEFT JOIN ( 427 SELECT 428 a.Hdbh, 429 c.StartTime, 430 c.EndTime 431 FROM 432 shipment_bill a WITH ( NOLOCK ) 433 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 434 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 435 AND c.StartDepartId= 172 436 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 437 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 438 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 439 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 440 WHERE 441 a.IsValid= 1 442 AND a.BillDate= '2019-8-30' 443 ) y ON x.Hdbh = y.Hdbh 444 ) kqwfyc 445 WHERE 446 kqwfyc.库区发车时间 = '1970-01-01' 447 AND kqwfyc.网点到货时间 > '1970-01-01' 448 GROUP BY 449 kqwfyc.到站 450 ) kqwfyc01 ON zs01.到站 = kqwfyc01.到站 451 LEFT JOIN ( 452 SELECT 453 mdwfc.到站, 454 COUNT ( mdwfc.到站 ) AS '门店未发车' 455 FROM 456 ( 457 SELECT 458 x.Hdbh AS '单号', 459 x.qz AS '起站', 460 x.qzbm AS '起站部门类型', 461 x.dz AS '到站', 462 x.dzbm AS '到站部门类型', 463 x.BillDate AS '收件时间', 464 x.CreateTime AS '门店收件时间', 465 x.StartTime AS '门店发车时间', 466 x.EndTime AS '库区到货时间', 467 y.StartTime AS '库区发车时间', 468 y.EndTime AS '网点到货时间', 469 x.TransferTime AS '网点中转时间', 470 x.SignTime AS '签收时间', 471 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 472 FROM 473 ( 474 SELECT 475 a.Hdbh, 476 d.Name AS qz, 477 d.OrgAttributes AS qzbm, 478 e.Name AS dz, 479 e.OrgAttributes AS dzbm, 480 a.BillDate, 481 a.CreateTime, 482 c.StartTime, 483 c.EndTime, 484 f.TransferTime, 485 g.SignTime 486 FROM 487 shipment_bill a WITH ( NOLOCK ) 488 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 489 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 490 AND c.EndDepartId= 172 491 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 492 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 493 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 494 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 495 WHERE 496 a.IsValid= 1 497 AND a.BillDate= '2019-8-30' 498 ) x 499 LEFT JOIN ( 500 SELECT 501 a.Hdbh, 502 c.StartTime, 503 c.EndTime 504 FROM 505 shipment_bill a WITH ( NOLOCK ) 506 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 507 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 508 AND c.StartDepartId= 172 509 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 510 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 511 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 512 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 513 WHERE 514 a.IsValid= 1 515 AND a.BillDate= '2019-8-30' 516 ) y ON x.Hdbh = y.Hdbh 517 ) mdwfc 518 WHERE 519 mdwfc.网点到货时间 = '1970-01-01' 520 AND mdwfc.库区发车时间 = '1970-01-01' 521 AND mdwfc.库区到货时间 = '1970-01-01' 522 AND mdwfc.门店发车时间 = '1970-01-01' 523 GROUP BY 524 mdwfc.到站 525 ) mdwfc01 ON zs01.到站 = mdwfc01.到站 526 LEFT JOIN ( 527 SELECT 528 mdfczt.到站, 529 COUNT ( mdfczt.到站 ) AS '门店发车在途' 530 FROM 531 ( 532 SELECT 533 x.Hdbh AS '单号', 534 x.qz AS '起站', 535 x.qzbm AS '起站部门类型', 536 x.dz AS '到站', 537 x.dzbm AS '到站部门类型', 538 x.BillDate AS '收件时间', 539 x.CreateTime AS '门店收件时间', 540 x.StartTime AS '门店发车时间', 541 x.EndTime AS '库区到货时间', 542 y.StartTime AS '库区发车时间', 543 y.EndTime AS '网点到货时间', 544 x.TransferTime AS '网点中转时间', 545 x.SignTime AS '签收时间', 546 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 547 FROM 548 ( 549 SELECT 550 a.Hdbh, 551 d.Name AS qz, 552 d.OrgAttributes AS qzbm, 553 e.Name AS dz, 554 e.OrgAttributes AS dzbm, 555 a.BillDate, 556 a.CreateTime, 557 c.StartTime, 558 c.EndTime, 559 f.TransferTime, 560 g.SignTime 561 FROM 562 shipment_bill a WITH ( NOLOCK ) 563 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 564 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 565 AND c.EndDepartId= 172 566 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 567 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 568 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 569 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 570 WHERE 571 a.IsValid= 1 572 AND a.BillDate= '2019-8-30' 573 ) x 574 LEFT JOIN ( 575 SELECT 576 a.Hdbh, 577 c.StartTime, 578 c.EndTime 579 FROM 580 shipment_bill a WITH ( NOLOCK ) 581 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 582 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 583 AND c.StartDepartId= 172 584 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 585 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 586 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 587 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 588 WHERE 589 a.IsValid= 1 590 AND a.BillDate= '2019-8-30' 591 ) y ON x.Hdbh = y.Hdbh 592 ) mdfczt 593 WHERE 594 mdfczt.网点到货时间 = '1970-01-01' 595 AND mdfczt.库区发车时间 = '1970-01-01' 596 AND mdfczt.库区到货时间 = '1970-01-01' 597 AND mdfczt.门店发车时间 > '1970-01-01' 598 GROUP BY 599 mdfczt.到站 600 ) mdfczt01 ON zs01.到站 = mdfczt01.到站 601 LEFT JOIN ( 602 SELECT 603 kqdh.到站, 604 COUNT ( kqdh.到站 ) AS '库区到货' 605 FROM 606 ( 607 SELECT 608 x.Hdbh AS '单号', 609 x.qz AS '起站', 610 x.qzbm AS '起站部门类型', 611 x.dz AS '到站', 612 x.dzbm AS '到站部门类型', 613 x.BillDate AS '收件时间', 614 x.CreateTime AS '门店收件时间', 615 x.StartTime AS '门店发车时间', 616 x.EndTime AS '库区到货时间', 617 y.StartTime AS '库区发车时间', 618 y.EndTime AS '网点到货时间', 619 x.TransferTime AS '网点中转时间', 620 x.SignTime AS '签收时间', 621 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 622 FROM 623 ( 624 SELECT 625 a.Hdbh, 626 d.Name AS qz, 627 d.OrgAttributes AS qzbm, 628 e.Name AS dz, 629 e.OrgAttributes AS dzbm, 630 a.BillDate, 631 a.CreateTime, 632 c.StartTime, 633 c.EndTime, 634 f.TransferTime, 635 g.SignTime 636 FROM 637 shipment_bill a WITH ( NOLOCK ) 638 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 639 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 640 AND c.EndDepartId= 172 641 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 642 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 643 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 644 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 645 WHERE 646 a.IsValid= 1 647 AND a.BillDate= '2019-8-30' 648 ) x 649 LEFT JOIN ( 650 SELECT 651 a.Hdbh, 652 c.StartTime, 653 c.EndTime 654 FROM 655 shipment_bill a WITH ( NOLOCK ) 656 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 657 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 658 AND c.StartDepartId= 172 659 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 660 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 661 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 662 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 663 WHERE 664 a.IsValid= 1 665 AND a.BillDate= '2019-8-30' 666 ) y ON x.Hdbh = y.Hdbh 667 ) kqdh 668 WHERE 669 kqdh.网点到货时间 = '1970-01-01' 670 AND kqdh.库区发车时间 = '1970-01-01' 671 AND kqdh.库区到货时间 > '1970-01-01' 672 GROUP BY 673 kqdh.到站 674 ) kqdh01 ON zs01.到站 = kqdh01.到站 675 LEFT JOIN ( 676 SELECT 677 kqfczt.到站, 678 COUNT ( kqfczt.到站 ) AS '库区发车在途' 679 FROM 680 ( 681 SELECT 682 x.Hdbh AS '单号', 683 x.qz AS '起站', 684 x.qzbm AS '起站部门类型', 685 x.dz AS '到站', 686 x.dzbm AS '到站部门类型', 687 x.BillDate AS '收件时间', 688 x.CreateTime AS '门店收件时间', 689 x.StartTime AS '门店发车时间', 690 x.EndTime AS '库区到货时间', 691 y.StartTime AS '库区发车时间', 692 y.EndTime AS '网点到货时间', 693 x.TransferTime AS '网点中转时间', 694 x.SignTime AS '签收时间', 695 datediff( MINUTE, x.createtime, y.endtime ) AS '耗时' 696 FROM 697 ( 698 SELECT 699 a.Hdbh, 700 d.Name AS qz, 701 d.OrgAttributes AS qzbm, 702 e.Name AS dz, 703 e.OrgAttributes AS dzbm, 704 a.BillDate, 705 a.CreateTime, 706 c.StartTime, 707 c.EndTime, 708 f.TransferTime, 709 g.SignTime 710 FROM 711 shipment_bill a WITH ( NOLOCK ) 712 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 713 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 714 AND c.EndDepartId= 172 715 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 716 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 717 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 718 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 719 WHERE 720 a.IsValid= 1 721 AND a.BillDate= '2019-8-30' 722 ) x 723 LEFT JOIN ( 724 SELECT 725 a.Hdbh, 726 c.StartTime, 727 c.EndTime 728 FROM 729 shipment_bill a WITH ( NOLOCK ) 730 JOIN shipment_transport b WITH ( NOLOCK ) ON a.Id = b.BillId 731 JOIN shipment_path c WITH ( NOLOCK ) ON a.Id= c.BillId 732 AND c.StartDepartId= 172 733 JOIN sys_organization d WITH ( NOLOCK ) ON b.StartDepartment= d.Id 734 JOIN sys_organization e WITH ( NOLOCK ) ON b.EndDepartment = e.Id 735 LEFT JOIN shipment_transfer f WITH ( NOLOCK ) ON a.Id= f.BillId 736 LEFT JOIN shipment_sign g WITH ( NOLOCK ) ON a.Id= g.BillId 737 WHERE 738 a.IsValid= 1 739 AND a.BillDate= '2019-8-30' 740 ) y ON x.Hdbh = y.Hdbh 741 ) kqfczt 742 WHERE 743 kqfczt.网点到货时间 = '1970-01-01' 744 AND kqfczt.库区发车时间 > '1970-01-01' 745 GROUP BY 746 kqfczt.到站 747 ) kqfczt01 ON zs01.到站 = kqfczt01.到站 748 JOIN 749 sys_organization bm ON zs01.[到站]=bm.Name 750
上天入地
无所不能
浙公网安备 33010602011771号