Gruppér priser efter periode fra - til

Can you explain why
01  02-05-15  09-05-15  100
isn't included in
01  04-04-15  18-04-15  100

EG:
01  04-04-15  09-05-15  100

?

Maybe because periods don't overlap?

Don't think you can do it in SQL .. I guess you will need to use a temp table and vba to find required periods.

"Maybe because periods don't overlap?"
Which is what gives the problem.

Hi Terry

My source extract have 52 weeks ( saturday to saturday ) per ID, with a price ( bed-nights ) for each week no. Some of these proces are identical in periods of linked week no.

I want to reduce my output to show the start and end of each linked period with the price attached (by ID )

And when you say linked period, you mean overlapping, IE last day of one week is same date as start date for next period, which can continue.

I'm very sure you would need to use code and a temp table.

I'm a bit pushed for time, but if I find a bit of free then I'll try and putt something together

Hvis vi udvider data lidt

Data består af

ID  Start    slut      pris
01  04-04-15  11-04-15  100
01  11-04-15  18-04-15  100
01  18-04-15  25-04-15  200
01  25-04-15  02-05-15  200
01  02-05-15  09-05-15  200
01  09-05-15  16-05-15  100


Så fanger vi også tilfældet: 3 poster i en periode

Pseudo:
for hver post
    hvis gammel periode er initialiseret
        hvis beløbet ændret
            udskriv poster fra gammel periode
            lav nuværende post til start og slut på gammel periode
        ellers
            udvid gammel periode til at indbefatte nuværende post
    ellers
        initialiser gammel periode
udskriv gammel periode

Lidt om omkontrolleret:

Sub testit()
    Dim start, slut, bel
    With CurrentDb.OpenRecordset("select start,slut,bel from Weeks where id=1 order by start")
        While Not .eof
            If Not IsEmpty(bel) Then
                If !bel <> bel Then
                    Debug.Print start, slut, bel
                    start = !start: End If
            Else
                start = !start: End If
            slut = !slut: bel = !bel
            .MoveNext
        Wend: End With
    Debug.Print start, slut, bel
End Sub

Ikke mere aktuelt

Lukker