"John Marchioli" <cake@lenel.com> wrote in message
news:b6462607.0308201219.6dc9c082@posting.google.com...
> Hi folks. I am by no means an SQL guru but I was hoping someone could
> tell if it is possible to answer the following question using the data
> below:
>
> What is the maximum datetime for A_DEVICE = 1 and the maximum datetime
> for A_DEVICE = 2 for each B_BID?
>
> I would also like the output to look like the following:
>
> B_NAME MAXTIME_DEVICE1 MAXTIME_DEVICE2
> ------ --------------------------- ------------------------
> X 2003-02-01 00:00:00.000 2003-04-02 00:00:00.000
> Y 2003-02-01 00:00:00.000 2003-05-01 00:00:00.000
>
> Any help would be greatly appreciated. Thanks.
>
> John
>
> PS I've included the SQL statements to create the tables and data.
>
> A_AID A_TIME A_DEVICEID A_BID
> ----- -------------------------- ----------- ------
> 1 2003-01-01 00:00:00.000 1 1
> 2 2003-02-01 00:00:00.000 1 1
> 3 2003-02-01 00:00:00.000 2 1
> 4 2003-04-02 00:00:00.000 2 1
> 5 2003-01-01 00:00:00.000 2 1
> 6 2003-01-01 00:00:00.000 1 2
> 7 2003-02-01 00:00:00.000 1 2
> 8 2003-05-01 00:00:00.000 2 2
> 9 2003-01-02 00:00:00.000 2 2
> 10 2003-01-01 00:00:00.000 2 2
>
>
> B_BID B_NAME
> ----------- --------------------
> 1 X
> 2 Y
>
> The following is the SQL statements to produce the table and data
> above:
>
> DROP TABLE A
> CREATE TABLE A (A_AID int NOT NULL, A_TIME datetime, A_DEVICEID int,
> A_BID int)
> ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (A_AID)
> DROP TABLE B
> CREATE TABLE B (B_BID int NOT NULL, B_NAME VARCHAR(20))
> ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY (B_BID)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (1,
> '1/1/2003', 1, 1)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (2,
> '2/1/2003', 1, 1)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (3,
> '2/1/2003', 2, 1)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (4,
> '4/2/2003', 2, 1)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (5,
> '1/1/2003', 2, 1)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (6,
> '1/1/2003', 1, 2)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (7,
> '2/1/2003', 1, 2)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (8,
> '5/1/2003', 2, 2)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (9,
> '1/2/2003', 2, 2)
> INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (10,
> '1/1/2003', 2, 2)
>
> INSERT INTO B (B_BID, B_NAME) VALUES (1, 'X')
> INSERT INTO B (B_BID, B_NAME) VALUES (2, 'Y')

"Brooks Stoner [MSFT]" <brookss@online.microsoft.com> wrote in message news:<3f44e6a3$1@news.microsoft.com>...
> Hi John,
>
> Here is one way to get the results you want.
>
> DECLARE @A_DEVICE1 INT
> DECLARE @A_DEVICE2 INT
> SELECT @A_DEVICE1 = 1, @A_DEVICE2 = 2
> SELECT B_NAME,
> MAXTIME_DEVICE1 = (SELECT MAX(A_TIME) FROM A WHERE A_BID = B_BID AND
> A_DEVICEID = @A_DEVICE1),
> MAXTIME_DEVICE2 = (SELECT MAX(A_TIME) FROM A WHERE A_BID = B_BID AND
> A_DEVICEID = @A_DEVICE2)
> FROM B
>
> As you can see, you need to supply the device ID values, but if this were
> going to be in a stored procedure, you could pass those values as
> parameters.
>
> I hope this helps,
>
> Brooks Stoner
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "John Marchioli" <cake@lenel.com> wrote in message
> news:b6462607.0308201219.6dc9c082@posting.google.com...
> > Hi folks. I am by no means an SQL guru but I was hoping someone could
> > tell if it is possible to answer the following question using the data
> > below:
> >
> > What is the maximum datetime for A_DEVICE = 1 and the maximum datetime
> > for A_DEVICE = 2 for each B_BID?
> >
> > I would also like the output to look like the following:
> >
> > B_NAME MAXTIME_DEVICE1 MAXTIME_DEVICE2
> > ------ --------------------------- ------------------------
> > X 2003-02-01 00:00:00.000 2003-04-02 00:00:00.000
> > Y 2003-02-01 00:00:00.000 2003-05-01 00:00:00.000
> >
> > Any help would be greatly appreciated. Thanks.
> >
> > John
> >
> > PS I've included the SQL statements to create the tables and data.
> >
> > A_AID A_TIME A_DEVICEID A_BID
> > ----- -------------------------- ----------- ------
> > 1 2003-01-01 00:00:00.000 1 1
> > 2 2003-02-01 00:00:00.000 1 1
> > 3 2003-02-01 00:00:00.000 2 1
> > 4 2003-04-02 00:00:00.000 2 1
> > 5 2003-01-01 00:00:00.000 2 1
> > 6 2003-01-01 00:00:00.000 1 2
> > 7 2003-02-01 00:00:00.000 1 2
> > 8 2003-05-01 00:00:00.000 2 2
> > 9 2003-01-02 00:00:00.000 2 2
> > 10 2003-01-01 00:00:00.000 2 2
> >
> >
> > B_BID B_NAME
> > ----------- --------------------
> > 1 X
> > 2 Y
> >
> > The following is the SQL statements to produce the table and data
> > above:
> >
> > DROP TABLE A
> > CREATE TABLE A (A_AID int NOT NULL, A_TIME datetime, A_DEVICEID int,
> > A_BID int)
> > ALTER TABLE A ADD CONSTRAINT PK_A PRIMARY KEY (A_AID)
> > DROP TABLE B
> > CREATE TABLE B (B_BID int NOT NULL, B_NAME VARCHAR(20))
> > ALTER TABLE B ADD CONSTRAINT PK_B PRIMARY KEY (B_BID)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (1,
> > '1/1/2003', 1, 1)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (2,
> > '2/1/2003', 1, 1)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (3,
> > '2/1/2003', 2, 1)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (4,
> > '4/2/2003', 2, 1)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (5,
> > '1/1/2003', 2, 1)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (6,
> > '1/1/2003', 1, 2)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (7,
> > '2/1/2003', 1, 2)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (8,
> > '5/1/2003', 2, 2)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (9,
> > '1/2/2003', 2, 2)
> > INSERT INTO A (A_AID, A_TIME, A_DEVICEID, A_BID ) VALUES (10,
> > '1/1/2003', 2, 2)
> >
> > INSERT INTO B (B_BID, B_NAME) VALUES (1, 'X')