Hente data pr måned bakover i tid

[RulleRimfrost]

Hei, jeg har en liten påskenøtt her. Jeg skal avstemme balansekontoer, og i den forbindelse summere periodedata i følgende struktur:

2012.01.01 | sum(beløp)

2011.12.01 | sum(beløp)

2011.11.01 | sum(beløp)

osv.... for hver aktive transaksjon, pr den 1. i hver mnd 10-15 år bakover i tid.

Tabellene ligger i sql server 2005 med følgende data : registreringsdato, sluttdato, beløp

Jeg får en periode med:

 

declare @c_date varchar(10);
set @c_date = '2012.01.01';

select @c_date, sum(beløp)
from database
where registreringsdato < @c_date
and (sluttdato >= @c_date or sluttdato is null)

 

 

Hvordan kan jeg få en query til å frese gjennom datoene bakover i tid? For-each løkke faller i hu, uten at jeg helt ser den.

[laserlars]

Hvorfor ikke gjøre det lett for deg selv å konvertere datoformatet til f.eks YYYY-MM (uten spesifikk dato) i spørringen, deretter bruke group by <dato>

 

Da vil du få f.eks

2011-01 | sum(beløp)

2011-02 | sum(beløp)

2011-03 | sum(beløp)

 

Og jeg ser at du har dato som varchar, jeg ville heller brukt datetime i tabellen. MYE mer fleksibelt.

Endret 15. februar 2012 av laserlars

[RulleRimfrost]

ja, jo, men den strekker seg bare 12 mnd. 15 år gir 180 mnd. Jeg sitter nå å funderer på en løkke som baserer seg på følgende sudokode:

 

for each 1 to 180

set @step = @step - 1

set @c_date = dateadd(month, @step, @c_date)

 

Får se om dette går i SQL server, kanskje med en while-løkke.

 

Edit: ser nå hva du mener med gruppering uten dag. Problemet er at et beløp skal summeres for flere mnd, kanskje år. Tror det må en loop til her.

Endret 15. februar 2012 av RulleRimfrost

[laserlars]

Hørtes veldig tungvindt ut, men nå vet jo ikke jeg alle detaljer om hva du skal ha ut av spørringen. JEG ville iallefall fått ut mnd for mnd først, og det kan man fint gjøre på måten jeg sa med en plain spørring helt uten løkker, variabler eller annet tullball

[RulleRimfrost]

Om man vil ha det enkelt setter man ikke en økonom til å kode

Men, en balansekonto har aktiva som er aktive over flere perioder. Om startdato er 01.01.2011 og sluttdato er 01.12.2012 skal denne raden være med i summen 12 ganger. Derfor blir gruppering vanskelig. Setter inn testkoden, så får jeg prøve å huske å oppdatere når jeg løser det. De 3 radene her skal altså gi meg 12 linjer i resultatet...

declare @tempTest table (
regdato datetime, sluttdato datetime, trans money);

insert into @tempTest (regdato, sluttdato, trans) values
('2011.01.01', '2011.12.31', 1000)
insert into @tempTest (regdato, sluttdato, trans) values
('2011.02.10', '2011.04.01', 2000)
insert into @tempTest (regdato, sluttdato, trans) values
('2011.06.20', '2011.11.01', 5000)

declare @step int;
declare @c_date datetime;
declare @tempResult table (
periode datetime, trans money);

set @step = 0
set @c_date = '2012.02.01'
while @step > -12
begin
set @step = @step - 1
set @c_date = dateadd(month, @step, @c_date)
insert into @tempResult(periode, trans)
select @c_date, sum(trans)
from @tempTest
where regdato < @c_date and sluttdato > @c_date
end;

select * from @tempResult

 

[Gjest Slettet-Pqy3rC]

Om startdato er 01.01.2011 og sluttdato er 01.12.2012 skal denne raden være med i summen 12 ganger.

group på DATEPART().

[RulleRimfrost]

Problemet med group er at en transaksjon med start 01.01.2011 og slutt 01.03.2011 ikke vil summeres den 01.02.2011, selv om den skal det.

Jeg har nå en funksjonibel kjøring, men med tabeller på 12mill rader, og databaser på 30GB spørs det hvordan dette går...

declare @tempTest table (
regdato datetime, sluttdato datetime, trans money);

insert into @tempTest (regdato, sluttdato, trans) values
('2011.01.01', '2011.12.31', 1000)
insert into @tempTest (regdato, sluttdato, trans) values
('2011.02.10', '2011.04.01', 2000)
insert into @tempTest (regdato, sluttdato, trans) values
('2011.06.20', '2011.11.01', 5000)

declare @step int;
declare @c_date datetime;
declare @tempResult table (
periode datetime, trans money);

set @step = -1
set @c_date = '2012.01.01'
while @step > -13
begin
set @c_date = dateadd(month, @step, '2012.01.01')
insert into @tempResult(periode, trans)
select @c_date, sum(trans)
from @tempTest
where regdato <= @c_date and sluttdato >= @c_date
set @step = @step - 1
end;

select * from @tempResult