• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 368
  • Last Modified:

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
0
Mike Miller
Asked:
Mike Miller
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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'
0
 
Mike MillerSoftware EngineerAuthor Commented:
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
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
waltersnowslinarnoldCommented:
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)
0
 
Mike MillerSoftware EngineerAuthor Commented:
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?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
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'
0
 
Mike MillerSoftware EngineerAuthor Commented:
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
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
>I ONLY want to pull data FROM 12/20/2009
now  i got you, you have to mention this before " data FROM 12/20/2009"

>What is MinTZDt and maxTzDt
seems like you didn't check the query completely, the  MinTZDt, maxTzDt comes from a derived table  
SELECT CardNo , MIN(TZDt) MinTZDt, MAX(TzDt) maxTzDt
FROM Table1
WHERE  TZDT >= '12/20/2009'
Group by CardNo



this is the one you have to run

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 changed '=' with '>='
Group by CardNo ) T1
ON Table2.CardNo = T1.CardNo
WHERE  Table2.Super = 'Smith,Joe'

0
 
Mike MillerSoftware EngineerAuthor Commented:
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.
0
 
Mike MillerSoftware EngineerAuthor Commented:
ok well this works...
WHERE  TZDT > '12/19/2009' AND TZDT < '12/21/2009'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
> 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 " 
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now