Advertisement
cevhyruz

PNP Confirmation SQL

Nov 6th, 2019
157
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
SQL 2.31 KB | None
  1. #-------------------------------------------------------------------------------
  2. # Delivered AND Undelivered  |
  3. #----------------------------
  4.  
  5. # undelivered
  6. SELECT COUNT(DISTINCT trackingno) AS Undelivered FROM status_details WHERE
  7. LEFT(UpdatedBy,3) = 'ang' AND LEFT(dateupdate,10)= '2019-11-05'
  8. AND undelivered = 'YES' AND NOT ISNULL(ThirdAttemptReason)
  9.  
  10. # delivered
  11. SELECT COUNT(DISTINCT trackingno) AS Delivered FROM status_details WHERE
  12. LEFT(UpdatedBy,3) = 'ang' AND LEFT(dateupdate,10)= '2019-11-05'
  13. AND delivered = 'YES'
  14.  
  15. #-------------------------------------------------------------------------------
  16. #  Branch TO Branch  |
  17. #--------------------
  18.  
  19. # COUNT ALL b2b
  20. SELECT COUNT(DISTINCT TrackingNo) AS B2B_Delivered FROM status_details
  21. WHERE LEFT(UpdatedBy, 3) = 'ang'          # branch
  22. AND LEFT(dateupdate, 10) = '2019-11-05'   # dated
  23. AND delivered = 'YES'                     # dvl
  24. AND TrackingNo REGEXP "^[B]"      
  25.  
  26. # SELECT ALL b2b
  27. SELECT * FROM status_details WHERE TrackingNo REGEXP "^[B]"
  28. AND LEFT(UpdatedBy, 3) = 'ang'
  29. AND LEFT(dateupdate, 10) = '2019-11-05'
  30. AND delivered = 'YES'
  31.  
  32. #-------------------------------------------------------------------------------
  33. #  HSBC - SOA    |
  34. #----------------
  35.  
  36. # SELECT ALL HSBC-SOA
  37. SELECT * FROM status_details WHERE TrackingNo REGEXP "^[A-Z0-9]{17,}"
  38. AND LEFT(UpdatedBy, 3) = 'ang'
  39. AND LEFT(dateupdate, 10) = '2019-11-05'
  40. AND delivered = 'YES'
  41.  
  42. #--------------------------------------------------------------------------------
  43. #  AC / CSU / EP   |
  44. #------------------
  45.  
  46. # SELECT ALL AC / CSU / EP
  47. SELECT * FROM status_details WHERE TrackingNo REGEXP "^[0-9]{7}"
  48. AND LEFT(UpdatedBy, 3) = 'ang'
  49. AND LEFT(dateupdate, 10) = '2019-11-05'
  50. AND delivered = 'YES'
  51.  
  52. #--------------------------------------------------------------------------------
  53. #  No Record Found   |
  54. #--------------------
  55.  
  56. # COUNT no record found
  57. SELECT COUNT(DISTINCT trackingno) AS Delivered FROM status_details_no_record_found WHERE
  58. LEFT(UpdatedBy,3) = 'ang'
  59. AND LEFT(dateupdate,10)= '2019-11-05'
  60. AND delivered = 'YES'
  61.  
  62. # SELECT ALL no record found
  63. SELECT * FROM status_details_no_record_found WHERE
  64. LEFT(UpdatedBy, 3) = 'ang'
  65. AND LEFT(dateupdate, 10) = '2019-11-05'
  66. AND delivered = 'YES'
  67.  
  68. #--------------------------------------------------------------------------------
Advertisement
RAW Paste Data Copied
Advertisement