19. september 2006 - 15:22Der er
14 kommentarer og 1 løsning
Antal ordrer over deadline
Jeg har en ordre-db, som jeg skal lave noget data udtræk på. Jeg har 4 deadlines, og 4 reelt udførte deadlines, alle felter som date.
Jeg skal nu have udtrukket data om hvor mange procent ordre der overskrider de forskellige deadlines over de forskellige måneder. Kan man det direkte i MySQL eller skal jeg hive det over i php, som intranet-systemet er kodet i?
Det har jeg selv fundet ud af at det er, men jeg skal bruge lidt hjælp til at udfærdige sætningen, da den jeg har lavet ikke helt returnerer det jeg regner med.
For nemhedsskyld så lad os kalde datoerne for a1-a4 og p1-p4.
Hmm, måske giver den et korrekt billede alligevel, jeg prøvede at skyde en masse querys afsted, hvor jeg via WHERE spørger på hver enkelt mulighed, og den gav andre resultater på de "falske" dele af IF-sætningerne.
Tilgengæld kommer det samlede antal ordre ikke op på det antal som der er i tabellen, men det gør det i min IF-query.
Så slår det mig, hvordan reagere de to i forhold til hinanden, hvis datofeltet indeholder NULL, 0000-00-00 eller "". Jeg fandt lige ud af at 2 af felterne ikke er date men text, derfor findes NULL og "".
Hvis IF bare ikke tolker dem som falske positive, så kan jeg stadig bruge resultatet.
SELECT SUM(IF(r542a>r576,1,0)) AS notOnTime, SUM(IF(r542a<=r576,1,0)) AS onTime FROM dintabel
På den måde virker SUM funktionerne som en COUNT, da de summerer enten 1 eller 0.
Det giver mulighed for at få alle resultaterne præsenteret på én række. Somme tider er det en fordel i forhold til et gitter (eller hvad man nu kalder det :-).
If (r544a > r578 , If (r550a > r578a , 'godk-levering' , 'godk' ) ,
If (r550a > r578a , 'levering' , 'Ingen' ) ) ) ) as test, count(*) from ordre5 group by If (r542a > r576 , If (r543a > r577 , If (r544a > r578 , If (r550a > r578a , 'Alle overskredet' , 'Statik-optegn-godk' ) ,
Teoretisk set burde min vel være lidt hurtigere, da der ikke er behov for nogen gruppering og sortering, men som du også anfører, så er formatet en smagssag:
SELECT SUM(IF(r542a > r576 AND r543a > r578 AND r544a > r578 AND r550a > r578, 1, 0)) AS Alle_overskredet, SUM(IF(r542a > r576 AND r543a > r578 AND r544a > r578 AND r550a <= r578, 1, 0)) AS Statik_optegn_godk,
SUM(IF(r542a > r576 AND r543a > r578 AND r544a <= r578 AND r550a > r578, 1, 0)) AS Statik_optegn_levering, SUM(IF(r542a > r576 AND r543a > r578 AND r544a <= r578 AND r550a <= r578, 1, 0)) AS Statik_optegn,
SUM(IF(r542a > r576 AND r543a <= r578 AND r544a > r578 AND r550a > r578, 1, 0)) AS Statik_godk_levering, SUM(IF(r542a > r576 AND r543a <= r578 AND r544a > r578 AND r550a <= r578, 1, 0)) AS Statik_godk,
SUM(IF(r542a > r576 AND r543a <= r578 AND r544a <= r578 AND r550a > r578, 1, 0)) AS Statik_levering, SUM(IF(r542a > r576 AND r543a <= r578 AND r544a <= r578 AND r550a <= r578, 1, 0)) AS Statik,
SUM(IF(r542a <= r576 AND r543a > r578 AND r544a > r578 AND r550a > r578, 1, 0)) AS Optegn_godk_levering, SUM(IF(r542a <= r576 AND r543a > r578 AND r544a > r578 AND r550a <= r578, 1, 0)) AS Optegn_godk,
SUM(IF(r542a <= r576 AND r543a > r578 AND r544a <= r578 AND r550a > r578, 1, 0)) AS Optegn_levering, SUM(IF(r542a <= r576 AND r543a > r578 AND r544a <= r578 AND r550a <= r578, 1, 0)) AS Optegn,
SUM(IF(r542a <= r576 AND r543a <= r578 AND r544a > r578 AND r550a > r578, 1, 0)) AS Godk_levering, SUM(IF(r542a <= r576 AND r543a <= r578 AND r544a > r578 AND r550a <= r578, 1, 0)) AS Godk,
SUM(IF(r542a <= r576 AND r543a <= r578 AND r544a <= r578 AND r550a > r578, 1, 0)) AS Levering, SUM(IF(r542a <= r576 AND r543a <= r578 AND r544a <= r578 AND r550a <= r578, 1, 0)) AS Ingen
Jeg ser lige, at jeg vist ikke har fået feltnavnene helt rigtige. Felterne på højre side af sammenligningerne er sat til r578 for de sidste tre sammenligninger, selv om de skulle have været hhv. r577, r578 og r578a.
SELECT SUM(IF(r542a > r576 AND r543a > r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Alle_overskredet, SUM(IF(r542a > r576 AND r543a > r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Statik_optegn_godk,
SUM(IF(r542a > r576 AND r543a > r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Statik_optegn_levering, SUM(IF(r542a > r576 AND r543a > r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Statik_optegn,
SUM(IF(r542a > r576 AND r543a <= r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Statik_godk_levering, SUM(IF(r542a > r576 AND r543a <= r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Statik_godk,
SUM(IF(r542a > r576 AND r543a <= r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Statik_levering, SUM(IF(r542a > r576 AND r543a <= r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Statik,
SUM(IF(r542a <= r576 AND r543a > r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Optegn_godk_levering, SUM(IF(r542a <= r576 AND r543a > r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Optegn_godk,
SUM(IF(r542a <= r576 AND r543a > r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Optegn_levering, SUM(IF(r542a <= r576 AND r543a > r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Optegn,
SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Godk_levering, SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Godk,
SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Levering, SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Ingen
Ja, den er umiddelbart mere overskuelig, det næste er så at den også skal gruppere for hver måned inden for ét regnskabsår.
Jeg tror dog jeg har løst det via group by månedsfeltet, så mangler kun lige årstallet. Men måske vil resultatet også være mere overskueligt i din, kjulius.
Der burde ikke være nogen problemer med at lave en gruppering på et månedsfelt. Noget ala det her burde virke:
SELECT year(logdate) as aar, month(logdate) as maaned, SUM(IF(r542a > r576 AND r543a > r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Alle_overskredet, SUM(IF(r542a > r576 AND r543a > r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Statik_optegn_godk,
SUM(IF(r542a > r576 AND r543a > r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Statik_optegn_levering, SUM(IF(r542a > r576 AND r543a > r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Statik_optegn,
SUM(IF(r542a > r576 AND r543a <= r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Statik_godk_levering, SUM(IF(r542a > r576 AND r543a <= r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Statik_godk,
SUM(IF(r542a > r576 AND r543a <= r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Statik_levering, SUM(IF(r542a > r576 AND r543a <= r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Statik,
SUM(IF(r542a <= r576 AND r543a > r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Optegn_godk_levering, SUM(IF(r542a <= r576 AND r543a > r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Optegn_godk,
SUM(IF(r542a <= r576 AND r543a > r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Optegn_levering, SUM(IF(r542a <= r576 AND r543a > r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Optegn,
SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Godk_levering, SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Godk,
SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Levering, SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Ingen
FROM ordre5 WHERE logdate BETWEEN fiscalstart AND fiscalend GROUP BY year(logdate), month(logdate)
Okay, nu har jeg set outputtet fra sql'en også, og så mener jeg ikke der er noget at diskutere, kjulius din løsning er den bedste og mest overskuelige.
Jeg har lige tilpasset den til mine felter, så den ender med at se sådanne nu: SELECT month(r572a) as maaned, SUM(IF(r542a > r576 AND r543a > r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Alle_overskredet, SUM(IF(r542a > r576 AND r543a > r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Statik_optegn_godk,
SUM(IF(r542a > r576 AND r543a > r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Statik_optegn_levering, SUM(IF(r542a > r576 AND r543a > r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Statik_optegn,
SUM(IF(r542a > r576 AND r543a <= r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Statik_godk_levering, SUM(IF(r542a > r576 AND r543a <= r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Statik_godk,
SUM(IF(r542a > r576 AND r543a <= r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Statik_levering, SUM(IF(r542a > r576 AND r543a <= r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Statik,
SUM(IF(r542a <= r576 AND r543a > r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Optegn_godk_levering, SUM(IF(r542a <= r576 AND r543a > r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Optegn_godk,
SUM(IF(r542a <= r576 AND r543a > r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Optegn_levering, SUM(IF(r542a <= r576 AND r543a > r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Optegn,
SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a > r578 AND r550a > r578a, 1, 0)) AS Godk_levering, SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a > r578 AND r550a <= r578a, 1, 0)) AS Godk,
SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a <= r578 AND r550a > r578a, 1, 0)) AS Levering, SUM(IF(r542a <= r576 AND r543a <= r577 AND r544a <= r578 AND r550a <= r578a, 1, 0)) AS Ingen
FROM ordre5 WHERE ordre5.r572a >= '$startdato' && ordre5.r572a < '$slutdato' GROUP BY month(r572a)";
kjulius --> Smid et svar. arne_v --> tak for hjælpen, du sendte mig et godt stykke af vejen.
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.
Ny bruger
Nybegynder
Opret
Preview
