[MS SQL] Hoogste datum uit kwartaal

[PsyBelgium]

Ik ben al een tijdje aan het worstelen met een query. Deze moet een item zijn laatst gevonden attribute-cost voor elk beschikbaar kwartaal tonen.

De tabel ziet er als volgt uit:

id	attribute	cost	entryDate
1	blaat 1	10	2001-03-11
1	blaat 1	15	2002-01-11
1	blaat 1	20	2002-01-12
1	blaat 2	5	2002-01-12
1	blaat 2	100	2002-01-30
1	blaat 2	1	2001-01-11
2	blaat 1	60	2001-03-11

En de gewenste ouput is dan als volgt voor item met id 1:

id	attribute	cost	quarterDate
1	blaat 1	10	2001Q1
1	blaat 2	1	2001Q1
1	blaat 1	20	2002Q1
1	blaat 2	100	2002Q1

Ik groepeer nu op jaar en kwartaal en join met het hoogst gevonden resultaat.

SQL:

SELECT id, attribute, cost, entryDate, b.Yr, b.Qtr
FROM items AS a
JOIN (
    SELECT Yr = DATEPART(YEAR, entryDate),
    Qtr = DATEPART(QUARTER, entryDate),
    MAX(entryDate) AS lastDate
    FROM items
    WHERE id = 1
    GROUP BY DATEPART(YEAR, entryDate),
    DATEPART(QUARTER, entryDate)
) AS b
ON a.entryDate = b.lastDate
WHERE id = 1

Helaas geeft dit een ongewenst resultaat:

id	attribute	cost	quarterDate
1	blaat 1	10	2001Q1
1	blaat 2	100	2002Q1

Waar ga ik in de fout met men query? Kan iemand me in de juiste richting wijzen?

[whoami]

Ik zou een UDF maken die als input een datum krijgt, en het kwartaal returned waarin deze datum zich bevind.
Ik denk dat je op die manier makkelijk een select kunt maken waarmee je je gewenste output kunt bekomen.

[KopjeThee]

Moet je niet "attribute" meenemen?

SQL:

SELECT id, attribute, cost, entryDate, b.Yr, b.Qtr
FROM items AS a
JOIN (
    SELECT Yr = DATEPART(YEAR, entryDate),
    Qtr = DATEPART(QUARTER, entryDate),
    MAX(entryDate) AS lastDate,
        attribute
    FROM items
    WHERE id = 1
    GROUP BY DATEPART(YEAR, entryDate),
    DATEPART(QUARTER, entryDate),
        attribute
) AS b
ON a.entryDate = b.lastDate and a.attribute = b.attribute
WHERE id = 1

[PsyBelgium]

whoami schreef op vrijdag 21 maart 2008 @ 20:29:
Ik zou een UDF maken die als input een datum krijgt, en het kwartaal returned waarin deze datum zich bevind.
Ik denk dat je op die manier makkelijk een select kunt maken waarmee je je gewenste output kunt bekomen.

Bedankt, je tip heeft geholpen!

Men UDF ziet er als volgt uit:

SQL:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

ALTER  FUNCTION [dbo].[getQuarterDate] (@pInputDate DATETIME)
RETURNS CHAR(7)
BEGIN
    DECLARE @vOutputDate CHAR(7)
    
    SET @vOutputDate =
    CAST(DATEPART(YEAR, @pInputDate) AS CHAR(4)) +
    'Q' +
    CAST(DATEPART(QUARTER, @pInputDate) AS CHAR(1))
    
    RETURN @vOutputDate
END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

En met de onderstaande query krijg ik dan men gewenste output:

SQL:

SELECT a.id, a.attribute, a.cost, b.quarterDate
FROM items AS a
JOIN (
    SELECT id, attribute, MAX(entryDate) AS 'lastDate',
    dbo.getQuarterDate(entryDate) AS 'quarterDate'
    FROM items 
    WHERE id = 1
    GROUP BY id, attribute,
    dbo.getQuarterDate(entryDate)
) AS b
ON a.entryDate = b.lastDate AND a.id = b.id AND a.attribute = b .attribute
WHERE a.id = 1
ORDER BY a.id, a.attribute, b.quarterDate

KopjeThee schreef op zaterdag 22 maart 2008 @ 14:34:
Moet je niet "attribute" meenemen?

SQL:

SELECT id, attribute, cost, entryDate, b.Yr, b.Qtr
FROM items AS a
JOIN (
    SELECT Yr = DATEPART(YEAR, entryDate),
    Qtr = DATEPART(QUARTER, entryDate),
    MAX(entryDate) AS lastDate,
        attribute
    FROM items
    WHERE id = 1
    GROUP BY DATEPART(YEAR, entryDate),
    DATEPART(QUARTER, entryDate),
        attribute
) AS b
ON a.entryDate = b.lastDate and a.attribute = b.attribute
WHERE id = 1

Inderdaad je hebt gelijk, had ik je post maar iets eerder gelezen
Er moet wel een kleine wijziging gebeuren bij je SELECT (ambigous)

SQL:

SELECT id, b.attribute, cost, entryDate, b.Yr, b.Qtr

Al moet ik wel zeggen dat de UDF methode verkies omdat deze de leesbaarheid van de query iets bevorderd

[Verwijderd]

Leesbaarheid van queries is te bevorderen door comments in je source, UDF's zijn voor de client in feite black boxes waar je tegenaan praat.
Als 't goed en performant kan zonder UDF's zou ik daar altijd voor kiezen...