Microsoft SQL - Dublicaten niet meenemen in resultaat VIEW

[Jboy1991]

Beste tweakers,

Ik loop vast op een naar mijn idee simpel probleem.

Wij hebben op dit moment 2 tabellen in 1 database,
1 - Medewerkers
2 - Contracten

In het Medewerkers tabel staan alle medewerkers incl personeelsnummer. In contracten staan de contracten van de medewerkers + voor hoeveel % dat contract gebruikt wordt. Er kunnen dus meerdere contracten onder een medewerker hangen.

Nu ben ik bezig met het opzetten van een VIEW tabel. In deze VIEW wil ik maar 1 ROW per medewerker terug hebben. Deze ROW moet dan gevuld zijn met de contract gegevens waar de medewerker het meest bij werkt.

Nu heb ik de basis opgezet door middel van een Inner Join. Ik had gehoopt via een Group BY op het persons_id, dat ik maar 1 keer het resultaat terug zou krijgen. Echter krijg ik bij Group by de volgende foutmelding:

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Column 'dbo.persons.person_id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


---------------------------
OK
---------------------------

De Query

SELECT DISTINCT
b.person_id, b.person_code, b.initials, b.first_name, b.last_name, b.last_name_prefix, b.gender, b.is_manager, a.person_id AS Expr1, a.employment_id, a.date_employed_from, a.date_employed_to,
a.hours_per_week, a.working_days, a.company_id, a.company, a.department_id, a.department, a.cost_center_id, a.cost_center, a.job_title_id, a.job_title, a.manager_person_id, a.date_last_working_day
FROM dbo.employments AS a INNER JOIN
dbo.persons AS b ON a.person_id = b.person_id
WHERE (a.date_employed_to IS NULL) OR
(a.date_employed_to >= GETDATE())
GROUP BY a.person_id

Ik heb de Query ook al omgedraaid gehad ( dat de join in de persons kwam ipv in employments)

Wie kan helpen om mijn gewenste resultaat te krijgen?
- Group by werkt niet
- DISTINCT werkt ook niet

Het is enorm belangrijk om de goede resultaat in de VIEW te krijgen, in de tooling die aan die VIEW gekoppeld komt, is het niet mogelijk om nog op te filteren.

[Stinu]

Dit moet met dit lukken: https://learn.microsoft.c...s/row-number-transact-sql.

Partitie per personeelsnummer, sorteren per "waar de medewerker het meest bij werkt" DESC.
En enkel waar ROW_NUMBER() = 1 nemen.

[Jboy1991]

Stinu schreef op dinsdag 21 november 2023 @ 20:00:
Dit moet met dit lukken: https://learn.microsoft.c...s/row-number-transact-sql.

Partitie per personeelsnummer, sorteren per "waar de medewerker het meest bij werkt" DESC.
En enkel waar ROW_NUMBER() = 1 nemen.

Bedankt! Het lijkt goede resultaten te geven!

WITH medewerkers AS (
SELECT a.person_id, a.person_code, a.initials, a.first_name, a.last_name, a.last_name_prefix, a.gender, a.is_manager, b.person_id AS Expr1, b.employment_id, b.date_employed_from, b.date_employed_to,
b.hours_per_week, b.working_days, b.company_id, b.company, b.department_id, b.department, b.cost_center_id, b.cost_center, b.job_title_id, b.job_title, b.manager_person_id, b.date_last_working_day,
ROW_NUMBER() OVER (PARTITION BY b.person_id
ORDER BY b.hours_per_week DESC) AS contracten
FROM dbo.employments AS b INNER JOIN
dbo.persons AS a ON (a.person_id = b.person_id)
WHERE (b.date_employed_to IS NULL) OR
(b.date_employed_to >= GETDATE())
)
SELECT
*
FROM medewerkers
WHERE contracten= 1;

Het enige dat ik nu nog moet uitdenken is wat gebeurd er als iemand 2 contracten heeft met elk dezelfde uren. Het moet niet zo zijn dat die na elke query-request verschillende resultaten geeft.

hmm.

[ Voor 7% gewijzigd door Jboy1991 op 21-11-2023 20:41 ]

[sig69]

Heeft @Stinu je de query via dm gestuurd? Als ik je OP lees ontbreekt het aan wat basiskennis en het lijkt me sterk dat je dan zelf op basis van een hint een query met een cte en row number functies in elkaar sleutelt. Maakt mij niet zoveel uit verder maar het is fijn als je ook begrijpt wat het doet en wat leert. Een bepaalde moderator hier en "teach a man to fish" etc.

[sig69]

Jboy1991 schreef op dinsdag 21 november 2023 @ 20:38:
[...]


Het enige dat ik nu nog moet uitdenken is wat gebeurd er als iemand 2 contracten heeft met elk dezelfde uren. Het moet niet zo zijn dat die na elke query-request verschillende resultaten geeft.

hmm.

Eerste, laatste, hoogste cost center, hang er een gewicht aan.. Wat de business nodig heeft. Kan onder het kopje "aanvrager bepaalt".

[Jboy1991]

sig69 schreef op dinsdag 21 november 2023 @ 20:56:
Heeft @Stinu je de query via dm gestuurd? Als ik je OP lees ontbreekt het aan wat basiskennis en het lijkt me sterk dat je dan zelf op basis van een hint een query met een cte en row number functies in elkaar sleutelt. Maakt mij niet zoveel uit verder maar het is fijn als je ook begrijpt wat het doet en wat leert. Een bepaalde moderator hier en "teach a man to fish" etc.

Beetje aparte opmerking. Nee, de query is niet toegestuurd en is zelf gemaakt a.d.h.v. het antwoord die Stinu gaf, soms is een beetje iemand de juiste richting wijzen voldoende.

Basiskennis wil ik dit niet noemen trouwens. Dit is voor het eerste dat ik views maak en in deze views de data nodig heb. Normaal had ik een middleware geschreven die de data voor mij al correct in de database plaatst. Wat de query doet is mij geheel duidelijk

sig69 schreef op dinsdag 21 november 2023 @ 21:00:
[...]

Eerste, laatste, hoogste cost center, hang er een gewicht aan.. Wat de business nodig heeft. Kan onder het kopje "aanvrager bepaalt".

Helaas werkt de organisatie waar ik niet werkt op die manier. Ik vermoed dat het een Order By wordt op de contract-start-datum. Welk contract eerst is, wordt dan getoond. Moet ik morgen maar even over denken.

[sig69]

Jboy1991 schreef op dinsdag 21 november 2023 @ 21:05:
[...]

Beetje aparte opmerking. Nee, de query is niet toegestuurd en is zelf gemaakt a.d.h.v. het antwoord die Stinu gaf, soms is een beetje iemand de juiste richting wijzen voldoende.

Basiskennis wil ik dit niet noemen trouwens. Dit is voor het eerste dat ik views maak en in deze views de data nodig heb. Normaal had ik een middleware geschreven die de data voor mij al correct in de database plaatst. Wat de query doet is mij geheel duidelijk

Nou ja ik dacht omdat je termen als database en tabel door elkaar haalt, en een foutmelding uit een group by statement niet kan plaatsen. Niet lullig bedoeld verder.

Helaas werkt de organisatie waar ik niet werkt op die manier. Ik vermoed dat het een Order By wordt op de contract-start-datum. Welk contract eerst is, wordt dan getoond. Moet ik morgen maar even over denken.

Iemand heeft bepaald dat dit nodig is toch? Kan jij ook zelf zijn natuurlijk, dan is het jouw keus

[Jboy1991]

sig69 schreef op dinsdag 21 november 2023 @ 21:14:
[...]

Nou ja ik dacht omdat je termen als database en tabel door elkaar haalt, en een foutmelding uit een group by statement niet kan plaatsen. Niet lullig bedoeld verder.

[...]

Eens dat dat er best knullig uit ziet. Inmiddels aangepast, al twijfelde ik even of een view een database of tabel is, maar google leerde mij dat het een tabel is.

[sig69]

Jboy1991 schreef op dinsdag 21 november 2023 @ 21:42:
[...]

Eens dat dat er best knullig uit ziet. Inmiddels aangepast, al twijfelde ik even of een view een database of tabel is, maar google leerde mij dat het een tabel is.

Nee. Een view is geen tabel.

[ Voor 3% gewijzigd door sig69 op 21-11-2023 23:44 ]

[Jboy1991]

sig69 schreef op dinsdag 21 november 2023 @ 23:40:
[...]

Nee. Een view is geen tabel.

Hmm
https://www.w3schools.com...20like%20a%20real%20table.

Het is een tabel. Alleen bevat het zelf geen data maar haalt het data op dmv ene query. Maar het resultaat is een tabel.

[sig69]

Een view is een virtuele tabel, zoals je zelf aanhaalt. Het resultaat is een tabel, maar dat is elke query uiteindelijk.
Het is belangrijk om je terminologie goed te hebben. MB vs mb scheelt ook nogal wat bijvoorbeeld.
(Mag ik je functie raden? Data scientist of iets dergelijks?)

[ Voor 84% gewijzigd door sig69 op 22-11-2023 00:10 ]

[Stinu]

@Jboy1991 goed dat je zelf gevonden hebt met een juiste richting aan te duiden
Nog een tip, als ik een OR gebruik in mijn WHERE. Zet ik deze altijd tussen haakjes.

Anders krijg je soms onverwachte resultaten als je "snel" even iets toevoegt.

WHERE voorwaarde1 OR voorwaarde2 AND voorwaarde3