[MSSQL] retourneren van row met laatste datum van die week

[Verwijderd]

Dames en heren,

ben ik weer met een vraagje: ik weet echter niet zeker of dat dit uberhaupt mogelijk is:


Ik ben bezig met het maken van een doorsnede waarin ik kan zien wat de status was van een item op een bepaalde week. Hier heb ik een slimme JOIN voor gekozen, waardoor ik alle statusveranderingen van voor of tijdens die week zie. in Query vorm:

Tables:
Ik heb twee tables:

YEAR_WEEK_TBL
Bevat simpelweg alleen maar jaar en week gegevens:
INDEX_YEAR,
INDEX_WEEK

ITEM_TBL
Bevat de aanpassingsinformatie en de bijbehorende status. Velden:
ITEM_ID,
EDIT_DATETIME,
EDIT_YEAR,
EDIT_WEEK,
STATUS

De EDIT_YEAR en EDIT_WEEK bevatten de jaar en week nummer die bij de EDIT_DATETIME hoort.

1e Query :

SQL:

CREATE VIEW 
    TMP_VIEW 
AS SELECT
            B.INDEX_YEAR,
            B.INDEX_WEEK,
            A.ITEM_ID,
            MAX(A.EDIT_DATETIME) as MAX_EDIT_DATETIME
FROM 
           ITEM_TBL A
JOIN
            YEAR_WEEK_TBL B
ON     
          A.EDIT_YEAR < B.INDEX_YEAR OR
          A.EDIT_WEEK = B.INDEX_YEAR  AND
          A.EDIT_WEEK <= B.INDEX_WEEK
GROUP BY 
            B.INDEX_YEAR,
            B.INDEX_WEEK,
            A.ITEM_ID

Dit geeft dus per week, per item de laatste EDIT_DATETIME. Tesamen met de ITEM_ID is de EDIT_DATETIME uniek , dus komt de tweede query er als volgt uit te zien:

2e query:

SQL:

SELECT 
            A.INDEX_YEAR,
            A.INDEX_WEEK,
            A.ITEM_ID, 
            B.STATUS
FROM 
           TMP_VIEW A
JOIN 
           ITEM_TBL B
ON 
           A.ITEM_ID = B.ITEM_ID AND 
           A.MAX_EDIT_DATETIME = B.ITEM_DATETIME

Gecombineerd geeft mij dit een lijst met per week alle items die op dat moment zijn aangepast en wat de laatste status binnen die week was. Op zich een toppertje maar mijn gevoel zegt me dat ik die tweede join niet nodig heb. Ik kan me voorstellen dat er een manier is om in de eerste query al de resultaten te beperken tot alleen de row waarin de max date staat, of om via een slimme CASE WHEN constructie die gegevens los te peuteren. Enigste nadeel is dat ik geen idee heb hoe ik het zou moeten aanpakken.

Kan iemand me hiermee op weg helpen? Alvast bedankt!

PS: Ik heb geprobeerd om de ene query in de andere te schuiven, maar om een of andere verdachte reden doet MSSQL dat niet: als ik de twee query's splits en dan uitvoer gaat het goed, maar combineer ik ze dan gaat het fout. Ik heb meerdere malen gechecked dus ik kan me niet voorstellen dat ik een typfout heb gemaakt. Het voorbeeld hier boven is nogal versimpeld: ik gebruik meerdere views om uiteindelijk tot dit geheel te komen. Kan het zijn dat het gebruik van views in views en herhaaldelijk gebruik van dezelfde view de query laat mislukken?

[ Voor 10% gewijzigd door Verwijderd op 11-01-2008 14:53 ]

[mocean]

Ik zou je database eens beter gaan normaliseren.
De tabel met week/jaar is behoorlijk triviaal. Voor elk jaar heb je vast gewoon 52 weken. En aangezien je in ITEM_TBL ook nog week & jaar opslaat, is YEAR_WEEK_TBL niet nodig.

Verder bevat EDIT_DATETIME ook de informatie WEEK/JAAR alleen moet je dat wel omrekenen.

Als je toch met YEAR_WEEK_TBL wil werken, zou ik er met een key naar verwijzen.

[Verwijderd]

mocean schreef op vrijdag 11 januari 2008 @ 15:02:
Ik zou je database eens beter gaan normaliseren.
De tabel met week/jaar is behoorlijk triviaal. Voor elk jaar heb je vast gewoon 52 weken. En aangezien je in ITEM_TBL ook nog week & jaar opslaat, is YEAR_WEEK_TBL niet nodig.

Dat klopt, maar ik heb niet van alle weken edits: de progress moet niet alleen worden getoond als er wel data is, maar ook als er niet data is. Als je een snelle manier weet om op basis van een willekeurige jaar/week lijst alle ontbrekende weken op te vullen met dezelfde gegevens van de voorgaande week, dan maak je me daar erg blij mee - dit is ook niet mijn favoriet.

mocean schreef op vrijdag 11 januari 2008 @ 15:02:
Verder bevat EDIT_DATETIME ook de informatie WEEK/JAAR alleen moet je dat wel omrekenen.
Als je toch met YEAR_WEEK_TBL wil werken, zou ik er met een key naar verwijzen.

Die verrekening zit er ook in, alleen om het voorbeeld hier simpel te houden heb ik die maar even als losse definitie te houden: ik wil niet hebben dat mensen commentaar geven over DATENAME terwijl dat helemaal niet mijn vraag is

[Verwijderd]

offtopic:
Sinds wanneer heeft een jaar vast 52 weken?
De opmerking over de key-verwijziging is natuurlijk terecht, dat zou je inderdaad moeten aanpassen. Het gebruik van een tabel met datums/weken/maanden/perioden/jaren/boekjaren etc is helemaal niet raar of slecht.

Over je vraag, ja moet altijd met een query de hoogste datum opzoeken en daarmee weer terugzoeken naar je rij. Dit kan helaas niet tegelijkertijd, als in "SELECT MAX(date), MAX(id)", want dat betekent iets heel anders.

Over je vraag over het samenvoegen van de view in je query: je moet even zoeken naar de term "derived tables" (google ofzo). Vind je vast een betere uitleg dan ik hier wil overtypen

[Verwijderd]

Bedankt voor de reactie, ik zal es wat gaan googlen: meer heb ik niet nodig.

Wel even ingaande op jouw (eigenlijk) off-topic reactie : hoezo zou een vaste waarde van 52 weken voor een jaar verkeerd zijn? Schrikkeljaar heeft 366 dagen. delen door 7 , kom ik uit op 52.2xxxxx weken: dat betekent dus dat in welk geval ook, je volgens mij nooit een week 53 kunt krijgen?

Wat ik me wel kan voorstellen is dat je verwijst naar de vraag: wat beschouwd men als eerste week van het jaar? Ik gebruik DATENAME om de conversie naar weeknummers te maken, kan die concluderen dat er een week 53 is?

[TeeDee]

Op deze manier rekenen met datum/tijd is altijd tricky. 29 December 2008 valt in week 1. Hoe ga je dat aanpakken?

[RobIII]

Verwijderd schreef op woensdag 16 januari 2008 @ 10:25:
Wel even ingaande op jouw (eigenlijk) off-topic reactie : hoezo zou een vaste waarde van 52 weken voor een jaar verkeerd zijn? Schrikkeljaar heeft 366 dagen. delen door 7 , kom ik uit op 52.2xxxxx weken: dat betekent dus dat in welk geval ook, je volgens mij nooit een week 53 kunt krijgen?

Wat ik me wel kan voorstellen is dat je verwijst naar de vraag: wat beschouwd men als eerste week van het jaar? Ik gebruik DATENAME om de conversie naar weeknummers te maken, kan die concluderen dat er een week 53 is?

http://en.wikipedia.org/wiki/Week#Week_number

[ Voor 5% gewijzigd door RobIII op 16-01-2008 10:49 ]

[curry684]

Verwijderd schreef op woensdag 16 januari 2008 @ 10:25:
Wel even ingaande op jouw (eigenlijk) off-topic reactie : hoezo zou een vaste waarde van 52 weken voor een jaar verkeerd zijn? Schrikkeljaar heeft 366 dagen. delen door 7 , kom ik uit op 52.2xxxxx weken: dat betekent dus dat in welk geval ook, je volgens mij nooit een week 53 kunt krijgen?

Is 52.2xxxx niet het gemiddelde van een aantal jaren met 52 weken en een kleiner aantal jaren met 53 weken dan?