[SQL / Access] record meerdere keren weergeven/tellen

[TXC]

Hier op mijn werk werken we met een workflowsysteem met tickets. Elke ticket heeft een date_opened en een date_closed veld. Nu heb ik een koppeling met Access gemaakt en daarin wil ik graag door middel van een query per dag kunnen zien hoeveel tickets geopend zijn. Maar tickets kunnen meerdere dagen open zijn, dus soms moet een record meerdere keren worden geteld. Ik kom er maar niet uit hoe dat met SQL zou moeten. Alleen iets met de date_opened is makkelijk te doen met een groupby maar ik wil dus dat het record bij elke datum erbij wordt geteld waarop het ticket nog open was.

Om het nog iets moeilijker te maken wil ik dat ook data waarop geen tickets open zijn worden weergegeven. Daarom heb ik een tweede tabel (datum) met alle dagen van het jaar (in een kolom). En dan wil ik van de eerste tot de laatste van de maand zien hoeveel tickets er elke dag open waren. Dus per dag in de tabel datum het aantal tickets open geteld uit de tabel tickets.

Ik wil dus ongeveer zoiets als hieronder:
1-8-05 30
2-8-05 50 (bijvoorbeeld 10 nieuwe tickets geopend erbij en 10 van gisteren niet geclosed)
3-8-05 72
4-8-05 40
5-8-05 51
etc. tot einde van de maand

Thanks for any help

[lier]

Volgens mij is dit erg eenvoudig:

Je gaat per dag bekijken of:

begindatum gelijk aan dag
dag tussen begindatum en einddatum
dag na begindatum (bij openstaande einddatum)
einddatum gelijk aan dag (of een dag ervoor, afhankelijk hoe/wat je wil)

Wat lukt niet ?

edit:

Je zal een loopje moeten maken om door de dagen (die je als range van-tot op geeft) heen te lopen.
Maar uitgangspunt is de dag en die vergelijk je met de begin en einddatum in de database.

[ Voor 28% gewijzigd door lier op 23-08-2005 13:39 ]

[P_de_B]

Die tabel met alle dagen van het jaar is inderdaad erg handig in dit soort gevallen.

SQL:

SELECT d.Dag, COUNT(ticket_id) as Aantal
FROM tblMetAlleDagen d
LEFT OUTER JOIN tblTickets t on d.Dag BETWEEN t.date_opened AND t.date_closed
GROUP BY d.Dag

[lier]

Inderdaad PB, maar een kleine uitbreiding:

code:

SELECT d.Dag, COUNT(ticket_id) as Aantal
FROM tblMetAlleDagen d
LEFT OUTER JOIN tblTickets t on d.Dag BETWEEN t.date_opened AND isnull(t.date_closed, getdate())
GROUP BY d.Dag

Denk namelijk dat de einddatum ook null kan zijn (niet afgesloten)

[P_de_B]

Jep, je hebt gelijk, maar is dit Access en geen SQL Sever, dus ISNULL zal vervangen moeten wordern door een constructie met IIF denk ik, en GetDate() door Now().

offtopic:
tegenwoordig kun je [code=sql] gebruiken

[ Voor 41% gewijzigd door P_de_B op 23-08-2005 13:44 ]

[TXC]

P_de_B schreef op dinsdag 23 augustus 2005 @ 13:39:
Die tabel met alle dagen van het jaar is inderdaad erg handig in dit soort gevallen.

SQL:

SELECT d.Dag, COUNT(ticket_id) as Aantal
FROM tblMetAlleDagen d
LEFT OUTER JOIN tblTickets t on d.Dag BETWEEN t.date_opened AND t.date_closed
GROUP BY d.Dag

Aha is het zo eenvoudig? Ik ken die OUTER JOIN niet (werk in Access eigenlijk altijd met de ontwerpweergave). Ik probeer het even...

Jep, je hebt gelijk, maar is dit Access en geen SQL Sever, dus ISNULL zal vervangen moeten wordern door een constructie met IIF denk ik, en GetDate() door Now().

Ik kreeg het al niet werkend met die ISNULL. Ik gebruik idd altijd now() in Access dus ik vond dat GetDate ook al gek. Nog even doorprutsen.

[ Voor 25% gewijzigd door TXC op 23-08-2005 14:06 ]

[TXC]

SQL:

SELECT d.Dag, COUNT(ticket_id) as Aantal 
FROM tblMetAlleDagen d 
LEFT OUTER JOIN tblTickets t on d.Dag BETWEEN t.date_opened AND t.date_closed 
GROUP BY d.Dag

Het lukt me nog niet omdat ik de query niet werkend krijg qua syntax in Access. Als melding krijg ik "Between operator without And in query expression 'd.dag BETWEEN t.date_opened'".

Natuurlijk heb ik eerst de juiste tabelnamen ingevuld maar ik blijf die melding krijgen. Wat ik niet snap aan de SQL code is de losse t (na tblTickets) en d (na tblMetAlleDagen). Als je met d bedoelt dat dat de tabel met alle dagen is (aangezien je d.Dag wil selecten) waarom staat er dan "FROM tblMetAlleDagen d" . Ik voel me een of andere newby terwijl ik al jaren apps in Access maak

[ Voor 4% gewijzigd door TXC op 23-08-2005 14:32 ]

[P_de_B]

Ik heb een alias aan de tabel gegeven door er 'd' achter te zetten. In plaats van tblMetAlleDagen kun je dan de afkosting 'd' gebruiken.

Wil je eens een exacte copy/paste van de probleemquery hier posten, dus niet overtypen en geen veldjes wijzigen.

[TXC]

Dit is hem:

SQL:

SELECT d.algemeen_datum, COUNT(ticket_number) as Aantal  
FROM tbl_dates d  
LEFT OUTER JOIN tbl_ticket_info t on d.algemeen_datum BETWEEN t.ticket_open_date AND t.ticket_close_date
GROUP BY d.algemeen_datum

De melding die ik krijg is: "Between operator without And in query expression 'd.algemeen_datum BETWEEN t.ticket_open_date'.

[TXC]

Ik ben alweer flink opgeschoten. Begonnen met de query builder en daarna met SQL aan de slag gegaan. Het werkt nu goed alleen de groupby moet er nog even in. Bedankt voor de hulp.

SQL:

SELECT tbl_dates.algemeen_datum, tbl_ticket_info.ticket_open_date_short, tbl_ticket_info.ticket_dose_date_short,tbl_ticket_info.ticket_number
FROM tbl_dates LEFT JOIN tbl_ticket_info ON (tbl_dates.algemeen_datum BETWEEN tbl_ticket_info.ticket_open_date_short AND tbl_ticket_info.ticket_dose_date_short);

offtopic:
Eigenlijk was dit wel wat basic SQL de mods will hate me for this