API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Jun 19th, 2019
98
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 10.58 KB | None | 0 0
raw download clone embed print report
  1. | Element_ID | Element | Transaction_Date | Transaction_Quantity | Total_Inventory |
  2. |:----------:|:----------:|:----------------:|:--------------------:|:---------------:|
  3. | | STOCK | | 5 | 5 |
  4. | MO302 | Make_Order | 1/3/2019 | 1 | 6 |
  5. | SO105 | Sale | 2/1/2019 | -1 | 5 |
  6. | SO106 | Sale | 2/1/2019 | -1 | 4 |
  7. | MO323 | Make_Order | 2/2/2019 | 1 | 5 |
  8. | SO107 | Sale | 2/4/2019 | -1 | 4 |
  9. | SO191 | Sale | 2/5/2019 | -1 | 3 |
  10. | SO123 | Sale | 2/6/2019 | -1 | 2 |
  11. | SO166 | Sale | 3/1/2019 | -1 | 1 |
  12. | SO819 | Sale | 3/5/2019 | -1 | 0 |
  13. | SO603 | Sale | 3/10/2019 | -4 | -3 |
  14. | MO400 | Make_Order | 3/15/2019 | 1 | -2 |
  15. | MO459 | Make_Order | 3/15/2019 | 1 | -1 |
  16. | MO460 | Make_Order | 3/18/2019 | 1 | 0 |
  17. | MO491 | Make_Order | 3/19/2019 | 1 | 1 |
  18. | MO715 | Make_Order | 4/1/2019 | 3 | 4 |
  19. | SO100 | Sale | 4/2/2019 | -1 | 3 |
  20. | SO322 | Sale | 4/3/2019 | -1 | 2 |
  21. | SO874 | Sale | 4/4/2019 | -1 | 1 |
  22. | SO222 | Sale | 4/5/2019 | -1 | 0 |
  23. | MO999 | Make_Order | 4/5/2019 | 1 | 1 |
  24. | SO999 | Sale | 4/6/2019 | -1 | 0 |
  25.  
  26. | Element_ID | Element | Transaction_Date | Transaction_Quantity | Total_Inventory | Replenishment | Replenishment_Date |
  27. |:----------:|:----------:|:----------------:|:--------------------:|:---------------:|:-------------:|:------------------:|
  28. | | STOCK | | 5 | 5 | NULL | NULL |
  29. | MO302 | Make_Order | 1/3/2019 | 1 | 6 | NULL | NULL |
  30. | SO105 | Sale | 2/1/2019 | -1 | 5 | STOCK | NULL |
  31. | SO106 | Sale | 2/1/2019 | -1 | 4 | STOCK | NULL |
  32. | MO323 | Make_Order | 2/2/2019 | 1 | 5 | NULL | NULL |
  33. | SO107 | Sale | 2/4/2019 | -1 | 4 | STOCK | NULL |
  34. | SO191 | Sale | 2/5/2019 | -1 | 3 | STOCK | NULL |
  35. | SO123 | Sale | 2/6/2019 | -1 | 2 | STOCK | NULL |
  36. | SO166 | Sale | 3/1/2019 | -1 | 1 | MO302 | 1/3/2019 |
  37. | SO819 | Sale | 3/5/2019 | -1 | 0 | MO323 | 2/2/2019 |
  38. | SO603 | Sale | 3/10/2019 | -4 | -3 | MO460 | 3/18/2019 |
  39. | MO400 | Make_Order | 3/15/2019 | 1 | -2 | NULL | NULL |
  40. | MO459 | Make_Order | 3/15/2019 | 1 | -1 | NULL | |
  41. | MO460 | Make_Order | 3/18/2019 | 1 | 0 | NULL | NULL |
  42. | MO491 | Make_Order | 3/19/2019 | 1 | 1 | NULL | NULL |
  43. | MO715 | Make_Order | 4/1/2019 | 3 | 4 | NULL | NULL |
  44. | SO100 | Sale | 4/2/2019 | -1 | 3 | MO491 | 3/19/2019 |
  45. | SO322 | Sale | 4/3/2019 | -1 | 2 | MO715 | 4/1/2019 |
  46. | SO874 | Sale | 4/4/2019 | -1 | 1 | MO715 | 4/1/2019 |
  47. | SO222 | Sale | 4/5/2019 | -1 | 0 | MO715 | 4/1/2019 |
  48. | MO999 | Make_Order | 4/5/2019 | 1 | 1 | NULL | NULL |
  49. | SO999 | Sale | 4/6/2019 | -1 | 0 | SO999 | 4/5/2019 |
  50.  
  51. for curr in transaction_quantity:
  52. if curr < 0:
  53. if stock.exists() and stock.notempty():
  54. fill in data from that
  55. else:
  56. find next replenishment
  57. fill in data from that
  58. else:
  59. next
  60.  
  61. /****** WiP Script ******/
  62. SELECT
  63. [jerry].[dbo].[purchases_new].*,
  64. CASE WHEN Transaction_Quantity < 0 THEN -- (SELECT Element_ID FROM the_current_row WHERE transaction_quantity > 0)
  65. ELSE NULL AS "Replenishment",
  66. -- (SELECT Transaction_Date FROM [jerry].[dbo].[purchases_new] WHERE Element_ID
  67. -- Not sure how to grab the correct date of the element id from the column before
  68. FROM
  69. [jerry].[dbo].[purchases_new]
  70.  
  71. DECLARE @T TABLE(Element_ID NVARCHAR(50),Element NVARCHAR(50), Transaction_Date DATETIME,Transaction_Quantity INT,Total_Inventory INT)
  72. INSERT @T VALUES
  73. ('MO301','Make_Order','1/1/2019',1,1),
  74. ('MO302','Make_Order','1/3/2019',1,2),
  75. ('SO105','Sale','2/1/2019',-1,1),
  76. ('SO106','Sale','2/1/2019',-1,0),
  77. ('MO323','Make_Order','2/2/2019',1,1),
  78. ('SO107','Sale','2/4/2019',-1,0),
  79. ('SO191','Sale','2/5/2019',-1,-1),
  80. ('SO123','Sale','2/6/2019',-1,-2),
  81. ('SO166','Sale','3/1/2019',-1,-3),
  82. ('SO603','Sale','3/2/2019',-1,-4),
  83. ('MO400','Make_Order','3/15/2019',1,-3),
  84. ('MO459','Make_Order','3/15/2019',1,-2),
  85. ('MO460','Make_Order','3/18/2019',1,-1),
  86. ('MO491','Make_Order','3/19/2019',1,0)
  87.  
  88. ;WITH Normalized AS
  89. (
  90. SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM @T
  91. )
  92. ,Additives AS
  93. (
  94. SELECT *, AddedOrder = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Normalized WHERE Transaction_Quantity>0
  95. )
  96. ,Subtractions AS
  97. (
  98. SELECT *, SubtractedOrder = ROW_NUMBER() OVER (ORDER BY (SELECT 0))FROM Normalized WHERE Transaction_Quantity<0
  99. )
  100. ,WithTies AS
  101. (
  102. SELECT
  103. S.RowNumber,
  104. S.Element_ID,
  105. BoughtFromRowNumber = A.RowNumber,
  106. SoldToID =S.Element_ID,
  107. BoughFromID=A.Element_ID,
  108. S.Element,
  109. S.Transaction_Date,
  110. S.Transaction_Quantity,
  111. S.Total_Inventory
  112. FROM
  113. Additives A
  114. LEFT OUTER JOIN Subtractions S ON A.AddedOrder=S.SubtractedOrder
  115.  
  116. UNION
  117.  
  118. SELECT
  119. A.RowNumber,
  120. A.Element_ID,
  121. BoughtFromRowNumber = S.RowNumber,
  122. SoldToID = NULL,
  123. BoughFromID=NULL,
  124. A.Element,
  125. A.Transaction_Date,
  126. A.Transaction_Quantity,
  127. A.Total_Inventory
  128. FROM
  129. Additives A
  130. LEFT OUTER JOIN Subtractions S ON A.AddedOrder=S.SubtractedOrder
  131. )
  132. SELECT
  133. T.Element_ID,
  134. T.Element,
  135. T.Transaction_Date,
  136. T.Transaction_Quantity,
  137. T.Total_Inventory,
  138. T2.SoldToID,
  139. T.BoughFromID
  140. FROM
  141. WithTies T
  142. LEFT OUTER JOIN WithTies T2 ON T2.BoughtFromRowNumber= T.RowNumber
  143. ORDER BY
  144. T.RowNumber
  145.  
  146. DECLARE @T TABLE(Element_ID NVARCHAR(50),Element NVARCHAR(50), Transaction_Date DATETIME,Transaction_Quantity INT,Total_Inventory INT)
  147. INSERT @T VALUES
  148. ('MO301','Make_Order','1/1/2019',5,1),
  149. ('MO302','Make_Order','1/3/2019',1,2),
  150. ('SO105','Sale','2/1/2019',-2,1),
  151. ('SO106','Sale','2/1/2019',-1,0),
  152. ('MO323','Make_Order','2/2/2019',1,1),
  153. ('SO107','Sale','2/4/2019',-1,0),
  154. ('SO191','Sale','2/5/2019',-1,-1),
  155. ('SO123','Sale','2/6/2019',-1,-2),
  156. ('SO166','Sale','3/1/2019',-1,-3),
  157. ('SO603','Sale','3/2/2019',-1,-4),
  158. ('MO400','Make_Order','3/15/2019',1,-3),
  159. ('MO459','Make_Order','3/15/2019',1,-2),
  160. ('MO460','Make_Order','3/18/2019',1,-1),
  161. ('MO491','Make_Order','3/19/2019',1,0)
  162.  
  163.  
  164. ;WITH Normalized AS
  165. (
  166. SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY (SELECT 0)), IsAdd = CASE WHEN Transaction_Quantity>0 THEN 1 ELSE 0 END FROM @T
  167. )
  168.  
  169. ,ReplicateAmount AS
  170. (
  171. SELECT Element_ID, Element, Transaction_Date, Transaction_Quantity=ABS(Transaction_Quantity) ,Total_Inventory, RowNumber, IsAdd
  172. FROM Normalized
  173.  
  174. UNION ALL
  175.  
  176. SELECT R.Element_ID, R.Element, R.Transaction_Date, Transaction_Quantity=(R.Transaction_Quantity - 1), R.Total_Inventory, R.RowNumber, R.IsAdd
  177. FROM ReplicateAmount R INNER JOIN Normalized N ON R.RowNumber = N.RowNumber
  178. WHERE ABS(R.Transaction_Quantity) > 1
  179. )
  180. ,NormalizedAgain AS
  181. (
  182. SELECT Element_ID, Element, Transaction_Date, Transaction_Quantity=1, Total_Inventory, RowNumber = ROW_NUMBER() OVER (ORDER BY RowNumber), IsAdd FROM ReplicateAmount
  183. )
  184. ,Additives AS
  185. (
  186. SELECT *, AddedOrder = ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM NormalizedAgain WHERE IsAdd=1
  187. )
  188. ,Subtractions AS
  189. (
  190. SELECT Element_ID, Element, Transaction_Date, Transaction_Quantity=-1 , Total_Inventory, RowNumber, SubtractedOrder = ROW_NUMBER() OVER (ORDER BY (SELECT 0))FROM NormalizedAgain WHERE IsAdd=0
  191. )
  192. ,WithTies AS
  193. (
  194. SELECT
  195. S.RowNumber,
  196. S.Element_ID,
  197. BoughtFromRowNumber = A.RowNumber,
  198. SoldToID =S.Element_ID,
  199. BoughFromID=A.Element_ID,
  200. S.Element,
  201. S.Transaction_Date,
  202. S.Transaction_Quantity,
  203. S.Total_Inventory
  204. FROM
  205. Additives A
  206. LEFT OUTER JOIN Subtractions S ON A.AddedOrder=S.SubtractedOrder
  207.  
  208. UNION
  209.  
  210. SELECT
  211. A.RowNumber,
  212. A.Element_ID,
  213. BoughtFromRowNumber = S.RowNumber,
  214. SoldToID = NULL,
  215. BoughFromID=NULL,
  216. A.Element,
  217. A.Transaction_Date,
  218. A.Transaction_Quantity,
  219. A.Total_Inventory
  220. FROM
  221. Additives A
  222. LEFT OUTER JOIN Subtractions S ON A.AddedOrder=S.SubtractedOrder
  223. )
  224. SELECT
  225. T.RowNumber,
  226. T.Element_ID,
  227. T.Element,
  228. T.Transaction_Date,
  229. T.Transaction_Quantity,
  230. T.Total_Inventory,
  231. T2.SoldToID,
  232. T.BoughFromID
  233. FROM
  234. WithTies T
  235. LEFT OUTER JOIN WithTies T2 ON T2.BoughtFromRowNumber= T.RowNumber
  236. WHERE
  237. NOT T.RowNumber IS NULL
  238. ORDER BY
  239. T.RowNumber
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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!