asked on

2 Table SQL Join with Min/Max DateTime

I would like 2 join two table from the same database.

Table1 has the following columns:
TZDT, CARDNO

Table2 has the following columns
FirstName, LastName, CARDNO2, Super

Table1 will have many entries of the same CARDNO with different values for TZDT which is Date/Time
Table2 will just have one entry for CARDNO2

Here is what I would like to display:

Table2.FirstName +' '+ Table2.LastName AS FLNAME
MIN(Table1.TZDT)
MAX(Table1.TZDT)
WHERE Table1.TZDT = '12/20/2009'
AND Table2.Super = 'Smith,Joe'
AND Table1.CARDNO = Table2.CARDNO2

For example....
Table1:

TZDT CARDNO
11/10/2009 8:15:00 72654
11/15/2009 8:15:00 72654
12/20/2009 8:15:00 72654
12/20/2009 9:20:00 72654
12/20/2009 9:45:00 72654
12/20/2009 10:30:00 72654
12/31/2009 8:15:00 72654

Table2:
FirstName LastName CARDNO2 Super
Mike Jones 72654 Smith,Joe
Steve Smith 00001 Barnes,Rick
Chris Brown 00002 Brown,Mac
James Miller 00003 Applewhite,Major

The query should return
Mike Jones 12/20/2009 8:15:00 12/20/2009 10:30:00

How do I do this?
TIA

Raja Jegan R

This should do:

select Table2.FirstName +' '+ Table2.LastName AS FLNAME,
MIN(Table1.TZDT) as min_value,
MAX(Table1.TZDT) as max_value
FROM table1 inner join table2 on table1.CARDNO2 = table2.CARDNO2
WHERE Table1.TZDT = '12/20/2009'
AND Table2.Super = 'Smith,Joe'
AND Table1.CARDNO = Table2.CARDNO2
group by Table2.FirstName +' '+ Table2.LastName

Aneesh

select Table2.FirstName +' '+ Table2.LastName AS FLNAME, MinTZDt, maxTzDt
FROM Table2
INNER JOIN
(SELECT CardNo , MIN(TZDt) MinTZDt, MAX(TzDt) maxTzDt
FROM Table1
WHERE t1.TZDT = '12/20/2009'------------ i dont think you need this,
Group by CardNo ) T1
ON Table2.CardNo = T1.CardNo
WHERE Table2.Super = 'Smith,Joe'

Member_2_1242703

ASKER

aneeshattingal i'm not even sure what you're trying to do there.

rrjegan17 your query is not returning anything although after double checking I'm certain there is data in there that should appear in the result

waltersnowslinarnold

Use the following code..

SELECT firstname+' '+lastname, MIN(Table1.TZDT), MAX(Table1.TZDT) FROM table2 INNER JOIN table1 ON table2.CARDNO2 IN (SELECT table1.CARDNO FROM table1)

Member_2_1242703

ASKER

walter...ok that returned data but when I added in my where clauses it returned nothing. Maybe I am putting them in the wrong place. Could you complete your query?

Aneesh

did you try the query i posted , you dont really need that where condition (if you put that date filter out there , both minimum and the max date will be '12/20/2009'

select Table2.FirstName +' '+ Table2.LastName AS FLNAME, MinTZDt, maxTzDt
FROM Table2
INNER JOIN
(SELECT CardNo , MIN(TZDt) MinTZDt, MAX(TzDt) maxTzDt
FROM Table1
WHERE TZDT = '12/20/2009'------------ i dont think you need this,
Group by CardNo ) T1
ON Table2.CardNo = T1.CardNo
WHERE Table2.Super = 'Smith,Joe'

Member_2_1242703

ASKER

annee
first how does this work?
select Table2.FirstName +' '+ Table2.LastName AS FLNAME, MinTZDt, maxTzDt
What is MinTZDt and maxTzDt
TZDT is in table1

and I do need the where clause. there are several other dates in my sample and I ONLY want to pull data from 12/20/2009

I will try it now though

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Member_2_1242703

ASKER

Yours also returns no data HOWEVER, When I remove this line
WHERE TZDT >= '12/20/2009'

It ALMOST returns the correct data. The min and max are wrong obviously because it is picking the first date and last date in the table.

Member_2_1242703

ASKER

ok well this works...
WHERE TZDT > '12/19/2009' AND TZDT < '12/21/2009'

Aneesh

> The min and max are wrong obviously because it is picking the first date and last date in the table.
I hope you dodnt miss the " GROUP BY part "