11. september 2002 - 10:08Der er
25 kommentarer og 1 løsning
Or i select igen igen
Hej Eksperter Den her er ikke nem. som det er nu er resultatet 4 poster, hvor der faktisk gerne skulle være 5. problemet er at den 5 post bliver sorteret fra i disse linier:
(WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.StartDate > '07/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StartDate > '07/08/02') AND
De to linier her skulle hente den 5 post
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
Men hvordan gør jeg det? Her er hele min query
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid
WHERE
(WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.StartDate > '07/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StartDate > '07/08/02') AND
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
OR
(WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StopDate < '07/01/02') AND (WPMProjectFlow.StopDate < '07/08/02') AND
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
I et inbound callcenter, hvor identiteten på den, der ringer ind, skal bekræftes, kan kontrollen nu foregå i telefonkøen. Det understøtter fem centrale KPI'er for callcentre.
Prøv om det ikke hjælper at separere de to OR-statments:
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid
WHERE ( (WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.StartDate > '07/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StartDate > '07/08/02') AND
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL) ) OR (
(WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StopDate < '07/01/02') AND (WPMProjectFlow.StopDate < '07/08/02') AND
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL) )
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid
WHERE
((WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.StartDate > '07/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StartDate > '07/08/02') AND
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL) )
OR
((WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StopDate < '07/01/02') AND (WPMProjectFlow.StopDate < '07/08/02') AND
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL))
Ocp --> Det hjælper ikke. Resultatet bliver det samme. Nej der skal stå AND. OR vil gi alle poster og blive opfattet som var den uden for den øvrige select. Måske problemet i virkeligheden er at min query ikke er struktureret ordentligt. I så fald må der være en metode til at holde AND og OR samlet så det ikke kan misforstået af sql-serveren.
Kan du beskrive i ord hvordan dette statement skal virke i sammenhængen? Jeg har en fornemmelse af hvad du prøver at lave, men jeg vil lige være sikker:
(WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
Ocp --> Den første dato (WPMProjectFlow.StartDate > '01/01/02') er hardcoded. Det er bare for at jeg ikke får gamle data med. De to andre er variabler fra min asp side. Du har ret i at den ene er overflødig, den er væk nu. Mit statement skal hente personer der er ledige Fra en dato til en dato. (WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL) skal give mig de personer der er ledige noget af dagen. Hvis der står NULL er de helt fri i perioden og er datestep under 8 er de ledige noget af en dag i det indtastede tidsrum.
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid WHERE
(WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.StartDate > '07/08/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
OR
(WPMProject.Active = 1) AND (WPMProjectFlow.StartDate > '01/01/02') AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StopDate < '07/01/02') AND (WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
No but it is easier to see exactly which records you are finding! You could also make a VIEW (Enterprise Manager), here you have a visual designer much like Access
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid WHERE (WPMProject.Active = 1) AND (WPMProjectFlow.ProjectID = 43) AND (WPMProjectFlow.StartDate > cast('20020708' as datetime)) AND (WPMProjectFlow.StopDate < cast('20020701' as datetime) or WPMProjectFlow.StopDate is null) AND (WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL)
Startdato og stopdato skulle lige byttes rundt. men ellers gav den en post. Nemlig den der havde et datestep under 8. Det var den der manglede i min version
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid WHERE WPMProject.Active = 1 AND WPMProjectFlow.ProjectID = 43 AND ( ( ( WPMProjectFlow.StopDate >= cast('20020708' as datetime) AND WPMProjectFlow.StartDate <= cast('20020701' as datetime) ) AND ( WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL ) ) OR ( WPMProjectFlow.StopDate < cast('20020701' as datetime) OR WPMProjectFlow.StartDate > cast('20020708' as datetime) ) )
Nu får du samme resultat som mig, nemlig uden ham du fik med først. Med den garanti sender jeg gerne databasen til dig. :-) Bare som en backup eller hvad?
- Enten skal personen være fri fra projekter i perioden (nederste del af where-statementet. - Eller også skal han være på deltidsprojekt i perioden (øverste del af projektet)
SELECT WPMProjectFlowHours.datestep, ACMUser.Username, WPMProjectFlow.ID, WPMProjectFlow.UserID, WPMProjectFlow.StartDate, WPMProjectFlow.StopDate, WPMProjectFlow.ActualStartDate, WPMProjectFlow.ActualStopDate, WPMProjectFlow.WorkTypeID, WPMProjectWorkType.Name AS pname, WPMProjectFlow.Name, WPMProjectFlow.Description FROM ACMUser INNER JOIN WPMProjectFlow ON ACMUser.ID = WPMProjectFlow.UserID INNER JOIN WPMProject ON WPMProject.ID = WPMProjectFlow.ProjectID INNER JOIN WPMProjectWorkType ON WPMProjectFlow.WorkTypeID = WPMProjectWorkType.ID LEFT OUTER JOIN WPMProjectFlowHours ON WPMProjectFlow.ID = WPMProjectFlowHours.flowid WHERE WPMProject.Active = 1 AND WPMProjectFlow.ProjectID = 43 AND ( ( WPMProjectFlow.StopDate >= cast('20020701' as datetime) AND WPMProjectFlow.StartDate <= cast('20020708' as datetime) AND ( WPMProjectFlowHours.datestep < 8 OR WPMProjectFlowHours.datestep IS NULL ) ) OR ( ( WPMProjectFlow.StopDate < cast('20020701' as datetime) OR WPMProjectFlow.StartDate > cast('20020708' as datetime) ) OR ( WPMProjectFlow.StopDate is null AND WPMProjectFlow.StartDate is null ) )
)
ORDER BY WPMProjectFlow.StartDate
Du forudsætter at der findes en start- eller stopdato. I min kode har jeg taget højde for at personen måske aldrig har været tilknyttet et projekt.
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.
