# MaxDate - Microsoft SQL / MS SQL Server

I have 3 tables: table1: DateMod (smalldatetime) StId Eid table2: DateMod (smalldatetime) StId Wid table3: stid I am selecting the max(Wid) and max(eid) based onstid: select max(Wid),date from table2 a,table 3 where a.stid=b.stid group by a.stid union select max(eid),date from table1 a,table 3 where a.stid=b.stid group by a.stid At this point I need to compare the dates between the results (if sid listed in both results) and select the most resent value from one of the tables for each stid. How do I do this?...

1. ## MaxDate

I have 3 tables:
table1:
DateMod (smalldatetime)
StId
Eid

table2:
DateMod (smalldatetime)
StId
Wid

table3:
stid

I am selecting the max(Wid) and max(eid) based onstid:
select max(Wid),date
from table2 a,table 3
where a.stid=b.stid
group by a.stid
union
select max(eid),date
from table1 a,table 3
where a.stid=b.stid
group by a.stid

At this point I need to compare the dates between the
results
(if sid listed in both results) and select the most resent
value
from one of the tables for each stid.
How do I do this?
dennis Guest

2. ## Re: MaxDate

Please show some sample data and the results you wish to obtain from the
sample data.

Bob Barrows

dennis wrote:
> I have 3 tables:
> table1:
> DateMod (smalldatetime)
> StId
> Eid
>
> table2:
> DateMod (smalldatetime)
> StId
> Wid
>
> table3:
> stid
>
> I am selecting the max(Wid) and max(eid) based onstid:
> select max(Wid),date
> from table2 a,table 3
> where a.stid=b.stid
> group by a.stid
> union
> select max(eid),date
> from table1 a,table 3
> where a.stid=b.stid
> group by a.stid
>
>
> At this point I need to compare the dates between the
> results
> (if sid listed in both results) and select the most resent
> value
> from one of the tables for each stid.
> How do I do this?

Bob Barrows Guest

3. ## Re: MaxDate

For StID='12345' I have records in both tables.
I am selecting max Wid for stid(group by).
Now I need select the max DateMod from both table if it
exists
in both tables (stid) if not just use record with max
(Wid) or (EID).

Table1:
DateMod StId Wid
12/13/2002 12345 9052
12/03/2002 24566 9004
12/16/2002 12345 9072

table2:
DateMod StId Eid
12/12/2002 12345 8001
12/03/2002 24566 8005
12/16/2002 12345 7900

table3:
stid
12345
24566
12345
dennis Guest

4. ## Re: MaxDate

I'm sorry, i still don't understand what the output data is supposed to look
dennis wrote:
> For StID='12345' I have records in both tables.
> I am selecting max Wid for stid(group by).
> Now I need select the max DateMod from both table if it
> exists
> in both tables (stid) if not just use record with max
> (Wid) or (EID).
>
> Table1:
> DateMod StId Wid
> 12/13/2002 12345 9052
> 12/03/2002 24566 9004
> 12/16/2002 12345 9072
>
> table2:
> DateMod StId Eid
> 12/12/2002 12345 8001
> 12/03/2002 24566 8005
> 12/16/2002 12345 7900
>
> table3:
> stid
> 12345
> 24566
> 12345

Bob Barrows Guest

5. ## Re: MaxDate

Table1:
DateMod StId Wid
12/13/2002 12345 9052
12/02/2002 24566 9004
12/16/2002 12345 9072
12/16/2002 22349 7907

table2:
DateMod StId Eid
12/12/2002 12345 8001
12/03/2002 24566 8005
12/16/2002 12345 7900
12/16/2002 22345 7905

table3:
stid
12345
24566
12345
22345

Result:

12345 12/16/2002 7900
24566 12/03/2002 8005
22345 12/16/2002 7900
22349 12/16/2002 7907
dennis Guest

6. ## Re: MaxDate

1. I'm not clear about what table3 has to do with anything.
In table3 I have details about demographic for spesific
stid.
I didn't listed.

StID 22349 exists in Table1, but not in table3,
yet you have a row for it (22349) in your desired
results.
Sorry I foget to add in table 3.
All stid have to exist in table3.

2. A record for StID 12345, 12/16/2002 exists in both
Table1 and Table2.
Your desired result uses the Eid from Table2 (7900)
(9072) from Table1. Why?
Because for stid 12345 the latest transaction date is
12/16/2002.
This is my problem. If I have a record for stid in both
tables select
the latest record based on datemod.

Do you realize that you have a non-normalized database
design?
In each table I have different information and I need to
collect
this info into 1 result.

Thanks
Dennis

dennis Guest

7. ## Re: MaxDate

dennis wrote:
> 1. I'm not clear about what table3 has to do with anything.
> In table3 I have details about demographic for spesific
> stid.
> I didn't listed.
>
> StID 22349 exists in Table1, but not in table3,
> yet you have a row for it (22349) in your desired
> results.
> Sorry I foget to add in table 3.
> All stid have to exist in table3.
OK
>
>
> 2. A record for StID 12345, 12/16/2002 exists in both
> Table1 and Table2.
> Your desired result uses the Eid from Table2 (7900)
> (9072) from Table1. Why?
> Because for stid 12345 the latest transaction date is
> 12/16/2002.
> This is my problem. If I have a record for stid in both
> tables select
> the latest record based on datemod.
>
But they are BOTH the latest record: they both have DateMod = 12/16/2003.
Here's the record from Table1:
12/16/2002 12345 9072

And here's the record from Table2:
12/16/2002 12345 7900

See? Neither qualifies as latest. So we have to choose whether to use 9072
or 7900. How do you want this choice to be made?

Bob Barrows

Bob Barrows Guest

8. ## Re: MaxDate

My Date is set to smalldatetime, so it should take the
correct time if max is used.
Here's the record from Table1:
12/16/2002 12345 9072

And here's the record from Table2:
12/16/2002 12345 7900
dennis Guest

9. ## Re: MaxDate

dennis wrote:
> My Date is set to smalldatetime, so it should take the
> correct time if max is used.
> Here's the record from Table1:
> 12/16/2002 12345 9072
>
> And here's the record from Table2:
> 12/16/2002 12345 7900
You mean the fields contain times as well as dates and you're just not
showing them to me? So what you're telling me is that if you were showing me
the times that are in the fields, one would be more recent than the other?
Well, OK, but it would have been helpful if you had shown me the times to
begin with - I could have had a solution for you a few hours ago ...

I'm going to start by updating the date in my copy of Table2 so it contains
a time making it more recent than the corresponding 12345 record in Table1.

Here is the script I used to create my test tables and data:
CREATE TABLE dbo.Table1
(
DateMod datetime NOT NULL,
StID int NOT NULL,
Wid int NOT NULL
)
go
CREATE TABLE dbo.Table2
(
DateMod datetime NOT NULL,
StID int NOT NULL,
Eid int NOT NULL
)
INSERT INTO Table1 ( DateMod, StID, Wid )
VALUES ( '12/13/2002 12:00:00.000 AM', 12345, 9052 )
go
INSERT INTO Table1 ( DateMod, StID, Wid )
VALUES ( '12/02/2002 12:00:00.000 AM', 24566, 9004 )
go
INSERT INTO Table1 ( DateMod, StID, Wid )
VALUES ( '12/16/2002 12:00:00.000 AM', 12345, 9072 )
go
INSERT INTO Table1 ( DateMod, StID, Wid )
VALUES ( '12/16/2002 12:00:00.000 AM', 22349, 7907 )
go
INSERT INTO Table2 ( DateMod, StID, Eid )
VALUES ( '12/12/2002 12:00:00.000 AM', 12345, 8001 )
go
INSERT INTO Table2 ( DateMod, StID, Eid )
VALUES ( '12/03/2002 12:00:00.000 AM', 24566, 8005 )
go
INSERT INTO Table2 ( DateMod, StID, Eid )
VALUES ( '12/16/2002 12:00:00.000 AM', 22345, 7905 )
go
INSERT INTO Table2 ( DateMod, StID, Eid )
VALUES ( '12/16/2002 02:00:00.000 AM', 12345, 7900 )
go
CREATE TABLE dbo.table3
(
stid int NOT NULL
)
go
INSERT INTO table3 ( stid )
VALUES ( 12345 )
go
INSERT INTO table3 ( stid )
VALUES ( 22345 )
go
INSERT INTO table3 ( stid )
VALUES ( 22349 )
go
INSERT INTO table3 ( stid )
VALUES ( 24566 )
go

Then I'm going to create a couple views to make the final query easier to

create view NormalizedData as
Select DateMod, t1.StID, Wid Value
FROM table1 t1 inner join table3 t3
ON t1.stid = t3.stid
UNION
Select DateMod, t2.StID, Eid
FROM table2 t2 inner join table3 t3
ON t2.StID= t3.StID
go
create view MaxDateMods as
select StID, max(DateMod) maxdate
from NormalizedData
group by StID
go

And here's the final query:

select n.StID,DateMod,[Value]
from NormalizedData n inner join MaxDateMods m
on n.StID= m.StIDand n.DateMod=m.maxdate

This yields these results:
12345 12/16/2002 2:00:00.000 AM 7900
22345 12/16/2002 12:00:00.000 AM 7905
22349 12/16/2002 12:00:00.000 AM 7907
24566 12/3/2002 12:00:00.000 AM 8005

HTH,
Bob Barrows

Bob Barrows Guest

10. ## Re: MaxDate

You're very welcome.

dennis wrote:
> THANK YOU>>> :)

Bob Barrows Guest

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•