rapport en COUNT(Distinct)

[maike028]

Hoi,

Sinds kort ben ik mij mysql eigen aan het maken maar beheers nog niet alle materie.
Ik ben een rapport aan het maken en heb de volgende query hiervoor gebruikt:

SELECT CONVERT(varchar(255), Client.ClientNummer) + ' ' + Client.Achternaam
AS Cliënt, COUNT(DISTINCT Urenregistratie.MedewerkerID) AS Aantal
FROM Medewerker INNER JOIN
DossierItem INNER JOIN
Dossier ON DossierItem.DossierID = Dossier.DossierID INNER JOIN
Urenregistratie ON DossierItem.ID = Urenregistratie.ItemID INNER JOIN
Client INNER JOIN
Zwangerschap ON Client.ID = Zwangerschap.ClientID ON
Dossier.DossierID = Zwangerschap.DossierID ON
Medewerker.DossierID = Urenregistratie.MedewerkerID
WHERE (Dossier.Archief <> 1) AND (Dossier.Verwijderd <> 1) AND
(Urenregistratie.MeeloopUren = 0) AND (Urenregistratie.Partus = 0)
GROUP BY Client.ClientNummer, Client.Achternaam
ORDER BY Client.ClientNummer

Ik krijg nu een overzicht met het aantal medewerkers per client. Ik heb hier twee vragen over.

1.
Nu wil ik deze graag gesommeerd hebben met mw1, mw2, mw3 en mw>3.
Hoe kan ik dit aangeven?

Ik zat zelf te denken aan

WHERE SUM(Urenregistratie.MedewerkerID =1) AS mw1 AND SUM(Urenregistratie.MedewerkerID =2) AS mw2 AND SUM(Urenregistratie.MedewerkerID =3) AS mw3 AND SUM(Urenregistratie.MedewerkerID >3) AS mw>3

maar dit werkt niet.


2.
Ik wil graag in het rapport de resultaten per maand weergegeven hebben en als parameter een jaartal (gegevens afleiden uit Urenregistratie.Datum) Hoe kan ik dit het beste doen? Zo ook voor de maanden dat dan automatisch de gegevens van die maand in de juiste kolom vermeld worden.

Maand Mw1 Mw2 Mw3 Mw>3
januari
februari
maart
april etc.

Alvast bedankt!
Groetjes Maike

 

[Skinflowers]

Hoi,

Als je kolommen wilt hebben met mw1,mw2,mw3 en mw>3, kan je hiervoor de CASE gebruiken.

dus zoiets:

SELECT client, SUM (CASE mw=1 THEN Urenregistratie.uren ELSE 0 END) AS AANTAL_MW1

In dit geval worden uren alleen maar gesommeerd om voor mw1, voor andere mw's wordt namelijk 0 erbij opgeteld.

Als je raporten gaat maken is CASE altijd erg handig..hoewel je query er onleesbaar door worden.. (Je ziet de CASE ook vaak terug waar je totalen per maand wil hebben.. Je hebt dan wel 12 CASE instructie nodig!)

Als je meer hulp nodig hebt moet je tabellen even posten..

gr.

 

[maike028]

Hoi Skinflower,

Bedankt voor je reactie. Ik kom er nog niet uit. Vandaar dat ik de tabellen post

De tabellen:
Client.*
Medewerker.*
Zwangerschap.*
Urenregistratie.*
Dossier.*
DossierItem.*

De velden die je nodig hebt voor het oproepen van de juiste gegevens:
CONVERT(varchar(255), Client.ClientNummer) + ' ' + Client.Achternaam AS Cliënt Urenregistratie.MedewerkerID

De relaties:
Medewerker INNER JOIN
DossierItem INNER JOIN
Dossier ON DossierItem.DossierID = Dossier.DossierID INNER JOIN
Urenregistratie ON DossierItem.ID = Urenregistratie.ItemID INNER JOIN
Client INNER JOIN
Zwangerschap ON Client.ID = Zwangerschap.ClientID ON Dossier.DossierID = Zwangerschap.DossierID ON
Medewerker.DossierID = Urenregistratie.MedewerkerID

Toelichting:
Iedere dag dat een medewerker gewerkt heeft krijgt een nieuwe urenregel (Urenregistratie.ItemdID). Dit betekent als een persoon acht dagen werkt bij een client de medewerkerid 8 keer voorkomt. Dit heb ik inmiddels met de volgende code teruggebracht naar 1
COUNT(DISTINCT Urenregistratie.MedewerkerID) AS Aantal

Nu moet ik alleen nog de aantallen groeperen
SOM Aantal medewerker =1
SOM Aantal medewerker = 2
SOM Aantal medewerker = 3 etc.

Je gaf ook aan dat de maanden met Case weergegeven worden. (Urenregistratie.Datum wordt als dd-MM-yyyy weergegeven)
Case If Urenregistratie.Datum, MM="1" Then maand = "Januari"
Case If Urenregistratie.Datum, MM="2" Then maand = "Februari" etc

Ik ben hier niet zo thuis is, maar is dit wat je bedoeld?

Alvast bedankt

Groetjes Maike

 

[Skinflowers]

Hoe moet je rapport er uit zien ??

wil je zo'n report:
client, medewerker, dagen
jansen, 1, 8
de vries 1, 4
de vires 2, 4

of zo'n report:
client, medewerker_1, mederwerker_2, medewerker_3, medewerker > 3
jansen, 8, 0, 0, 0
de vries 4, 4, 0, 0

dit laatste kan alleen als het aantal medewerkers vast staat.. en niet meer veranderd...

dit laatste type raport kan je gebruiken voor overzicht per maand (er zijn maar 12 maanden en dit zal niet snel veranderen )

 

[maike028]

Hoi Skinflower,

Naar aanleiding van de code

SELECT CONVERT(varchar(255), Client.ClientNummer) + ' ' + Client.Achternaam
AS Cliënt, COUNT(DISTINCT Urenregistratie.MedewerkerID) AS Aantal

krijg ik nu de gegevens
CLIENT AANTAL MEDEWERKERS
client A 1
Client B 3
Client C 2
Client D 1
Client E 2

Wat ik nu echter wil is dat ik per maand kan zien hoeveel clienten er 1 medewerker gehad hebben, 2 medewerkers, 3 medewerkers etc....
Ik moet nu dus een code gebruiken waarmee ik middels Urenregistratie.datum per maand inzichtelijk maakt hoeveel gezinnen er 1 mw hebben gehad etc......

 

[Skinflowers]

Volgens mij moet de volgende query werken: Ik heb echter geen mySQL dus kan ik het niet controleren

Als je een parameter wilt toevoegen aan je query moet je er een stored procedure van maken. Hoe staat oa. hier beschreven: http://www.ivosa.be/cursus/databanken/hfstk25.htm de where moet je dan uitbreiden met YEAR(Urenregistratie.Datum) = jaartal

Alleen wat moet er gebeuren als een client een medewerkers heeft in januari en februari ? Nu worden deze client meegeteld in januari en februari.. is dit wat je wilt ?

SELECT T.maand, Count(DISTINCT T.Client), T.Aantal FROM
(SELECT Client.ClientNummer AS Client, COUNT(DISTINCT Urenregistratie.MedewerkerID) AS Aantal, MONTH(Urenregistratie.Datum) as Maand
FROM Medewerker INNER JOIN
DossierItem INNER JOIN
Dossier ON DossierItem.DossierID = Dossier.DossierID INNER JOIN
Urenregistratie ON DossierItem.ID = Urenregistratie.ItemID INNER JOIN
Client INNER JOIN
Zwangerschap ON Client.ID = Zwangerschap.ClientID ON
Dossier.DossierID = Zwangerschap.DossierID ON
Medewerker.DossierID = Urenregistratie.MedewerkerID
WHERE (Dossier.Archief <> 1) AND (Dossier.Verwijderd <> 1) AND
(Urenregistratie.MeeloopUren = 0) AND (Urenregistratie.Partus = 0)
GROUP BY Client.ClientNummer, MONTH(Urenregistratie.Datum) 
) AS T
group by T.aantal, T.maand order by T.maand, T.aantal

Als je de maanden horizontaal will hebben wordt de query..(deze is niet helemaal uitgeschreven je moet de ... vervangen door nog een paar keer case statement,)

SELECT T.Aantal, SUM (CASE WHEN T.maand = 1 THEN 1 ELSE 0 END) as JAN, SUM (CASE WHEN T.maand = 2 THEN 1 ELSE 0 END) as feb ....,SUM (CASE WHEN T.maand = 12 THEN 1 ELSE 0 END) as dec FROM (SELECT Client.ClientNummer AS Client, COUNT(DISTINCT Urenregistratie.MedewerkerID) AS Aantal, MONTH(Urenregistratie.Datum) as Maand
FROM Medewerker INNER JOIN
DossierItem INNER JOIN
Dossier ON DossierItem.DossierID = Dossier.DossierID INNER JOIN
Urenregistratie ON DossierItem.ID = Urenregistratie.ItemID INNER JOIN
Client INNER JOIN
Zwangerschap ON Client.ID = Zwangerschap.ClientID ON
Dossier.DossierID = Zwangerschap.DossierID ON
Medewerker.DossierID = Urenregistratie.MedewerkerID
WHERE (Dossier.Archief <> 1) AND (Dossier.Verwijderd <> 1) AND
(Urenregistratie.MeeloopUren = 0) AND (Urenregistratie.Partus = 0)
GROUP BY Client.ClientNummer, MONTH(Urenregistratie.Datum) 
) AS T group by T.aantal

 

[maike028]

Hoi Skinflowers!

Heel erg bedankt voor je hulp. Ik ga nu met de stored procedure op de server aan de slag.

1000x dank

groetjes maike