[SQL] Selecteer eerst vorige datum

[Liqued]

Ik heb een tabel die vergelijkbaar is met de hier onderstaande.

id	klantId	datumtijd
1	15	15-12-2010 10:15
2	1122	15-12-2010 10:20
3	15	15-12-2010 10:17

Mijn uitdaging is om een query te bouwen waarbij er een extra kolom aan het einde wordt toegevoegd met daarbij het verschil tussen de de datumtijd uit de "huidige" rij en de eerst vorige datumtijd met dezelfde klantId (let hierbij op dat kolom datumtijd niet gesorteerd staat).

Nu ben ik al een tijdje aan het prutsen, maar ik kom er niet goed uit

[Big Womly]

Met welke database werk je?
Indien Oracle kan je via de window functions Lead() en Lag() werken:

SQL:

select id, klantid, datumtijd, lag(datumtijd) over (partition by klantid order by datumtijd)
from table
order by klantid, datumtijd

edit:

Zie nu in de tags dat het om access 2010 gaat...
Misschien dat er een equivalent bestaat.

[ Voor 12% gewijzigd door Big Womly op 28-11-2011 15:06 ]

[RobIII]

Waarom een query en niet gewoon even een scriptje flansen in 3 minuten En laat eens zien dan wat je geprobeerd hebt?

Met een query alleen ga je er niet komen; misschien met een UDF maar het hele nut van dit in een query willen oplossen ontgaat me...

[borft]

<knip>

nevermind ik moet leren lezen.

[ Voor 96% gewijzigd door borft op 28-11-2011 15:10 ]

[RobIII]

borft schreef op maandag 28 november 2011 @ 15:08:

SQL:

update table t1 
set t1.previous=t2.date
inner join table t2 on t2.i1 = (t1.id+1)

Leuk als je id's opeenvolgend zijn én de id's opeenvolgende datums bevatten; beiden zijn zelden het geval en nr. 2 geeft TS zelf al expliciet aan...

[Liqued]

@RobIII: ik ben in eerste instantie bezig geweest met de data in Spotfire (is een analyse tool en zit een kleine SQL-server achter). Daar kwam ik op de volgende query: [DatumTijd] - max([DatumTijd]) OVER (Intersect(Previous(AllPrevious([DatumTijd])),[klantId]))

Dit werkt alleen echt veeeel te traag (gaat om 300k records en verwerkt op deze wijze +/- 1 per seconde. Ik probeer dit dus wat handiger te doen en probeer het in access.

[ Voor 2% gewijzigd door Liqued op 28-11-2011 15:17 . Reden: typo ]

[Verwijderd]

code:

update table jetabel a
set prev_dt = (
  Select datumtijd
    From jetabel b
  Where b.klantId = a.klantId
      and (
         Select count(*)
           From jetabel c
         Where c.klantId = b.klantId
            And c.datumtijd > b.datumtijd) = 1

Zoiets dergelijks. Met de juiste indices moet dit aardig vlot gaan, al is het vrij lelijk nog

[CaVeFiSh]

Mijn oplossing voor dit probleem in MSSQL:

code:

--SELECT 1 AS [ID], GETDATE() AS [DT]
--INTO #Test
--UNION SELECT 1,DATEADD(DAY,-2,GETDATE())
--UNION SELECT 1,DATEADD(DAY,-3,GETDATE())

SELECT X.[ID],X.[DT],Z.[DT] AS [Vorige Datum]
FROM (
SELECT T1.[ID]
            ,T1.[DT]
            ,ROW_NUMBER() OVER (PARTITION BY T1.[ID] ORDER BY T1.[DT] DESC) AS [Row]
FROM #Test T1
         ) X
OUTER APPLY (SELECT Y.[ID],Y.[DT],Y.[Row]
                         FROM (SELECT T2.[ID]
                                                    ,T2.[DT]
                                                    ,ROW_NUMBER() OVER (PARTITION BY T2.[ID] ORDER BY T2.[DT] DESC) AS [Row]
                                        FROM #Test T2
                                        WHERE T2.[ID] = X.[ID]
                        ) Y
                        WHERE Y.[Row] = X.[Row] + 1
            )   Z

[pedorus]

Liqued schreef op maandag 28 november 2011 @ 14:56:
(let hierbij op dat kolom datumtijd niet gesorteerd staat).

Is er wel een sortering als je het per klantid bekijkt of ook niet? Zo ja, dan zou een simpele self-join, uitsluiting over over datumtijd, en max(orders2.datumtijd) volstaan. Zo nee, dan neem ik aan dat je sortering over id bedoelt. Met last(datumtijd) zou dat nog steeds simpel kunnen, als het niet erg is dat je ongedefinieerd gedrag aanroept, en anders gebruik je een van de vele groupwise-max oplossingen waar je niet vrolijk van wordt..

[GlowMouse]

Heeft Access geen user variables?

SQL:

SET @klant=-1;
SET @tijd=0;
UPDATE tabel SET verschil=IF(klant=@klant, -@tijd+(@tijd:=tijd), (@klant:=klant)*0) ORDER BY klant,tijd;

[curry684]

RobIII schreef op maandag 28 november 2011 @ 15:04:
Met een query alleen ga je er niet komen; misschien met een UDF maar het hele nut van dit in een query willen oplossen ontgaat me...

Kan makkelijk met een query, en is in bepaalde situaties best nuttig om even in batch te kunnen vullen hoor, gewoon gedenormaliseerde data.

SQL:

-- SQL Server sample
update KlantTijden set verschil = 
    case (select count(*) from KlantTijden kt 
                where kt.datumtijd < KlantTijden.datumtijd 
                    and kt.klantid = KlantTijden.klantid)
        when 0 then null
        else DATEDIFF(day, (select top 1 datumtijd from KlantTijden kt
                where kt.datumtijd < KlantTijden.datumtijd
                    and kt.klantid = KlantTijden.klantid order by datumtijd desc),
                datumtijd)
    end     

Schoonheidsprijzen zal het niet winnen, werken wel.

[ Voor 3% gewijzigd door curry684 op 28-11-2011 17:48 . Reden: leesbaarheid query ]

[Verwijderd]

Kan dit niet gewoon met een subquery?

SQL:

 SELECT TB1.id, TB1.KlantId, TB1.datumtijd, (SELECT MAX(TB2.datumtijd) FROM Tabel TB2 WHERE TB2.datumtijd < TB1.datumtijd AND TB2.klantId = TB1.klantId) AS VorigeDatum FROM Tabel TB1 INTO NieuweTabel 

Ik heb zoiets soortgelijks vanmiddag getest op een Access database en dat werkte. Ik had helaas niets anders voor handen.

edit: bijna klantId vergeten....

[ Voor 8% gewijzigd door Verwijderd op 28-11-2011 20:11 ]

[curry684]

Hmm wtf zie nu pas door jouw query hoe nodeloos complex mijn query straks was doordat ik op een andere manier was begonnen

Fixed:

SQL:

update KlantTijden set verschil =
    datediff(day, (select max(datumtijd) from KlantTijden kt
                where kt.datumtijd < KlantTijden.datumtijd
                    and kt.klantid = KlantTijden.klantid), datumtijd);

[Verwijderd]

curry684 schreef op maandag 28 november 2011 @ 20:50:
Hmm wtf zie nu pas door jouw query hoe nodeloos complex mijn query straks was doordat ik op een andere manier was begonnen

Fixed:

SQL:

update KlantTijden set verschil =
    datediff(day, (select max(datumtijd) from KlantTijden kt
                where kt.datumtijd < KlantTijden.datumtijd
                    and kt.klantid = KlantTijden.klantid), datumtijd);

LOL, en ik had het completer kunnen maken met datediff.

Overigens, voor de TS in Access is de syntax voor datediff: datediff('d', ipv datediff(day,

[ Voor 8% gewijzigd door Verwijderd op 28-11-2011 21:35 . Reden: annvullende opmerking ]

[pedorus]

Verwijderd schreef op maandag 28 november 2011 @ 19:56:
Kan dit niet gewoon met een subquery?

Dat kan prima, maar de performance van dat soort subqueries is nogal beroerd in access.

GlowMouse schreef op maandag 28 november 2011 @ 17:11:
Heeft Access geen user variables?

SQL:

SET @klant=-1;
SET @tijd=0;
UPDATE tabel SET verschil=IF(klant=@klant, -@tijd+(@tijd:=tijd), (@klant:=klant)*0) ORDER BY klant,tijd;

Helaas niet. Maar je zou dit wel prima met eigen VBA-functies kunnen doen, die wel gesupport zijn, en die je gewoon in een query kan gebruiken. Wat het handigst is hier hangt sterk af van de sortering en de eventuele subset van de query.

[Verwijderd]

Wat ik me nu ff afvraag is of de TS nu een oplossing heeft waar hij wat mee kan.....

[ Voor 1% gewijzigd door Verwijderd op 29-11-2011 21:30 . Reden: Typo ]

[RobIII]

curry684 schreef op maandag 28 november 2011 @ 17:46:
[...]



Kan makkelijk met een query

Laten we zeggen dat ik wat aannames deed over Access die, klaarblijkelijk, wat kort door de bocht waren
Ik wist bijvoorbeeld niet dat 't ding subqueries ondersteunt (of is dat iets nieuws?) <- Zit er al sinds St. Juttemis in

[ Voor 19% gewijzigd door RobIII op 29-11-2011 22:04 ]

[curry684]

Ik moet overigens bekennen dat ik de 'Access' ook niet had gespot bij de tags en in een offtopic comment Gelukkig staan er nog immer 8 jaar oude stickies van mij in Programming

[Killemov]

RobIII schreef op dinsdag 29 november 2011 @ 22:01:
[...]

Laten we zeggen dat ik wat aannames deed over Access die, klaarblijkelijk, wat kort door de bocht waren
Ik wist bijvoorbeeld niet dat 't ding subqueries ondersteunt (of is dat iets nieuws?) <- Zit er al sinds St. Juttemis in

Huh? Wat? Sint Juttemis? Wikipedia: Sint-juttemis