NESTED SQL QUESTION and aggregate function

I have have three tables that I need to pull information from, and
return selected rows. Sorry for the psudeo-code for the tables, but it
should give you an idea of their (simplified) structure:
Inspections (
InspectionID int (PK),
LocationID int (FK),
InspectorID int (FK),
InspectionDate datetime
)
Inspectors (
InspectorID int (PK)
InspectorName varchar(50)
)
Location (
LocationID int (PK)
LocationName varchar(50)
)
Here's the data I need displayed as it would be shown in a "flat"
return:
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101)
ORDER BY p.InspectorName, LastUpdate
would return results:
Joe Inspector DEARBORN 2006-02-09 10:00:07
Joe Inspector DEARBORN 2006-02-09 10:10:04
Joe Inspector DEARBORN 2006-02-09 10:19:19
John Smith ANN ARBOR 2006-02-09 14:20:35
John Smith DEXTER 2006-02-09 14:21:38
Jane Doe CLINTON 2006-02-09 11:40:49
Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
Now, this is what I actually need: I need ONLY the first row (ie,
earliest time of inspection) for EACH inspector, regardless of
location. So my result set would look like:
Joe Inspector DEARBORN 2006-02-09 10:00:07
John Smith ANN ARBOR 2006-02-09 14:20:35
Jane Doe CLINTON 2006-02-09 11:40:49
Another Guy <NULL> <NULL>
Also, if the inspector has no inspections for that day, I would still
like to see the inspector name (note last row)
I'll be passing in the date (not the time) as a parameter.
I have tried using a nested SQL statement with an aggreagate MIN on the
InspectionTime, but I can't seem to get just the FIRST row for each
inspector to display - it will just give me every inspection for that
inspector.
Any help or insight will be greatly appreciated.
Christiani think this will work...
SELECT
p.InspectorName,
l.LocationName,
i.InspectionDate
FROM
Inspectors p
LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
where inspectiondate = (select min(inspectiondate) from inspections
where CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
'02/09/2006', 101) group by inspectorid)
ORDER BY p.InspectorName, LastUpdate
post DDL and insert statement for more clear solutions|||SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName, LocationID
) t1
ON p.InspectorId = t1.InspectorId
INNER JOIN Location l ON t1.LocationId = l.LocationID
ORDER BY p.InspectorName, t1.EaliestInspection
"kaczmar2@.hotmail.com" wrote:

> I have have three tables that I need to pull information from, and
> return selected rows. Sorry for the psudeo-code for the tables, but it
> should give you an idea of their (simplified) structure:
> Inspections (
> InspectionID int (PK),
> LocationID int (FK),
> InspectorID int (FK),
> InspectionDate datetime
> )
> Inspectors (
> InspectorID int (PK)
> InspectorName varchar(50)
> )
> Location (
> LocationID int (PK)
> LocationName varchar(50)
> )
> Here's the data I need displayed as it would be shown in a "flat"
> return:
> SELECT
> p.InspectorName,
> l.LocationName,
> i.InspectionDate
> FROM
> Inspectors p
> LEFT OUTER JOIN Inspections i ON p.InspectorID = i.InspectorID
> LEFT OUTER JOIN Location l ON i.LocationID = l.LocationID
> WHERE CONVERT(CHAR(10), i.InspectionDate, 101) = CONVERT(CHAR(10),
> '02/09/2006', 101)
> ORDER BY p.InspectorName, LastUpdate
> would return results:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> Joe Inspector DEARBORN 2006-02-09 10:10:04
> Joe Inspector DEARBORN 2006-02-09 10:19:19
> John Smith ANN ARBOR 2006-02-09 14:20:35
> John Smith DEXTER 2006-02-09 14:21:38
> Jane Doe CLINTON 2006-02-09 11:40:49
> Jane Doe MOUNT CLEMENS 2006-02-09 11:54:07
> Now, this is what I actually need: I need ONLY the first row (ie,
> earliest time of inspection) for EACH inspector, regardless of
> location. So my result set would look like:
> Joe Inspector DEARBORN 2006-02-09 10:00:07
> John Smith ANN ARBOR 2006-02-09 14:20:35
> Jane Doe CLINTON 2006-02-09 11:40:49
> Another Guy <NULL> <NULL>
> Also, if the inspector has no inspections for that day, I would still
> like to see the inspector name (note last row)
> I'll be passing in the date (not the time) as a parameter.
> I have tried using a nested SQL statement with an aggreagate MIN on the
> InspectionTime, but I can't seem to get just the FIRST row for each
> inspector to display - it will just give me every inspection for that
> inspector.
> Any help or insight will be greatly appreciated.
> Christian
>|||Sorry, that last JOIN should be LEFT, not INNER.
--
"Mark Williams" wrote:
> SELECT p.InspectorName, ISNULL(l.LocationName, 'No Inspection Today'),
> ISNULL(CONVERT(char(20), t1."EarliestInspection", 108), 'No Inspection Tod
ay')
> FROM Inspectors P
> LEFT JOIN
> (
> SELECT InspectorId, LocationID, MIN(InspectionDate) AS "EarliestInspection
"
> FROM Inspections
> WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
> GROUP BY InspectorName, LocationID
> ) t1
> ON p.InspectorId = t1.InspectorId
> INNER JOIN Location l ON t1.LocationId = l.LocationID
> ORDER BY p.InspectorName, t1.EaliestInspection
> --
> "kaczmar2@.hotmail.com" wrote:
>|||Thank you very much for your feedback. This gets me what I want except
for one thing: It shows the earliest time for each location. I want
to show the earliest time for each inspector regardless of location,
but I do want to see the lcoation in the result set. So I can't group
by location. This is your result set:
Tony Inspector ANN ARBOR 16:13:40
Tony Inspector YPSILANTI 17:19:08
Joe Schmoe PLAINWELL 13:12:39
Jane Doe GRAND RAPIDS 11:42:27
Jane Doe GRANDVILLE 12:48:00
Any ideas on how to get the earliest time regardless of location?
Thank you for your continued help.
Mark Williams wrote:
> Sorry, that last JOIN should be LEFT, not INNER.
> --
>
> "Mark Williams" wrote:
>|||Terribly sorry,
SELECT p.InspectorName, ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection Today
')
FROM Inspectors P
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT InspectorId, MIN(InspectionDate) AS "EarliestInspection"
FROM Inspections
WHERE DATEDIFF(dd, InspectionDate, '20060209') = 0
GROUP BY InspectorName
) t1
INNER JOIN
(SELECT i.LocationId, l.LocationName FROM Inspections i
INNER JOIN Locations l ON i.LocationId = l.LocationId) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
"kaczmar2@.hotmail.com" wrote:

> Thank you very much for your feedback. This gets me what I want except
> for one thing: It shows the earliest time for each location. I want
> to show the earliest time for each inspector regardless of location,
> but I do want to see the lcoation in the result set. So I can't group
> by location. This is your result set:
> Tony Inspector ANN ARBOR 16:13:40
> Tony Inspector YPSILANTI 17:19:08
> Joe Schmoe PLAINWELL 13:12:39
> Jane Doe GRAND RAPIDS 11:42:27
> Jane Doe GRANDVILLE 12:48:00
> Any ideas on how to get the earliest time regardless of location?
> Thank you for your continued help.
>
> Mark Williams wrote:
>|||Thank you for the reply. I mad to modify the query since your subquery
returns more than one value. "where inspectiondate = (.." was changed
to "where inspectiondate IN (.."
This looks to give me coreect results except for those inspectors that
did not work that day. I would like to show them in the results with
NULL data.
I have attached the DDL and INSERT statements as requested:
CREATE TABLE [dbo].[Inspections] (
[InspectionID] [int] NOT NULL ,
[LocationID] [int] NOT NULL ,
[InspectorID] [int] NOT NULL ,
[InspectionDate] [datetime] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Inspectors] (
[InspectorID] [int] NOT NULL ,
[InspectorName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Location] (
[LocationID] [int] NOT NULL ,
[LocationName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspections] ADD
CONSTRAINT [PK_Inspections] PRIMARY KEY CLUSTERED
(
[InspectionID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Inspectors] ADD
CONSTRAINT [PK_Inspectors] PRIMARY KEY CLUSTERED
(
[InspectorID]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Location] ADD
CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED
(
[LocationID]
) ON [PRIMARY]
GO
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(1,'Joe Inspector')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(2,'Jane Doe')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(3,'John Smith')
INSERT INTO Inspectors(InspectorID,InspectorName)
VALUES(4,'New Guy')
INSERT INTO Location(LocationID,LocationName)
VALUES(1,'Detroit')
INSERT INTO Location(LocationID,LocationName)
VALUES(2,'Ann Arbor')
INSERT INTO Location(LocationID,LocationName)
VALUES(3,'Royal Oak')
INSERT INTO Location(LocationID,LocationName)
VALUES(4,'Monroe')
INSERT INTO Location(LocationID,LocationName)
VALUES(5,'Dearborn')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(1,1,1,'2/9/2006 9:10 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(2,1,1,'2/9/2006 10:15 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(3,2,2,'2/9/2006 7:30 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(4,3,2,'2/9/2006 11:00 AM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(5,4,3,'2/9/2006 2:00 PM')
INSERT INTO
Inspections(InspectionID,LocationID,Insp
ectorID,InspectionDate)
VALUES(6,5,3,'2/9/2006 1:00 PM')
Thank you very much for your help.|||If you have the proper constraints on your table, you will almost NEVER
use CONVERT()) in a query. Why keep mopping the floor and losing the
ability to use indexes when a simple CHECK() can trim off the time part
of a DATETIME? Fix the leak!
You might also want to learn about ISO-8601 and the SQL Standards for
temporal data, just in case you need to use ISO Standards some day :))
After all, you are one of the few posters lately who actually followed
the ISO-11179 naming conventions!
Your origianl query looks useful in itself, so you might put it in a
VIEW, but this will give you what you asked for
SELECT inspector_name, location_name, MIN(inspection_date)
-- , MAX(inspection_date) could be useful, too!
FROM (SELECT P.inspector_name, L.location_name, I.inspection_date
FROM Inspectors AS P
LEFT OUTER JOIN
Inspections AS I
ON P.inspector_id = I.inspector_id
LEFT OUTER JOIN
Locations AS L
ON I.location_id = L.Location_id
WHERE I.inspection_date '2006-02-09')
AS X(inspector_name, location_name, inspection_date)
GROUP BY X.inspector_name, X.location_name;|||Mark-
Thank you very much, this worked! I just had to tweak one of the
joins. Here is the cleaned up, final query:
SELECT
p.InspectorName,
ISNULL(t3.LocationName, 'No Inspection Today'),
ISNULL(CONVERT(char(20), t3."EarliestInspection", 108), 'No Inspection
Today')
FROM Inspectors p
LEFT JOIN
(
SELECT t1.InspectorId, t1.EarliestInspection, t2.LocationName
FROM
(
SELECT i.InspectorId, MIN(i.InspectionDate) AS "EarliestInspection"
FROM Inspections i
WHERE DATEDIFF(dd, i.InspectionDate, '20060209') = 0
GROUP BY i.InspectorId /* name */
) t1
INNER JOIN
(
SELECT i.LocationId, l.LocationName, i.InspectionDate, i.InspectorID
-- added
FROM Inspections i
INNER JOIN Location l ON i.LocationId = l.LocationId
) t2
ON t1.InspectorId = t2.InspectorId AND t1.EarliestInspection =
t2.InspectionDate
) t3
ON t3.InspectorId = p.InspectorId
Thanks again for your help!!!|||CELKO-
Thank you for your input. I probably did not explain my complete
intentions when you saw the CONVERT function used to strip of the time.
I do need the time information, but I need to group by the day for
this query, that is why I was using convert. And yes, I probably
should be aware of my naming conventions/syntax. Note that I posted
abriged tables/data/queries to simplify the example to the group.sql