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

Nested Query Problem

I am writing a stored procedure that has to insert several rows from one table to another. The problem is that the table into which the rows will be inserted, has more columns than the table that the rows come from. When the stored procedure is called, the extra columns in each new row is supposed to be populated by the stored procedure's arguments. Example:

TableA:
Columns: ID, Group, Name, Email, NewletterSubscriber

TableB:
Columns: ID, Name, Email

The arguments provided are Group, and NewsletterSubscriber.

I need to insert into Table A all records from Table B where ID > 1000 and I need to insert the Group and NewsLetterSubscriber arguments at the same time because these columns do not allow nulls.

I think it might be something like

insert into TableA (ID, Group, Name, Email, NewletterSubscriber)
values (
select * from TableB where ID > 1000)

But how do I insert the stored procedure arguments into the correct columns of the rows?

Your insert statement should be something like

insert into TableA(group, newsletterSubscriber)
values (@.arg_group, @.arg_newsletterSubscriber)

|||

As per your example:

You can do the following :

insert TableA (ID, Group, Name, Email, NewletterSubscriber)

select ID,@.Group,Name,Email,@.NewsletterSubscriber from TableB where ID > 1000

Here @.Group and @.NewsletterSubscriber are a arguments from a Stored procedure.

Thanks

Naras.

|||

I think the procedure definition you want is:

Code Snippet

CREATE PROCEDURE TransRecords_AtoB

@.DefGroup AS int,

@.DefNewsLetterSub AS bit

AS

BEGIN

-- Do you need to clear existing records if so uncomment statement below

-- DELETE FROM TableA

-- WHERE [ID] IN (

-- SELECT ID

-- FROM TableB

-- WHERE (ID > 1000)

-- )

-- Insert the required rows

INSERT INTO TableA ([ID], [Group], [Name],

[Email], [NewsLetterSubscriber])

SELECT [ID], @.DefGroup, [Name], [Email], @.DefNewsLetterSub

FROM TableB

WHERE ([ID] > 1000)

END

If you need to remove records already existing for IDs in TableB then use the commented out delete. Change the types of the arguments to match your fields.

I would recomment that you consider changing some of your column names. It is a bad idea to use identifiers that are reserved words (or might become ones). Name and Group fall into that camp and ID is also suspect. These have to be delimited as shown (and this can cause problem with autogenerated SQL in some tools). Use RecID, RecName, RecGroup etc. or something more descriptive.

negative space information

why does sp_spaceused report negative values for space?
exec sp_spaceused ResourceCompetency
name rows reserved
data index_size unused
----
ResourceCompetency 7602 -56 KB
816 KB 656 KB -1528 KBThis happens because of the inaccuracy exists between the sysindexes table.
Run
DBCC UPDATEUSAGE ... WITH COUNT_ROWS
to correct this inaccuracy.
--
-Vishal
"Will Mullen" <will.mullen@.windriver.com> wrote in message
news:01b101c3716c$768e9bd0$a101280a@.phx.gbl...
> why does sp_spaceused report negative values for space?
> exec sp_spaceused ResourceCompetency
> name rows reserved
> data index_size unused
> ----
> ResourceCompetency 7602 -56 KB
> 816 KB 656 KB -1528 KB
>

Need UPDATE query help

I have a table that now holds data about operator actions. The table is
updated each night from a .csv using DTS. The problem is that the rows do no
t
include all the columns I need. I need to caculate a column from two other
rows and add it to the first. For Example, I am trying to add "New Column":
User Encoder1 Encoder2 Action
"New Column"
John 30 26354 7
26398-26354=44
John 30 26354 2
John 30 26398 3
The Encoder1 positions will always be the same as well as the user names
between the three rows. This table is large and I am going to need to change
all of the rows in this table and all the rows that are added each night. Is
this possible? any suggestions would be appreciated
ThanksI will make some guesses about which rows you are manipulating there, but
something like this will work
UPDATE u
SET u.NewColum = A3.Encoder2 - A2.Encoder2
FROM User u
INNER JOIN User A3 ON u.User = A3.User
INNER JOIN User A2 ON u.User = A2.User
WHERE u.User = 'John' AND u.Action = 7 AND A3.Action = 3 AND A2.Action = 2
I am assumingthat for the row with Action 7 you are calculating from tows
with Action 3 and 2. If not, then modify as you se fit, but I think the
query gets you in the right direction. You just need to alias the calls to
the same table to get the various rows.
HTH
John Scragg
"A.B." wrote:

> I have a table that now holds data about operator actions. The table is
> updated each night from a .csv using DTS. The problem is that the rows do
not
> include all the columns I need. I need to caculate a column from two other
> rows and add it to the first. For Example, I am trying to add "New Column"
:
> User Encoder1 Encoder2 Action
> "New Column"
> John 30 26354 7
> 26398-26354=44
> John 30 26354 2
> John 30 26398 3
> The Encoder1 positions will always be the same as well as the user names
> between the three rows. This table is large and I am going to need to chan
ge
> all of the rows in this table and all the rows that are added each night.
Is
> this possible? any suggestions would be appreciated
> Thanks|||>> I have a table that now holds data about operator actions. <<
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
include all the columns I need. I need to caculate a column from two
other rows and add it to the first. <<
Let's get back to the basics of an RDBMS. there is no sequential
access or ordering in an RDBMS, so "first", "next" and "last" are
totally meaningless. If you want an ordering, then you need to havs a
column that defines that ordering in a column.
CREATE TABLE Foobar
(user_name CHAR(15) NOT NULL
encoder1 INTEGER NOT NULL,
encoder2 INTEGER NOT NULL,
foobar_action INTEGER NOT NULL,
PRIMARY KEY ('? ));
Unfortunately, you did not tell how to do the calculation. Would you
like to try again with a usable spec?

Need to transfer certain rows from one table into another table

On my online store, I need to figure out with sql how I can copy data from several different tables into another table:

There is a table that contains all of the customers billing info

another table has customer's shipping info

The target table is for tracking and processing orders. I will need to populate different fields from different tables, as well as insert values into fields from my code behind (i.e. customers can choose different types of shipping, payment options). I googled this and keep running into the join statement (which I use all the time). I don't need to join anything for displaying, I literally need insert data into a column in my orders table, just not sure what the sql syntax is for that.

You can use an "Insert Into" SQL statementsimilar to this:

INSERT INTO MyTable (Name, Description)
SELECT Name, Description
FROM MyOtherTable WHERE ID = 5;