Guest User

Untitled

a guest
Jul 18th, 2012
39
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.72 KB | None | 0 0
  1. Updating a column with the earliest date which has the same identifier, and has a column > 0
  2. ID | SecondID | DateRecorded | NumberToCheck | NumberToUpdate
  3.  
  4. UPDATE dbo.Table
  5. SET NumberToUpdate =//Update NumberToUpdate
  6. NumberToCheck - //NumberToCheck from the current row, subtracting...
  7. (SELECT TOP 1 t2.NumberToCheck FROM Table t2 WHERE ID = t2.ID AND secondID = t2.secondID AND t2.DateRecorded =
  8. (SELECT TOP 1 MIN(t3.DateRecorded) FROM Table t3 WHERE t2.ID = t3.ID AND t2.secondID = t3.secondID AND t3.Passes > 0))
  9. //the NumberToCheck with the earliest date, of the same ID.
  10.  
  11. select t.*
  12. from (select t.*, row_number() over (partition by id order by date) as seqnum
  13. from table t
  14. where number_to_check > 0
  15. ) t
  16. where seqnum = 1
  17.  
  18. with vals as (select t.*
  19. from (select t.*, row_number() over (partition by id order by date) as seqnum
  20. from table t
  21. where NumberToCheck > 0
  22. ) t
  23. where seqnum = 1
  24. )
  25. update table
  26. set NumberToUpdate = NumberToCheck - vals.NumberToCheck
  27. from vals
  28. where t.id = vals.id
  29.  
  30. UPDATE dbo.Table as t1
  31. INNER JOIN
  32. (
  33. SELECT MIN(DateRecord) as mindate, ID
  34. FROM dbo.Table
  35. GROUP BY ID
  36. ) as mindates
  37. ON t1.ID = mindates.ID
  38. INNER JOIN
  39. dbo.Table as substractions
  40. ON mindates.mindate = substraction.DateRecord
  41. AND mindates.ID = substraction.ID
  42. SET t1.numberToUpdate = t1.numberToCheck - substractions.NumberToCheck
  43. WHERE substraction.NumberToCheck > 0
  44. --your questions is ambigious here: did you mean this one should be greater than 0, or should t1.NumberToCheck > 0 ?
  45. AND t1.DateRecord <> mindates.mindate
  46. --Assuming that you don't want to update the record with the lowest date.
Advertisement
Add Comment
Please, Sign In to add comment