Help with a SQL query

[eyrei]

Can anyone tell me basically what this query does?

SELECT p.ProfileName, e.LastName + ', ' + e.FirstName AS Expr1, e.Abbr, sd.AssignDate, sd.StartTime, sd.EndTime, sd.CovStartTime, sd.CovEndTime,
sh.CovStartTime AS Expr2, sh.CovEndTime AS Expr3, sd.FillType
FROM SchedData sd INNER JOIN
Shift sh ON sd.ShiftID = sh.ShiftID INNER JOIN
Profile p ON sd.ProfileID = p.ProfileID INNER JOIN
Employee e ON sd.EmployeeID = e.EmployeeID
WHERE (sd.CovStartTime < 0) OR
(sd.CovStartTime > 2359) OR
(sd.CovEndTime < 0) OR
(sd.CovEndTime > 4759)
ORDER BY sd.AssignDate

 

[eyrei]

Anyone?

 

[funxus]

I don't know what all of it means, but from what I remember:

SELECT means you're trying to get records from a database or table.
FROM says what database or table
INNER JOIN don't know what it is.
WHERE gives some conditions that need to be met for the records you're trying to get.
ORDER says how how the records should be ordered.

I think the meaning of this code depends a lot on what the lower case text contains.

Try searching for an SQL language reference on google.

 

[zurichuk]

I'll try, I'm an database guru but I'm used to old style Oracle style SQL and PL/SQL rather than ANSCI (is that so?)

SELECT p.ProfileName, e.LastName + ', ' + e.FirstName AS Expr1, e.Abbr, sd.AssignDate, sd.StartTime, sd.EndTime, sd.CovStartTime, sd.CovEndTime,
sh.CovStartTime AS Expr2, sh.CovEndTime AS Expr3, sd.FillType
FROM SchedData sd INNER JOIN
Shift sh ON sd.ShiftID = sh.ShiftID INNER JOIN
Profile p ON sd.ProfileID = p.ProfileID INNER JOIN
Employee e ON sd.EmployeeID = e.EmployeeID
WHERE (sd.CovStartTime < 0) OR
(sd.CovStartTime > 2359) OR
(sd.CovEndTime < 0) OR
(sd.CovEndTime > 4759)
ORDER BY sd.AssignDate

This query concerns 4 tables in the database called

SchedData which is then given an alias sd
Shift which is then given an alias sh
Profile given p
Employee given e

the value in the column ShiftID, which is in the tables sd (SchedData) and sh (Shift) must be the same
similarly the values of ProfileID in tables sd and p must be the same
similarly the values of EmployeeID in sd and e must be the same
and finally CovStartTime in table sd must be less than 0 OR greater than 2359 OR CovEndTime in the same table must be less than 0 or greater than 4759, 4 possibilities, only 1 needs to be satisfied

the query will return anything from 0 rows to any that match the criteria ordering them by the Assigndate column

In ORacle it would be written so (although recent versions will accept the above AFAIK), so really in zurichuk oracle style it would be written so

SELECT p.ProfileName, e.LastName + ', ' + e.FirstName AS Expr1, e.Abbr, sd.AssignDate, sd.StartTime, sd.EndTime, sd.CovStartTime, sd.CovEndTime,sh.CovStartTime AS Expr2, sh.CovEndTime AS Expr3, sd.FillType
FROM SchedData sd, Shift sh, Profile p, Employee e
WHERE sd.ShiftID = sh.ShiftID
AND sd.ProfileID = p.ProfileID
AND sd.EmployeeID = e.EmployeeID
AND ((sd.CovStartTime < 0) OR
(sd.CovStartTime > 2359) OR
(sd.CovEndTime < 0) OR
(sd.CovEndTime > 4759))
ORDER BY sd.AssignDate