Max of Date with Criteria... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Max of Date with Criteria...

Author

Topic

Vassago
Starting Member

33 Posts

Posted - 2005-12-27 : 18:08:25

I am very new to SQL coding, but have a lot of experience in Access. Please bare with me as I try to explain my situation.

I have five tables, as follow:

ACCOUNTS:
accountnum, typeid
11111, 01
22222, 02
33333, 01
44444, 01
55555, 02

TYPES:
typeid, typedes
01, Type1
02, Type2
03, Type3

TRANSACTIONS:
tranid, accountnum, trandate, trantype
001, 55555, 12/22/05, 147
002, 22222, 12/23/05, 148
003, 11111, 12/22/05, 147
004, 11111, 12/22/05, 147
005, 33333, 12/23/05, 147
006, 22222, 12/22/05, 147
007, 11111, 12/23/05, 147
008, 11111, 12/22/05, 147
009, 22222, 12/22/05, 147
010, 11111, 12/22/05, 147
011, 11111, 12/23/05, 147
012, 55555, 12/22/05, 148
013, 22222, 12/23/05, 147
014, 33333, 12/25/05, 148

ORIGINFO
accountnum, originid
11111, 03
33333, 01
44444, 02

ORIGTYPES:
originid, origindes
01, Origin1
02, Origin2
03, Origin3

The table named ACCOUNTS has every account listed on it. I want to create a query with the following fields based on the data from the above tables.

accountnum, typedes, typeid, origindes, originid, lasttran148date

My biggest problem is trying to write the last field, lasttran148date. I want sql to look on TRANSACTIONS for the last time a trantype value of 148 appears on the list, and fill in the trandate as lasttran148date where trantype was 148. This is my code so far:

Select ACCOUNTS.accountnum, TYPES.typedes, ACCOUNTS.typeid, ORIGTYPES.origindes, ORIGINFO.originid
from ACCOUNTS
left join TYPES on ACCOUNTS.accountnum = TYPES.accountnum
left join ORIGINFO on ACCOUNTS.accountnum = ORIGINFO.accountnum
left join ORIGTYPES on ORIGINFO.originid = ORIGTYPES.originid

Can anyone give me a suggestion for that last field? Also, are my joins correct or should I use a different type of join to get what I'm looking for?

All help is greatly appreciated.

Vassago

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 18:42:11

Check the following:

Select ACCOUNTS.accountnum, TYPES.typedes, ..... ,
lasttran148date =
Case
When trantype = 148 then trandate
End
from ACCOUNTS
left join TYPES .....

Vassago
Starting Member

33 Posts

Posted - 2005-12-27 : 19:00:14

quote:
Originally posted by Srinika

Check the following:

Select ACCOUNTS.accountnum, TYPES.typedes, ..... ,
lasttran148date =
Case
When trantype = 148 then trandate
End
from ACCOUNTS
left join TYPES .....

Thanks! That almost gives me what I need, only it doesn't give me that max of that field. It must be something simple, but how can I obtain the max of that field?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 19:49:20

Max of which field?
I guess Max of trandate!!
Try
When trantype = 148 then (Select Max(trandate) from .. where trantype = 148)

Vassago
Starting Member

33 Posts

Posted - 2005-12-27 : 20:25:47

quote:
Originally posted by Srinika

Max of which field?
I guess Max of trandate!!
Try
When trantype = 148 then (Select Max(trandate) from .. where trantype = 148)

Yes, I meant the trandate. Thanks for the reply. It's still not giving me what I need. This is what it's giving me as the results with this code:

11111, Null
11111, Null
11111, Null
11111, Null
11111, Null
11111, Null
22222, Null
22222, 12/23/05
22222, Null
22222, Null
33333, Null
33333, 12/25/05
55555, Null
55555, 12/22/05

and this is what I want:

11111, Null
22222, 12/23/05
33333, 12/25/05
44444, Null
55555, 12/22/05

Do you have any other suggestions?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 20:41:06

Try Inner Join instead of Left Join

Vassago
Starting Member

33 Posts

Posted - 2005-12-27 : 20:47:10

quote:
Originally posted by Srinika

Try Inner Join instead of Left Join

Inner join gives the same results...here is my code...maybe I'm not following you completely. Please take a look:

SELECT ACCOUNTS.accountnum, lasttran148date =
Case
When trantype = 148 then (Select Max(trandate) from TRANSACTIONS where trantype = 148)
End
from ACCOUNTS
inner join TRANSACTIONS on ACCOUNTS.accountnum = TRANSACTIONS.accountnum

I appreciate all of your help so far. Maybe you can find something wrong with the above code to cause it to give me those results.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 21:01:09

Inner join all :
.....
Inner join TYPES on ACCOUNTS.accountnum = TYPES.accountnum
Inner join ORIGINFO on ACCOUNTS.accountnum = ORIGINFO.accountnum
Inner join ORIGTYPES on ORIGINFO.originid = ORIGTYPES.originid

Vassago
Starting Member

33 Posts

Posted - 2005-12-27 : 21:08:49

quote:
Originally posted by Srinika

Inner join all :
.....
Inner join TYPES on ACCOUNTS.accountnum = TYPES.accountnum
Inner join ORIGINFO on ACCOUNTS.accountnum = ORIGINFO.accountnum
Inner join ORIGTYPES on ORIGINFO.originid = ORIGTYPES.originid

Sorry. I left the other stuff off for now and decided to just go with ACCOUNTS and TRANSACTIONS until I get the lasttran148 working. Do you see anything wrong with my code that would make it give me those above results?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 21:23:34

Sorry Vassago,
So far I was answering without having any access to a Database!!
Hence its very hard for me to try out some other way!!
For another week that will be the case.
Hope somebody else might help u, from there!!

Until u get some help from somebody, keep on experimenting.
Good Luck

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2005-12-27 : 21:34:42

Just remembered, check putting Distinct keyword in front of the list of items as :

SELECT Distinct ACCOUNTS.accountnum, lasttran148date =
Case
When trantype = 148 then (Select Max(trandate) from TRANSACTIONS where trantype = 148)
End

Vassago
Starting Member

33 Posts

Posted - 2005-12-27 : 21:56:16

quote:
Originally posted by Srinika

Just remembered, check putting Distinct keyword in front of the list of items as :

SELECT Distinct ACCOUNTS.accountnum, lasttran148date =
Case
When trantype = 148 then (Select Max(trandate) from TRANSACTIONS where trantype = 148)
End

It's now giving me one record with a null value for the lasttran148date, and one record with what appears to be the lasttrandate, so we are close... any ideas on how to get rid of the null results if a 148 exists for that particular account?

yonabout
Posting Yak Master

112 Posts

Posted - 2005-12-28 : 09:30:27

Hi,

I've got the results you were after by aliasing the table and getting the max values for each account type. Probably not the best way of doing it, but an option:

select 
accountnum,
max(lasttran148date) as date148

from

(

Select 
distinct(ACCOUNTS.accountnum), 
TYPES.typedes, 
ACCOUNTS.typeid, 
ORIGTYPES.origindes, 
ORIGINFO.originid,
Case 
When trantype = 148 then (Select Max(trandate) from TRANSACTIONS where trantype = 148)
End as lasttran148date

from ACCOUNTS
left join TYPES on ACCOUNTS.typeid = TYPES.typeid
left join ORIGINFO on ACCOUNTS.accountnum = ORIGINFO.accountnum
left  join ORIGTYPES on ORIGINFO.originid = ORIGTYPES.originid
left join transactions on transactions.accountnum = accounts.accountnum) as account

group by accountnum

which using your test data returns


11111      NULL
22222      2005-12-25 00:00:00.000
33333      2005-12-25 00:00:00.000
44444      NULL
55555      2005-12-25 00:00:00.000

Cheers,

Yonabout

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources