API tools faq
paste
Guest User

Untitled

a guest
Jan 19th, 2018
89
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.18 KB | None | 0 0
raw download clone embed print report
  1. With raw_measurements as (
  2. (
  3. SELECT
  4. dbo.MEASUREMENTS.LSTRUCNO AS "Struct",
  5. dbo.MEASUREMENTS.LARTINO AS "Arti",
  6. dbo.MEASUREMENTS.LJOBNO AS "Job",
  7. dbo.MEASUREMENTS.LPART AS "Part",
  8. dbo.MEASUREMENTS.LMESID AS "Mes_id",
  9. dbo.MEASUREMENTS.LSAMP AS "Samp",
  10. dbo.MEASUREMENTS.LITEMNO AS "Item",
  11. dateadd(minute,CAST(RIGHT(RTRIM(dbo.MEASUREMENTS.LMEASDATE),2) as Int),dateadd(HOUR,cast(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,9,2) as Int),dateadd(week,CAST(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,5,2) as Int)-1,dateadd(year,CAST(LEFT(dbo.MEASUREMENTS.LMEASDATE,4) as Int)-1900,0))+ CAST(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,7,1) as Int))) AS fixed_date,
  12. CAST(dbo.MEASUREMENTS.LMES_1 AS FLOAT)/CAST(100 AS FLOAT) AS "Mes_val",
  13. 1 as number,
  14. (CAST(dbo.MEASUREMENTS.LMES_12 AS INT)/CAST(100 AS INT)) AS "Mes_val_quant"
  15. FROM
  16. dbo.K_DBQ_MESDATA
  17. where
  18. 1 <= (CAST(dbo.MEASUREMENTS.LMES_12 AS INT)/CAST(100 AS INT))
  19. AND (dbo.MEASUREMENTS.LJOBNO like ('111111'))
  20. )
  21. UNION ALL
  22. (
  23. SELECT
  24. dbo.MEASUREMENTS.LSTRUCNO AS "Struct",
  25. dbo.MEASUREMENTS.LARTINO AS "Arti",
  26. dbo.MEASUREMENTS.LJOBNO AS "Job",
  27. dbo.MEASUREMENTS.LPART AS "Part",
  28. dbo.MEASUREMENTS.LMESID AS "Mes_id",
  29. dbo.MEASUREMENTS.LSAMP AS "Samp",
  30. dbo.MEASUREMENTS.LITEMNO AS "Item",
  31. dateadd(minute,CAST(RIGHT(RTRIM(dbo.MEASUREMENTS.LMEASDATE),2) as Int),dateadd(HOUR,cast(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,9,2) as Int),dateadd(week,CAST(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,5,2) as Int)-1,dateadd(year,CAST(LEFT(dbo.MEASUREMENTS.LMEASDATE,4) as Int)-1900,0))+ CAST(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,7,1) as Int))) AS fixed_date,
  32. CAST(dbo.MEASUREMENTS.LMITTU_2 AS FLOAT)/CAST(100 AS FLOAT) AS "Mes_val",
  33. 2 as number,
  34. (CAST(dbo.MEASUREMENTS.LMES_12 AS INT)/CAST(100 AS INT)) AS "Mes_val_quant"
  35. FROM
  36. dbo.K_DBQ_MESDATA
  37. where
  38. 2 <= (CAST(dbo.MEASUREMENTS.LMES_12 AS INT)/CAST(100 AS INT))
  39. AND (dbo.MEASUREMENTS.LJOBNO like ('111111'))
  40. )
  41. UNION ALL
  42.  
  43. [... 10 of these all together]
  44.  
  45. UNION ALL
  46. (SELECT
  47. dbo.MEASUREMENTS.LSTRUCNO AS "Struct",
  48. dbo.MEASUREMENTS.LARTINO AS "Arti",
  49. dbo.MEASUREMENTS.LJOBNO AS "Job",
  50. dbo.MEASUREMENTS.LPART AS "Part",
  51. dbo.MEASUREMENTS.LMESID AS "Mes_id",
  52. dbo.MEASUREMENTS.LSAMP AS "Samp",
  53. dbo.MEASUREMENTS.LITEMNO AS "Item",
  54. dateadd(minute,CAST(RIGHT(RTRIM(dbo.MEASUREMENTS.LMEASDATE),2) as Int),dateadd(HOUR,cast(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,9,2) as Int),dateadd(week,CAST(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,5,2) as Int)-1,dateadd(year,CAST(LEFT(dbo.MEASUREMENTS.LMEASDATE,4) as Int)-1900,0))+ CAST(SUBSTRING(dbo.MEASUREMENTS.LMEASDATE,7,1) as Int))) AS fixed_date,
  55. CAST(dbo.MEASUREMENTS.LMES_10 AS FLOAT)/CAST(100 AS FLOAT) AS "Mes_val",
  56. 10 as number,
  57. (CAST(dbo.MEASUREMENTS.LMES_12 AS INT)/CAST(100 AS INT)) AS "Mes_val_quant"
  58. FROM
  59. dbo.K_DBQ_MESDATA
  60. where
  61. 10 <= (CAST(dbo.MEASUREMENTS.LMES_12 AS INT)/CAST(100 AS INT))
  62. AND (dbo.MEASUREMENTS.LJOBNO like ('111111'))
  63. )
  64. ),
  65.  
  66. accepted as (
  67. select
  68. Part,
  69. Mes_id,
  70. max(Samp) as 'Samp_max',
  71. Item
  72. from
  73. raw_measurements
  74. group by
  75. Part,
  76. Mes_id,
  77. Item
  78. ),
  79.  
  80. clean_measurements as (
  81. select
  82. *
  83. from raw_measurements
  84. where
  85. concat(Part, Mes_id, Samp, Item) in (
  86. select
  87. concat(Part, Mes_id, Samp_max, Item)
  88. from
  89. accepted
  90. )
  91. ),
  92.  
  93. quality_params as (
  94. (
  95. select
  96. 'struct' as 'type',
  97. '999999' as art,
  98. crepo,
  99. CSURF,
  100. CLOW1,
  101. CLOW2,
  102. CLAV,
  103. CUPP2,
  104. CUPP1,
  105. STRUCTNO,
  106. MES_ID,
  107. PARTID
  108. from dbo.DB_ST_QUALPAR
  109. )
  110. UNION ALL
  111. (
  112. select
  113. 'art' as 'type',
  114. anumero as art,
  115. crepo,
  116. CSURF,
  117. CLOW1,
  118. CLOW2,
  119. CLAV,
  120. CUPP2,
  121. CUPP1,
  122. STRUCTNO,
  123. MES_ID,
  124. PARTID
  125. from from dbo.DB_AR_QUALPAR
  126. )
  127. )
  128.  
  129. select
  130. a.Job,
  131. c.art,
  132. a.Arti,
  133. a.Struct,
  134. a.Part,
  135. c.type,
  136. a.Mes_id,
  137. c.crepo,
  138. c.CSURF',
  139. CAST(AVG(a.Mes_val) AS Decimal(8,2)) AS AVG,
  140. CAST(MIN(a.Mes_val) AS Decimal(8,2)) AS MIN,
  141. CAST(MAX(a.Mes_val) AS Decimal(8,2)) AS MAX,
  142. d.LOMINA,
  143. d.LOLOSU,
  144. b.LOMINA,
  145. LTRIM(RTRIM(b.LOLOSU)),
  146. LTRIM(RTRIM(b.LYKSIK)),
  147. CAST(c.CLOW1/100.00 AS Decimal(8,2)) AS CLOW1,
  148. CAST(c.CLOW2/100.00 AS Decimal(8,2)) AS CLOW2,
  149. CAST(c.CLAV/100.00 AS Decimal(8,2)) AS CLAV,
  150. CAST(c.CUPP2/100.00 AS Decimal(8,2)) AS CUPP2,
  151. CAST(c.CUPP1/100.00 AS Decimal(8,2)) AS CUPP1
  152. from clean_measurements a
  153. JOIN dbo.K_DBLAOH_TESTID b ON a.Mes_id=b.LMESID
  154. JOIN quality_params c ON (a.Struct=c.STRUCTNO AND a.Mes_id=c.MES_ID AND a.Part=c.PARTID)
  155. JOIN dbo.K_DBLAOH_TESKID d ON a.Mes_id=d.LMESID
  156. WHERE
  157. a.Job LIKE '111111'
  158. AND c.crepo LIKE 'X'
  159. AND d.LANG LIKE '001'
  160. AND (c.art=a.Arti OR c.art='999999')
  161. GROUP BY
  162. a.Part,
  163. a.Mes_id,
  164. d.LOMINA,
  165. d.LOLOSU,
  166. b.LOMINA,
  167. b.LOLOSU,
  168. b.LYKSIK,
  169. c.crepo,
  170. c.CSURF,
  171. a.Job,
  172. a.Struct,
  173. c.CLOW1,
  174. c.CLOW2,
  175. c.CLAV,
  176. c.CUPP2,
  177. c.CUPP1,
  178. type,
  179. c.art,
  180. a.Arti
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!