[SQL Server] select query help

[Verwijderd]

Mijn tabel heeft een aantal colommen waarvan de volgende twee voor mijn probleem belangrijk zijn. Deze twee colommen zijn TicketDate /datetime/ en Balance /decimal (18,2)/. Onderstaand is een voorbeeld van de gegevens in mijn tabel. Mijn doel is voor iedere datum de rij met de laatste tijdmarkering op te halen. Van onderstaande gegevens wil ik dus de TicketDate en Balance voor de rijen aangeduid met een • ophalen.

code:

3/27/2007 4:00:00 PM, 408.52 [*]
3/28/2007 11:01:14 AM, 408.52
3/28/2007 11:03:49 AM, 408.52
3/28/2007 4:00:00 PM, 408.56
3/28/2007 4:00:00 PM, 408.55
3/28/2007 4:00:00 PM, 408.50
3/28/2007 8:10:43 PM, 401.55 [*]
3/29/2007 4:00:00 PM, 401.59
3/29/2007 4:00:00 PM, 401.54
3/29/2007 5:46:02 PM, 401.61 [*]

Het ophalen van een resultaat voor een specifieke datum lukt prima, ik kom er echter niet uit wat voor SQL Query ik moet gebruiken om voor elke datum in mijn tabel de rij met de laatste tijd te selecteren.

C#:

            ArrayList datetime = new ArrayList();
            ArrayList balance = new ArrayList();

            //Query to execute.
            SqlCommand cmd = new SqlCommand("SELECT TOP 1 TicketDate, Balance FROM Data WHERE TicketDate >= '20070329' AND TicketDate < '20070330' ORDER BY TicketDate DESC", con);
            
            //Open the connection.
            con.Open();

            //Execute reader.
            SqlDataReader rdr = cmd.ExecuteReader();

            //Process the results.
            while (rdr.Read())
            {
                datetime.Add(rdr[0]);
                balance.Add(rdr[1]);
            }

            if (con != null)
                con.Close();

            if (rdr != null)
                rdr.Close();

            datetime.TrimToSize();
            balance.TrimToSize();

[whoami]

Tja, met de code die je nu toont lukt het natuurlijk niet. Nu voeg je gewoon alles toe.
Je kan eens kijken naar de mogelijkheden die SQL je biedt mbt Aggregate methods (MIN() bv), en dan in combinatie met een subquery de gewenste gegevens ophalen.

Anders zal je gewoon alle rijen moeten ophalen, gesorteerd op datum en op tijd en dan, zolang de datum hetzelfde is, de tijden vergelijken, en iedere keer je getalletje bijhouden.
Zoiets (pseudo)

code:

select * from tabel order by datum, tijd
while( !eof )
{

    date currentDate = currentRecord.Datum;
    while( !eof && currentDate == currentRecord.Datum )
    {
        getal = currentRecord.Getal;
        currentRecord = readnext;
    }

    print "laatste getal voor huidige datum: " + getal;
}

Wellicht dat je het met sql ook zo voor elkaar kunt krijgen.

code:

select distinct 1 datum, tijd,  ( select top 1 tijd, getal from tabel x where x.datum = tabel.datum order by tijd desc )
from tabel

[purge]

Je kan gebruik maken van een sub-query waarbij je de laatste tijd selecteerd per dag. Wat doe je als de tijden exact gelijk zijn? Hieronder twee verschillende manieren om vervolgens je gegevens te selecteren waarbij ik de performance niet heb bekeken. Ik heb ergens een constructie gezien om het zonder sub-query te doen maar ik kon daarvan even geen bron vinden.

SQL:

SELECT     TicketDate, Balance
FROM         TEST_Tickets
WHERE     (TicketDate IN
                          (SELECT     MAX(DISTINCT TicketDate) AS MaxTicketDate
                            FROM          TEST_Tickets AS LastTicketsByDay
                            GROUP BY CONVERT(varchar(8), TicketDate, 112)))

SQL:

SELECT     TEST_Tickets.TicketDate, TEST_Tickets.Balance
FROM         TEST_Tickets INNER JOIN
                          (SELECT     MAX(DISTINCT TicketDate) AS MaxTicketDate
                            FROM          TEST_Tickets
                            GROUP BY CONVERT(varchar(8), TicketDate, 112)) AS LastTicketsByDay ON TEST_Tickets.TicketDate= LastTicketsByDay .MaxTicketDate

edit:

Dit principe kan je prima gebruiken met een veld ID, in plaats van TicketDate.

[ Voor 4% gewijzigd door purge op 04-07-2007 19:00 ]

[Verwijderd]

Het probleem van twee rijen met dezelfde tijd is een goede vraag. Daarom kan ik mijn vraag het beste iets anders formuleren. Elke rij heeft ook een uniek ID, waarbij een rij met een latere datetime ook altijd een hoger ID heeft dan een rij met een eerdere datetime.

Colommen: ID, TicketDate, Balance.

code:

1, 3/27/2007 4:00:00 PM, 408.52 [*]
2, 3/28/2007 11:01:14 AM, 408.52
3, 3/28/2007 11:03:49 AM, 408.52
4, 3/28/2007 4:00:00 PM, 408.56
5, 3/28/2007 4:00:00 PM, 408.55
6, 3/28/2007 4:00:00 PM, 408.50
7, 3/28/2007 8:10:43 PM, 401.55 [*]
8, 3/29/2007 4:00:00 PM, 401.59
9, 3/29/2007 4:00:00 PM, 401.54
10, 3/29/2007 5:46:02 PM, 401.61 [*]

De select query kan dan iets zijn als: Voor elke datum in de tabel, selecteer het record met het hoogste ID. Bijvoorbeeld voor de 29e zijn er drie records, waarvan 10 het hoogste ID is --> dus selecteer uit de rij met ID 10 de TicketDate en Balance. En dan natuurlijk voor elke datum in de tabel.

Ik zal jullie opmerkingen gaan bestuderen, mijn kennis van SQL is echter nog niet zo groot.

[P.O. Box]

kijk ook eens naar "HAVING"

[Niemand_Anders]

Edwardvb schreef op donderdag 05 juli 2007 @ 09:23:
kijk ook eens naar "HAVING"

En wat moet de TS dan met having gaan doen? Having is een where clausule op de group by. Echter de TS heeft alleen de laatste datum/tijd notering op de datum nodig, en die kan dus zeer eenvoudig met max() worden opgehaald zoals purge aangaf.

Denk je dat het met having beter kan, geef dan zou ik graag eens een voorbeeld willen zien. De group by wordt namelijk gedaan op TicketDate dus het enigste wat je met having kunt filteren is het datum bereik waarop de group by wordt uitgevoerd.

[P.O. Box]

je hebt gelijk... heb te snel gedacht... excuses...

[Verwijderd]

De SQL queries zijn redelijk duidelijk, ik krijg echter nog een error bij het uitvoeren. Niet op lijn 11, maar op lijn 14 (zie startpost). De error is:

Arithmetic overflow error converting expression to data type datetime.

Dis is mijn sql. Data is de table en Ticket is het ID.

SQL:

                SELECT TicketDate, Balance
                FROM Data 
                WHERE (TicketDate IN
                         (SELECT MAX(DISTINCT Ticket) AS MaxTicketDate
                         FROM Data AS LastTicketsByDay 
                         GROUP BY CONVERT(varchar(8), TicketDate, 112)))

Ik ben alles een beetje nagegaan, maar kan niet echt localiseren waar de error mee van doen heeft. Het Convert gedeelte was mijn eerste ingeving, maar die convert niet naar datetime, maar juist van datetime. Ik heb nog geprobeert met andere styles te werken dan de 112 ISO standardiseren, maar die geven dezelfde error.

[Mental]

Edit2:
Lezen is ook een kunst, sql is natuurlijk het probleem aangezien het bij een single result wel werkt.
Kun je eens kijken of je met de nieuwe query dezelfde data van je sql server krijgt als met een enkel resultaat? (door hem bijv op 1 result te limiten).

probeer op lijn14 eens van rdr.Read() dit te maken: rdr.Read() != null.
Als ik de error vertaal naar boeren nederlands loopt je compiler te miepen dat rdr.Read() geen geldige expressie is.


edit:

na nog eens gekeken te hebben (ik moet er bij zeggen dat ik niet regelmatig in .net code) zie ik dat je rdr tegelijkertijd als SqlReader gebruikt en ook als array, kan dat uberhaubt wel?
Mij lijkt het volgende iets logischer:
[code]while(var temp = rdr.Read()) {
datetime.Add(temp[0]);
}
[/code]
_{let niet op de syntax, ben php / javascript gewend.}
Maar het kan aan mij liggen

[ Voor 71% gewijzigd door Mental op 05-07-2007 20:29 ]

[Verwijderd]

Ik heb het al gevonden. Een stomme fout. De subquery returnt integers. Het moet dus zijn WHERE (Ticket ... ipv WHERE (TicketDate. Het werkt iig perfect. Bedankt, vooral Purge voor de mooie query.

SQL:

                SELECT TicketDate, Balance 
                FROM Data  
                WHERE (Ticket IN 
                         (SELECT MAX(DISTINCT Ticket) AS MaxTicketDate 
                         FROM Data AS LastTicketsByDay  
                         GROUP BY CONVERT(varchar(8), TicketDate, 112)))

Misschien nog een kleine andere vraag. Purge had het over performance. Ik ben benieuwd hoe je queries op performance test. Gewoon willekeurige implementaties uitproberen en kijken hoelang het duurt om ze uit te voeren, of moet ik dit anders aanpakken?

[tss68nl]

Makkelijkste methode is inderdaad in de query analyzer en een zo grote set opvragen dat ie ook substantieel wat tijd nodig heeft om de resultaten op te leveren. Ook kan je in de query analyzer het execution/query plan opvragen. Let er daarbij wel op dat in sql 2000 het vooraf berekende queryplan vaak nergens op slaat.

[Verwijderd]

Ik heb de query aangepast om soortgelijke resultaten te bereiken op week en maandniveau. De maandquery is makkelijk aangezien door het veranderen van de 8 in een 6 de dd bij het converten verloren gaat. De week query was iets meer werk, maar ik ook gelukt. Bij het debuggen zie ik echter dat de week loop van zondag tot zaterdag (rare conventie). Hoe kan ik het begin van de week op maandag zetten?

SQL:

                SELECT TicketDate, Balance
                FROM Data
                WHERE (Ticket IN 
                         (SELECT MAX(DISTINCT Ticket) AS MaxTicketDate 
                         FROM Data AS LastTicketsByDay 
                         GROUP BY DATEPART(week, TicketDate), DATEPART(year, TicketDate)))

[MMUilwijk]

Verwijderd schreef op donderdag 05 juli 2007 @ 21:15:
Ik heb het al gevonden. Een stomme fout. De subquery returnt integers. Het moet dus zijn WHERE (Ticket ... ipv WHERE (TicketDate. Het werkt iig perfect. Bedankt, vooral Purge voor de mooie query.

SQL:

                SELECT TicketDate, Balance 
                FROM Data  
                WHERE (Ticket IN 
                         (SELECT MAX(DISTINCT Ticket) AS MaxTicketDate 
                         FROM Data AS LastTicketsByDay  
                         GROUP BY CONVERT(varchar(8), TicketDate, 112)))

Misschien nog een kleine andere vraag. Purge had het over performance. Ik ben benieuwd hoe je queries op performance test. Gewoon willekeurige implementaties uitproberen en kijken hoelang het duurt om ze uit te voeren, of moet ik dit anders aanpakken?

Je kan voor de performance idd het executionplan bekijken, daarin vind je precies per actie van de SQL Server de performance terug. De query analyser is leuk om mee te beginnen, en wellicht kan je daarna nog de SQL Server profiler gebruiken.

[tss68nl]

Verwijderd schreef op vrijdag 06 juli 2007 @ 18:52:
Ik heb de query aangepast om soortgelijke resultaten te bereiken op week en maandniveau. De maandquery is makkelijk aangezien door het veranderen van de 8 in een 6 de dd bij het converten verloren gaat. De week query was iets meer werk, maar ik ook gelukt. Bij het debuggen zie ik echter dat de week loop van zondag tot zaterdag (rare conventie). Hoe kan ik het begin van de week op maandag zetten?

Niet, bekend probleem met sql server. Enige uitweg is een user-function maken die wel de correcte weeknummers terug geeft voor een gegeven datum. Meest gebruikte methode hier in europa is geloof ik dat weeknummer 1 begint in de week waarin minimaal 4 dagen van het nieuwe jaar in zitten.