API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 21st, 2019
75
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 6.04 KB | None | 0 0
raw download clone embed print report
  1. CREATE TABLE #T (ID INT , ProductId INT, MemberId INT, SessionId VARCHAR(100), CreatedOn DATETIME, Quantity INT)
  2. INSERT INTO #T SELECT 62,1 ,2 ,' ','2012-09-11',1
  3. INSERT INTO #T SELECT 63, 2,2, ' ','2012-09-11',1
  4. INSERT INTO #T SELECT 64, 1,0,'agzdeoqubwokfme05wwmne55','2012-09-11',1
  5. INSERT INTO #T SELECT 65, 3,0,'agzdeoqubwokfme05wwmne55','2012-09-11',1
  6.  
  7. ;With CTE AS
  8. (SELECT
  9. ID
  10. ,ProductId
  11. ,MemberId = MAX(MemberId) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  12. ,SessionID
  13. ,CreatedOn = CONVERT(VARCHAR(10),MAX(CreatedOn) OVER(PARTITION BY ProductID ORDER bY(SELECT 1)),20)
  14. ,Quantity = SUM(Quantity) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  15. ,Rn = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  16. FROM #T)
  17.  
  18. SELECT ID,ProductId,MemberId,SessionID,CreatedOn,Quantity FROM CTE WHERE Rn = 1
  19.  
  20. DROP TABLE #T
  21.  
  22. CREATE TABLE #T (ID INT , ProductId INT, MemberId INT, SessionId VARCHAR(100), CreatedOn DATETIME, Quantity INT)
  23. INSERT INTO #T SELECT 62,1 ,2 ,' ','2012-09-11',1
  24. INSERT INTO #T SELECT 63, 2,2, ' ','2012-09-11',1
  25. INSERT INTO #T SELECT 64, 1,0,'agzdeoqubwokfme05wwmne55','2012-09-11',1
  26. INSERT INTO #T SELECT 65, 3,0,'agzdeoqubwokfme05wwmne55','2012-09-11',1
  27.  
  28. ;With CTE AS
  29. (SELECT
  30. ID
  31. ,ProductId
  32. ,MemberId = MAX(MemberId) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  33. ,SessionID
  34. ,CreatedOn = CONVERT(VARCHAR(10),MAX(CreatedOn) OVER(PARTITION BY ProductID ORDER bY(SELECT 1)),20)
  35. ,Quantity = SUM(Quantity) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  36. ,Rn = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  37. FROM #T)
  38.  
  39. SELECT ID,ProductId,MemberId,SessionID,CreatedOn,Quantity FROM CTE WHERE Rn = 1
  40.  
  41. DROP TABLE #T
  42.  
  43. CREATE TABLE #T (ID INT , ProductId INT, MemberId INT, SessionId VARCHAR(100), CreatedOn DATETIME, Quantity INT)
  44. INSERT INTO #T SELECT 62,1 ,2 ,' ','2012-09-11',1
  45. INSERT INTO #T SELECT 63, 2,2, ' ','2012-09-11',1
  46. INSERT INTO #T SELECT 64, 1,0,'agzdeoqubwokfme05wwmne55','2012-09-11',1
  47. INSERT INTO #T SELECT 65, 3,0,'agzdeoqubwokfme05wwmne55','2012-09-11',1
  48.  
  49. ;With CTE AS
  50. (SELECT
  51. ID
  52. ,ProductId
  53. ,MemberId = MAX(MemberId) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  54. ,SessionID
  55. ,CreatedOn = CONVERT(VARCHAR(10),MAX(CreatedOn) OVER(PARTITION BY ProductID ORDER bY(SELECT 1)),20)
  56. ,Quantity = SUM(Quantity) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  57. ,Rn = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  58. FROM #T)
  59.  
  60. SELECT ID,ProductId,MemberId,SessionID,CreatedOn,Quantity FROM CTE WHERE Rn = 1
  61.  
  62. DROP TABLE #T
  63.  
  64. ID ProductId MemberId SessionID CreatedOn Quantity
  65. 62 1 2 2012-09-11 2
  66. 63 2 2 2012-09-11 1
  67. 65 3 0 agzdeoqubwokfme05wwmne55 2012-09-11 1
  68.  
  69. UPDATE #T
  70. SET
  71. #T.ID = c.ID
  72. ,#T.ProductId =c.ProductId
  73. ,#T.MemberId = c.MemberId
  74. ,#T.SessionID = c.SessionID
  75. ,#T.CreatedOn = c.CreatedOn
  76. ,#T.Quantity = c.Quantity
  77. FROM #T
  78. INNER JOIN CTE c
  79. ON #T.ID = c.ID
  80. WHERE c.Rn = 1
  81.  
  82. ID ProductId MemberId SessionId CreatedOn Quantity
  83. 62 1 2 2012-09-11 2
  84. 63 2 2 2012-09-11 1
  85. 64 1 0 agzdeoqubwokfme05wwmne5 2012-09-11 1
  86. 65 3 0 agzdeoqubwokfme05wwmne55 2012-09-11 1
  87.  
  88. ;With CTE AS
  89. (SELECT
  90. ID
  91. ,ProductId
  92. ,MemberId = MAX(MemberId) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  93. ,SessionID
  94. ,CreatedOn = CONVERT(VARCHAR(10),MAX(CreatedOn) OVER(PARTITION BY ProductID ORDER bY(SELECT 1)),20)
  95. ,Quantity = SUM(Quantity) OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  96. ,Rn = ROW_NUMBER() OVER(PARTITION BY ProductID ORDER bY(SELECT 1))
  97. FROM #T)
  98.  
  99. UPDATE #T
  100. SET
  101. #T.ID = c.ID
  102. ,#T.ProductId =c.ProductId
  103. ,#T.MemberId = c.MemberId
  104. ,#T.SessionID = c.SessionID
  105. ,#T.CreatedOn = CONVERT(VARCHAR(10),c.CreatedOn,20)
  106. ,#T.Quantity = c.Quantity
  107. FROM #T
  108. INNER JOIN CTE c
  109. ON #T.ID = c.ID
  110. WHERE c.Rn = 1
  111.  
  112. SELECT * FROM #T
  113.  
  114. DROP TABLE #T
  115.  
  116. UPDATE #T
  117. SET
  118. #T.ID = c.ID
  119. ,#T.ProductId =c.ProductId
  120. ,#T.MemberId = c.MemberId
  121. ,#T.SessionID = c.SessionID
  122. ,#T.CreatedOn = c.CreatedOn
  123. ,#T.Quantity = c.Quantity
  124. FROM #T
  125. INNER JOIN (SELECT t.ID,t.ProductID,t.MemberId,t.SessionId,t.CreatedOn,x.Quantity
  126. FROM #T t
  127. JOIN (
  128. SELECT
  129. ID = MIN(ID)
  130. ,ProductID
  131. ,MemberId = MAX(MemberId)
  132. ,CreatedOn = MAX(CreatedOn)
  133. ,Quantity = SUM(Quantity)
  134. FROM #T
  135. GROUP BY ProductID
  136. )X
  137. ON t.ID =X.ID) c
  138. ON #T.ID = c.ID
  139.  
  140.  
  141. SELECT * FROM #T
  142. DROP TABLE #T
  143.  
  144. SELECT ProductID,
  145. max(MemberId),
  146. max(CreatedOn),
  147. sum(Quantity)
  148. FROM theTable
  149. WHERE SessionID=<id> or MemberID=<id>
  150. GROUP BY ProductID
  151.  
  152. DECLARE @Temp1 TABLE
  153. (
  154. --datafields same AS your table
  155.  
  156. )
  157.  
  158. DECLARE @Temp2 TABLE
  159. (
  160. --datafields same AS your table
  161. )
  162.  
  163. INSERT INTO @Temp1
  164. SELECT ID,
  165. ProductID,
  166. MemberId,
  167. SessionId,
  168. CreatedOn,
  169. Quantity
  170. FROM theTable
  171. WHERE SessionID= @SessionID or MemberID= @MemberID
  172.  
  173. UPDATE a
  174. SET SessionID = @SessionID , MemberID= @MemberID
  175. FROM @Temp1 AS a
  176. --WHERE SessionID= @SessionID OR MemberID= @MemberID
  177.  
  178. INSERT INTO @Temp2
  179. SELECT MAX(ID),
  180. ProductID,
  181. max(MemberId),
  182. MAX(SessionId),
  183. max(CreatedOn),
  184. sum(Quantity)
  185. FROM @Temp1
  186. GROUP BY ProductID
  187.  
  188. UPDATE a
  189. SET a.MemberId = b.MemberId ,a.SessionId=b.SessionId ,a.CreatedOn=b.CreatedOn,a.Quantity=b.Quantity
  190. FROM YourTable AS a
  191. INNER JOIN @Temp2 AS b
  192. ON a.ID=b.ID
  193.  
  194. DELETE FROM YourTable
  195. WHERE ID IN ( SELECT ID
  196. FROM @Temp1 AS ta
  197. WHERE NOT EXISTS ( SELECT 1
  198. FROM @Temp2 AS tb
  199. WHERE ta.ID = tb.ID ) )
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!