Aantallen tellen (count)

[pascal_rienstra]

Beste tweakers,

Ik zit met een query waar ik niet uitkom. Ik heb onderstaande tabel en zou onderstaand resultaat willen hebben.

De kolom Enforcement_date is niet altijd gevuld, toch zou ik indien deze per subname willen vergelijken/tellen. Dus bij subname A-1 is de Enforcement_date bij 1 van de 3 regels gevuld. Ik zou dan vervolgens al die 3 regels willen vergelijken of de datum in Returned < is dan de datum bij Enforcement_date (count: Returned < Enforcement_date)

Daarnaast zou ik ook de Enforcement_date + maand willen bereken en vervolgens deze maand willen vergelijken met de datum bij returned (count: Returned < Enforcement_date+1)

ID	Projectname	Subname	Exp_return_date	Returned	Enforcement_date
1	A	A-1	1-4-2023	28-1-2023
2	A	A-1	1-4-2023		01-05-2023
3	A	A-1	1-4-2023	28-4-2023
4	A	A-2	1-7-2023	10-08-2023	02-08-2023
5	A	A-2	1-7-2023	28-06-2023
6	A	A-2	1-7-2023		02-08-2023
7	B	B-1	1-3-2023	04-04-2023	01-04-2023
8	B	B-1	1-3-2023	02-03-2023
9	B	B-2	1-6-2023	28-05-2023
10	B	B-2	1-6-2023	03-06-2023

Project	Subname	ED	Count	<= Exp_return_date	< enformcement_date	Enforcement_date_+1_month	None
A	A-1	01-05-2023	3	1 (all before 01-04-2023)	2 (all before 01-05-2023)	2 (all before 01-06-2023)	1
A	A-2	02-08-2023	3	1 (all before 01-07-2023)	1 (all before 02-08-2023)	2 All before (02-09-2023)	1
B	B-1	01-04-2023	2	0 (all before 01-03-2023)	1 (all before 01-04-2023	2 All before 01-05-2023)	0
B	B-2		2	1 (all before 01-06-2023)			0

code:

SELECT [Project], [Subname], MIN(Enforcement_date) as ‘ED’,
COUNT(*) as 'Count', 
COUNT(case when Returned <= Exp_return_date then 1 else null end) as ‘<= Exp_return_date’,

COUNT(case when Returned <= Enforcement_date then 1 else null end) as ‘< Enformcement_date’, -> didn’t works, Enforcement_date is not always entered.
COUNT(case when Returned <= Enforcement_date+1month then 1 else null end) as ‘< Enformcement_date’, -> how can I calculated this?

COUNT(case when Returned <> '' then 1 else null end) as ‘None’

FROM *******
GROUP BY [Project], [Subname]

[Wokker]

Misschien kan je beter sum gebruiken inplaats van count.

code:

SELECT 
    [Project], 
    [Subname], 
    MIN(Enforcement_date) as ED,
    COUNT(*) as Count, 
    SUM(CASE WHEN Returned <= Exp_return_date THEN 1 ELSE 0 END) as [<= Exp_return_date],
    SUM(CASE WHEN Returned <> '' AND Returned < Enforcement_date THEN 1 ELSE 0 END) as [< Enforcement_date],
    SUM(CASE WHEN Returned <> '' AND Returned < DATEADD(MONTH, 1, Enforcement_date) THEN 1 ELSE 0 END) as [< Enforcement_date+1month],
    SUM(CASE WHEN Returned IS NULL OR Returned = '' THEN 1 ELSE 0 END) as None
FROM *****
GROUP BY [Project], [Subname]

[Vloris]

Waar gaat dit over? Excel? Sqlserver? Mysql? Mariadb? Postgresql? Nog iets onbekends?

Je vraag is ook nogal vaag gesteld, misschien dat je er zelf niet uit komt omdat je voor jezelf de vraagstelling ook nog niet duidelijk hebt? Wat wil je met de regels waar enforcement_date ontbreekt? Dat staat er niet bij.

Kortom: er mist nog wat belangrijke informatie waarmee wij je van nuttige antwoorden kunnen voorzien

[pascal_rienstra]

Dank voor de reacties!
Het gaat om SQL.

Ik dacht dat mijn uitleg wel duidelijk was, maar niet dus ;-)

ID 1+2+3 horen bij elkaar, alle drie project A en sub 1-A. Enkel bij ID 2 staat de datum 01-05-2023. Ik wil dus voor ID 1 + 2 + 3 controleren hoeveel er returned zijn voor die datum. Dit zou in dit voorbeeld dus ID 1 + ID 3 zijn (2 stuks).

De datum van 1-5-2023 zou dus "virtueel" bij ID 1 +3 ingevuld moeten worden zodat ermee vergelijken kan worden (Returned < Enforcement_date).

[Compuchip87]

En als bij ID 1 nou 02-06 als enforcement date staat? Welke datum zou regel 3 dan moeten gebruiken?

[GeeEs]

pascal_rienstra schreef op vrijdag 25 augustus 2023 @ 16:18:
Dank voor de reacties!
Het gaat om SQL.

Ik dacht dat mijn uitleg wel duidelijk was, maar niet dus ;-)

ID 1+2+3 horen bij elkaar, alle drie project A en sub 1-A. Enkel bij ID 2 staat de datum 01-05-2023. Ik wil dus voor ID 1 + 2 + 3 controleren hoeveel er returned zijn voor die datum. Dit zou in dit voorbeeld dus ID 1 + ID 3 zijn (2 stuks).

De datum van 1-5-2023 zou dus "virtueel" bij ID 1 +3 ingevuld moeten worden zodat ermee vergelijken kan worden (Returned < Enforcement_date).

Als er überhaupt maar 1 datum leidend is, dan negeer je toch gewoon de lege velden en refereer je naar de datum die er wel staat?


Ben totaal geen sql expert, dus misschien zeg ik iets heel doms... of ik denk veel te simpel

[pascal_rienstra]

Compuchip87 schreef op vrijdag 25 augustus 2023 @ 16:24:
En als bij ID 1 nou 02-06 als enforcement date staat? Welke datum zou regel 3 dan moeten gebruiken?

Dan zou de vroegste datum van de subname (1-A) gebruikt moeten worden. Is zoiets te maken?

[mbe81]

Wat je zou kunnen doen is iets zoals:

• Maak een query die per project de enforcement date oplevert
• Join het resultaat van de query met de oorspronkelijke tabel
• Doe de vergelijking van de return date uit de tabel met de enforcement date uit de query
• Tel de resultaten en groepeer per project

[ Voor 3% gewijzigd door mbe81 op 25-08-2023 19:37 ]

[pascal_rienstra]

mbe81 schreef op vrijdag 25 augustus 2023 @ 19:26:
Wat je zou kunnen doen is iets zoals:

• Maak een query die per project de enforcement date oplevert
• Join het resultaat van de query met de oorspronkelijke tabel
• Doe de vergelijking van de return date uit de tabel met de enforcement date uit de query
• Tel de resultaten en groepeer per project

Ik heb op de database geen schrijfrechten, enkel leesrechten. Is zo'n nieuwe query dan mogelijk?

[mbe81]

Jazeker! In plaats van twee tabellen kan je een tabel ook aan het resultaat van een andere query joinen. Dit zijn zogenaamde subqueries.

Een voorbeeld hoe dit er uit zou kunnen zien:

code:

select c.id, c.name, o.last_order_date
from customers c
join (
  select customer_id, max(order_date) last_order_date
  from orders
  group by customer_id
) o
on c.id = o.customer_id

(In dit voorbeeld wordt uit vanuit de orders de laatste besteldatum per klant bepaald en daarna gecombineerd met de klantgegevens)

[ Voor 14% gewijzigd door mbe81 op 25-08-2023 19:50 ]

[pascal_rienstra]

mbe81 schreef op vrijdag 25 augustus 2023 @ 19:49:
Jazeker! In plaats van twee tabellen kan je een tabel ook aan het resultaat van een andere query joinen. Dit zijn zogenaamde subqueries.

Een voorbeeld hoe dit er uit zou kunnen zien:

code:

select c.id, c.name, o.last_order_date
from customers c
join (
  select customer_id, max(order_date) last_order_date
  from orders
  group by customer_id
) o
on c.id = o.customer_id

(In dit voorbeeld wordt uit vanuit de orders de laatste besteldatum per klant bepaald en daarna gecombineerd met de klantgegevens)

Denk dat dat wel lukt. In jouw voorbeeld haal je de gegevens uit 2 tabellen, ik zo dan als eerste de Subname (deze is uniek) en MIN(Enforcement_date) halen en deze vervolgens joinen met dezelfde tabel via de Subname voor de overige data en datums vergelijken en aantallen tellen enz.

[nalletje]

Wellicht kun je met een with werken (MySQL even getest)

code:

WITH 
    `virtual_enforcements` as (
        SELECT 
            `projectname`, 
            `subname`,
            MAX(`enforcement_date`) as `enforcement_date`
        FROM `table_name` 
        WHERE `enforcement_date` IS NOT NULL
        GROUP BY `projectname`, `subname`
    )
SELECT 
    `table_name`.`projectname` as project,
    `table_name`.`subname`,
    `virtual_enforcements`.`enforcement_date` as ed,
    count(1) as `rows`,
    SUM(
        CASE WHEN `table_name`.`returned` < `table_name`.`exp_return_date` AND `table_name`.`returned` IS NOT NULL THEN 1 ELSE 0 END
    ) as lte_exp_return_date,
    SUM(
        CASE WHEN `table_name`.`returned` <= `virtual_enforcements`.`enforcement_date` AND `table_name`.`returned` IS NOT NULL THEN 1 ELSE 0 END
    ) as lte_enforcement_date,
    SUM(
        CASE WHEN `table_name`.`returned` <= DATE_ADD(`virtual_enforcements`.`enforcement_date`, INTERVAL 1 MONTH) AND `table_name`.`returned` IS NOT NULL THEN 1 ELSE 0 END
    ) as bt_enforcement_date_1mp,
    SUM(CASE WHEN `table_name`.`returned` IS NULL THEN 1 ELSE 0 END) as none
FROM `table_name`
LEFT JOIN `virtual_enforcements` ON `virtual_enforcements`.`projectname` = `table_name`.`projectname` AND `virtual_enforcements`.`subname` = `table_name`.`subname`
GROUP BY `table_name`.`projectname`, `table_name`.`subname`;

[mbe81]

pascal_rienstra schreef op vrijdag 25 augustus 2023 @ 20:06:
[...]


Denk dat dat wel lukt. In jouw voorbeeld haal je de gegevens uit 2 tabellen, ik zo dan als eerste de Subname (deze is uniek) en MIN(Enforcement_date) halen en deze vervolgens joinen met dezelfde tabel via de Subname voor de overige data en datums vergelijken en aantallen tellen enz.

Ja, inderdaad! Maar als ik naar je tabelstructuur kijk, dan zou ik persoonlijk ik groeperen (en joinen) op Projectname, Subname. Omdat in theorie dezelfde subname wellicht in meerdere projecten gebruikt kan worden?

Maar dat moet inderdaad werken.

@nalletje WITH kan inderdaad ook, dat is een alternatieve manier om een subquery te definiëren. Wordt ook door praktisch alle databases ondersteund. Ik gebruik dat zelf alleen als ik dezelfde subquery vaker moet herhalen omdat ik deze manier van schrijven minder leesbaar vindt.

[Providentia]

edit -- geen nuttige toevoeging.

[ Voor 97% gewijzigd door Providentia op 25-08-2023 20:39 ]

[mbe81]

Providentia schreef op vrijdag 25 augustus 2023 @ 20:31:
Dit wellicht?

Dit werkt niet. Je doet dan de vergelijk op regelniveau. In de tabel in het start topic staan de te vergelijk datums op verschillende regels. Je moet dus eerst per project de max (of min) enforcement_date bepalen en deze datum gebruiken in de vergelijking.

[nalletje]

mbe81 schreef op vrijdag 25 augustus 2023 @ 20:29:
...

@nalletje WITH kan inderdaad ook, dat is een alternatieve manier om een subquery te definiëren. Wordt ook door praktisch alle databases ondersteund. Ik gebruik dat zelf alleen als ik dezelfde subquery vaker moet herhalen omdat ik deze manier van schrijven minder leesbaar vindt.

Er zitten wel degelijk verschillen in beide methodes. Indien er een simpele join gebruikt kan worden met een relatief kleine tabel is er geen noodzaak voor een WITH, echter is een WITH is mijn ervaringen significant sneller met grotere data-sets

[pascal_rienstra]

nalletje schreef op vrijdag 25 augustus 2023 @ 20:14:
Wellicht kun je met een with werken (MySQL even getest)

code:

WITH 
    `virtual_enforcements` as (
        SELECT 
            `projectname`, 
            `subname`,
            MAX(`enforcement_date`) as `enforcement_date`
        FROM `table_name` 
        WHERE `enforcement_date` IS NOT NULL
        GROUP BY `projectname`, `subname`
    )
SELECT 
    `table_name`.`projectname` as project,
    `table_name`.`subname`,
    `virtual_enforcements`.`enforcement_date` as ed,
    count(1) as `rows`,
    SUM(
        CASE WHEN `table_name`.`returned` < `table_name`.`exp_return_date` AND `table_name`.`returned` IS NOT NULL THEN 1 ELSE 0 END
    ) as lte_exp_return_date,
    SUM(
        CASE WHEN `table_name`.`returned` <= `virtual_enforcements`.`enforcement_date` AND `table_name`.`returned` IS NOT NULL THEN 1 ELSE 0 END
    ) as lte_enforcement_date,
    SUM(
        CASE WHEN `table_name`.`returned` <= DATE_ADD(`virtual_enforcements`.`enforcement_date`, INTERVAL 1 MONTH) AND `table_name`.`returned` IS NOT NULL THEN 1 ELSE 0 END
    ) as bt_enforcement_date_1mp,
    SUM(CASE WHEN `table_name`.`returned` IS NULL THEN 1 ELSE 0 END) as none
FROM `table_name`
LEFT JOIN `virtual_enforcements` ON `virtual_enforcements`.`projectname` = `table_name`.`projectname` AND `virtual_enforcements`.`subname` = `table_name`.`subname`
GROUP BY `table_name`.`projectname`, `table_name`.`subname`;

Helaas blijkt dat bij de With niet alle enforcements dates opgehaald om die nog niet in de tabel aanwezig zijn (wordt beperkt geüpdatet).
Is er ook een mogelijkheid om handmatig enkele subname's met enforcements dates toe te voegen aan het script? Bv
Case IF `subname` = C-1 then
`virtual_enforcements`.`enforcement_date` = '01-09-2023'
END

Case IF `subname` = C-2 then
`virtual_enforcements`.`enforcement_date` = '01-08-2023'
END

[nalletje]

De WITH is een zelfstandige Query. Deze kun je inderdaad aanpassen en ook alléén draaien om te testen.

In mijn voorbeeld heb ik een WHERE toegevoegd met `enforcement_date` IS NOT NULL. Als je deze bijvoorbeeld weghaalt, zal (uit jouw voorbeeld) het B-2 project zichtbaar worden, echter met een NULL (lege waarde)

Als je hardcoded, data aan de query wilt toevoegen, kun je wellicht in de query controleren of de enforcement_date leeg is (NULL) in dat geval, kijk je welk project het is (C-1) en vul je deze met jouw gewenste datum.

Dit zou je dus kunnen doen op basis van een CASE WHEN binnen de with.

code:

SELECT 
            `projectname`, 
            `subname`,
            CASE WHEN MAX(`enforcement_date`) IS NULL AND `subname` = 'B-2' THEN '2023-09-01' ELSE MAX(`enforcement_date`) END as `enforcement_date`
        FROM `table_name` 
        GROUP BY `projectname`, `subname`

Echter is het nooit handig om hardcoded data toe te voegen, wellicht is het beter om een nieuwe tabel te introduceren met deze data?

[KabouterSuper]

Afhankelijk van je dialect sql kan je ook analytical queries gebruiken om je null-waarden op te vullen. Iets in de trant van

code:

Case when enforcement_date is not null 
  then enforcement_date 
  else max(enforcement_date) over (partition by project_name,subname) 
end

Maar de ondersteuning en de precieze syntax hangt af van je dialect (bovenstaande is Oracle)
Sinds er with-statements bestaan, gebruik ik trouwens geen subqueries meer. Het is zoveel overzichtelijker en sneller (omdat je materialize-hints kunt gebruiken).