|
View previous topic :: View next topic
|
| Author |
Message |
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi Team,
Can you please provide me SQL query idea for the following requirement
- Identify the latest Effective date for a given ID number from Table_ A
- Then check in the same table to extract row based on this same ID number with latest Effective date and FLAG_CODE 'A'
The method I have adopted is
SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A WHERE ID_NUM = :DCLGEN-ID-NUM
On successful retrieval of a row, the below mentioned SQL query is executed
SELECT * FROM TABLE_A WHERE ID_NUM = :DCLGEN-ID-NUM
AND EFF_DT = :EFF-DT
AND FLAG_CODE = 'A'
Can you please help me with single SQL query for achieving this requirement
Thanks
Vinu |
|
| Back to top |
|
 |
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
| Have you taken a look at sub queries. I have used then once or twice before. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi Expat,
I did the subquery like below
SELECT * FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM
AND FLAG_CODE = 'A'
AND EFF_DT = (SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM)
I am not sure whether this is correct.
Thanks
Vinu |
|
| Back to top |
|
|
expat
Global Moderator
Joined: 14 Mar 2007
Posts: 8797
Location: Welsh Wales
|
|
|
|
Swift example ...................... which works for me using SAS 
It gets the MAX Round for each ID and then selects records accordingly, so hopefully not too difficult for you to adapt using your own names.
| Code: |
Create table Work.Wombat as
Select Main.*
From
Dataset.Name as Main
,
(
Select ID
, Max(Round) as Mround
From
Dataset.Name
Group by
ID
) as Sub
Where Main.ID = Sub.ID
and Main.Round = Sub.Mround;
|
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
You need correlated query or look for EXISTS.
You need double check , if FLAG_CODE is required to consider while finding the max date else the results are wrong. |
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Hi All,
Only ID number is required while considering latest eff date.
I have tried testing below SQL query and it seems to be working
SELECT * FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM
AND FLAG_CODE = 'A'
AND EFF_DT = (SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM)
This query first gets me latest Eff date for the given ID num, then with that latest eff date it checks whether for the same ID number and Flag _code is 'A'
Whether we are seeing any loop holes here.
Thanks
Vinu |
|
| Back to top |
|
|
sergeyken
Senior Member
Joined: 29 Apr 2008
Posts: 2022
Location: USA
|
|
|
|
| vinu78 wrote: |
Hi Expat,
I did the subquery like below
| Code: |
SELECT * FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM
AND FLAG_CODE = 'A'
AND EFF_DT = (SELECT MAX(EFF_DT) INTO :EFF-DT FROM TABLE_A
WHERE ID_NUM = :DCLGEN-ID-NUM)
|
I am not sure whether this is correct.
Thanks
Vinu |
There is no need trying to save the result of SELECT MAX into any variable in your subquery. Its value is used as result of the subquery itself (as if it was a function call!).
I guess, this idea must be obvious even without reading SQL manuals, isn't it?
P.S.
Use the code tags in your examples. Otherwise 50% of readers would ignore your questions. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3053
Location: NYC,USA
|
|
|
|
| Code: |
SELECT A.* FROM TABLE_A A
WHERE A.ID_NUM = :DCLGEN-ID-NUM
AND A.FLAG_CODE = 'A'
AND A EFF_DT = (SELECT MAX(B.EFF_DT) FROM TABLE_A B
WHERE A.ID_NUM =B.ID_NUM) |
|
|
| Back to top |
|
|
vinu78
Active User
Joined: 02 Oct 2008
Posts: 179
Location: India
|
|
|
|
Thanks very much. This works.
Regards
Vinu |
|
| Back to top |
|
|
|
|
