Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Updating a column with the earliest date which has the same identifier, and has a column > 0
- ID | SecondID | DateRecorded | NumberToCheck | NumberToUpdate
- UPDATE dbo.Table
- SET NumberToUpdate =//Update NumberToUpdate
- NumberToCheck - //NumberToCheck from the current row, subtracting...
- (SELECT TOP 1 t2.NumberToCheck FROM Table t2 WHERE ID = t2.ID AND secondID = t2.secondID AND t2.DateRecorded =
- (SELECT TOP 1 MIN(t3.DateRecorded) FROM Table t3 WHERE t2.ID = t3.ID AND t2.secondID = t3.secondID AND t3.Passes > 0))
- //the NumberToCheck with the earliest date, of the same ID.
- select t.*
- from (select t.*, row_number() over (partition by id order by date) as seqnum
- from table t
- where number_to_check > 0
- ) t
- where seqnum = 1
- with vals as (select t.*
- from (select t.*, row_number() over (partition by id order by date) as seqnum
- from table t
- where NumberToCheck > 0
- ) t
- where seqnum = 1
- )
- update table
- set NumberToUpdate = NumberToCheck - vals.NumberToCheck
- from vals
- where t.id = vals.id
- UPDATE dbo.Table as t1
- INNER JOIN
- (
- SELECT MIN(DateRecord) as mindate, ID
- FROM dbo.Table
- GROUP BY ID
- ) as mindates
- ON t1.ID = mindates.ID
- INNER JOIN
- dbo.Table as substractions
- ON mindates.mindate = substraction.DateRecord
- AND mindates.ID = substraction.ID
- SET t1.numberToUpdate = t1.numberToCheck - substractions.NumberToCheck
- WHERE substraction.NumberToCheck > 0
- --your questions is ambigious here: did you mean this one should be greater than 0, or should t1.NumberToCheck > 0 ?
- AND t1.DateRecord <> mindates.mindate
- --Assuming that you don't want to update the record with the lowest date.
Advertisement
Add Comment
Please, Sign In to add comment