daily pastebin goal
39%
SHARE
TWEET

Untitled

a guest Jan 18th, 2019 59 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. select * from AOFDOXF DOXF
  2. LEFT JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  3. WHERE IDXT.IXXMLK = 'DATE'
  4. AND DOXF.DXINNR = 312100
  5.  
  6. --dd/MM/yyyy
  7. --dd/MM/yyyy HH:mm:ss
  8. -- ??? 12421    1   20144-06-30   , 462722  1   0066-01-17
  9.  
  10. select IXIXID FROM AOFIDXT WHERE IXXMLK = 'DATE';
  11.  
  12. select distinct DOXF.DXIXDA FROM AOFDOXF DOXF
  13. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  14. WHERE (IDXT.IXXMLK = 'DATE')
  15. AND NOT(DOXF.DXIXDA like '%/%')
  16. AND NOT(DOXF.DXIXDA like '%-%')
  17.  
  18. --NA , na , ou vide
  19.  
  20. select top 10 * from AOFDOXF DOXF;
  21. select top 1000 * from AOFIDXT;
  22.  
  23.  
  24. select count(*) FROM AOFDOXF DOXF
  25. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  26. WHERE IDXT.IXXMLK = 'DATE'
  27. AND (DOXF.DXIXDA = '' OR DOXF.DXIXDA like '%NA%' OR DOXF.DXIXDA like '%na%');
  28.  
  29. -- get values to be cleaned up
  30. select COUNT(*) FROM AOFDOXF DOXF
  31. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  32. WHERE IDXT.IXXMLK = 'DATE'
  33. AND (DOXF.DXIXDA = '' OR DOXF.DXIXDA like '%NA%' OR DOXF.DXIXDA like '%na%');
  34.  
  35.  
  36. -- get innr values to be cleaned up
  37. select DOXF.DXINNR , DOXF.DXDONR FROM AOFDOXF DOXF
  38. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  39. WHERE IDXT.IXXMLK = 'DATE'
  40. AND (DOXF.DXIXDA = '' OR DOXF.DXIXDA like '%NA%' OR DOXF.DXIXDA like '%na%');
  41.  
  42. --get DATE values containing / or -
  43. select distinct DOXF.DXIXDA FROM AOFDOXF DOXF
  44. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  45. WHERE (IDXT.IXXMLK = 'DATE')
  46. AND ((DOXF.DXIXDA like '%/%')
  47. OR (DOXF.DXIXDA like '%-%'))
  48.  
  49.  
  50.  
  51. --count/lister des dates qui ne sont pas sous format yyyy-MM-dd ??
  52.  
  53. select DOXF.DXINNR , DOXF.DXDONR , DOXF.DXIXDA  FROM AOFDOXF DOXF
  54. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  55. WHERE (IDXT.IXXMLK = 'DATE')
  56. AND TRY_PARSE(DOXF.DXIXDA as date) is null
  57. order by DOXF.DXIXDA;
  58.  
  59. select DOXF.DXINNR , DOXF.DXDONR , DOXF.DXIXDA  FROM AOFDOXF DOXF
  60. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  61. WHERE (IDXT.IXXMLK = 'DATE')
  62. AND TRY_PARSE(DOXF.DXIXDA as date) is null;
  63. --AND DOXF.DXIXDA like '%-%'
  64.  
  65.  
  66. select top 10 DOXF.DXINNR  FROM AOFDOXF DOXF
  67. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  68. WHERE (IDXT.IXXMLK = 'DATE')
  69. AND TRY_PARSE(DOXF.DXIXDA as date) is null;
  70.  
  71.  
  72. select DOXF.DXINNR , DOXF.DXDONR , DOXF.DXIXDA  FROM AOFDOXF DOXF
  73. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  74. WHERE (IDXT.IXXMLK = 'DATE')
  75. AND TRY_PARSE(DOXF.DXIXDA as date) is null
  76.  
  77.  
  78.  
  79.  
  80. select DISTINCT DOXF.DXIXDA FROM AOFDOXF DOXF
  81. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  82. WHERE (IDXT.IXXMLK = 'DATE')
  83. AND TRY_PARSE(DOXF.DXIXDA as date) is null;
  84.  
  85. select top 10 DOXF.DXINNR FROM AOFDOXF DOXF
  86. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  87. WHERE (IDXT.IXXMLK = 'DATE')
  88. AND TRY_PARSE(DOXF.DXIXDA as date) is null;
  89.  
  90. --get indexes that contains value not in the right format (yyyy-MM-dd)
  91. select distinct DOXF.DXIXID  FROM AOFDOXF DOXF
  92. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  93. WHERE (IDXT.IXXMLK = 'DATE')
  94. AND TRY_PARSE(DOXF.DXIXDA as date) is null;
  95.  
  96.  
  97. --get DATE values not containing / or -
  98. select distinct DOXF.DXIXDA FROM AOFDOXF DOXF
  99. INNER JOIN  AOFIDXT IDXT on IDXT.IXIXID = DOXF.DXIXID
  100. WHERE (IDXT.IXXMLK = 'DATE')
  101. AND NOT ((DOXF.DXIXDA like '%/%')
  102. OR (DOXF.DXIXDA like '%-%'))
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top