04. oktober 2009 - 11:26Der er
8 kommentarer og 1 løsning
find antal dage mellem 2 rækker med samme id
Hejsa jeg prøver at skrive ne sp der bla. skal finde antal dage mellem hver opdatering af en tabel, mere præcis en posterings tabel med konto nummer og posterings dato.
jeg har prøvet med cursor på følgende måde
declare @antaldage int declare @dato1 date ,@dato date declare pegefinger CURSOR for select kontonr from renteOpgorelse where kontonr = @kontonr open pegefinger
FETCH pegefinger into @kontonr
WHILE @@FETCH_STATUS = 0 BEGIN if not @dato is null set @dato = @dato1
set @dato1 = (select dato from renteOpgorelse where kontonr = @kontonr) set @antaldage = datediff(day,@dato,@dato1) print @antaldage
FETCH NEXT FROM pegefinger into @kontonr END --WHILE close pegefinger deallocate pegefinger
END GO
den compiler godt nok men når jeg vil kalde sp'en fejler den med
Msg 512, Level 16, State 1, Procedure tilskriv_rente, Line 23 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
fo mig virkder det lidt som om cursoren ikke fungere
I'd better say that I dont want to get too involved with your SP, I'm not too familier with SP's or cursors, but if I can help to point you in the right direct then great.
So you want to calculate the number of days between each row where the kontonr is the same and the dates are sorted ascending?
If thats what your after then in the sub select dont you need to select the row with the lowest date and which is > the current date?
I think i found another way araound it. I was looking for a slution that calculated the days between post in existing rows, but why not do it when i insert new rows into the table, then i should be possible to do
get max(dato) from the kontonr, calculated that with current date and then insert the row,
i just need to expand the table with the number of days column.,
anyway thonks for your effort, please make a answer and you get the point for this question,
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.