I would like if possible, in the query builder, to automatically pull the newest date.
Is that possible and how would I write that criteria?
Solved! Go to Solution.
hey @cowannbell
Would you be able to share some sample data?
You would likely want to want to do something in SQL to achieve this.
I am thinking something like what is described in the attached is what you are looking for:
how do I query sql for a latest record date for each user - Intellipaat Community
Thank you for that.
I've tried it and it works kind of but I only want to group by the date and I get an error when I try that. If I group by the other fields, then I get the latest date for the group. What I want is to return rows that only have the latest date. I hope that makes sense.
Here is SQL STATEMENT
select Max(MEMBERSHIP.EFF_DT),
MEMBERSHIP.GROUP_NBR,
MEMBERSHIP.PBUSGR_CAP_OFF_ID,
MEMBERSHIP.PLAN_DESIGN_CD,
MEMBERSHIP.PRODUCT_LN_CD
from MEMBERSHIP
where MEMBERSHIP.PRODUCT_LN_CD in ('01', '02')
group by MEMBERSHIP.EFF_DT
Adding Order by after Group by should work, remove the Max aggregate function.
If I remove the max, then it's going to pull everything. I only want the query to look at the date field and pull be only the rows where the date field is the most current but I also need it to pull the other fields. I just don't want it to group by the other fields.
@cowannbell what you are missing in the statement is the inner join:
select t.username, t.date, t.value
from MyTable t
inner join (
select username, max(date) as MaxDate
from MyTable
group by username
) tm on t.username = tm.username and t.date = tm.MaxDate
In the example provided in the link, the inner join serves at the filter. You can select the max date in the inner join and join on the date field for your larger data set. Some like this:
select t.MEMBERSHIP.EFF_DT,
t.MEMBERSHIP.GROUP_NBR,
t.MEMBERSHIP.PBUSGR_CAP_OFF_ID,
t.MEMBERSHIP.PLAN_DESIGN_CD,
t.MEMBERSHIP.PRODUCT_LN_CD
from MEMBERSHIP t
inner join (
select max(MEMBERSHIP.EFF_DT) as Max Date from MEMBERSHIP)
on t.MEMBERSHIP.EFF_DT=MEMBERSHIP.EFF_DT
where MEMBERSHIP.PRODUCT_LN_CD in ('01', '02')
group by MEMBERSHIP.EFF_DT
Hope that makes sense.
Thank you for your reply.
I'm trying this and I get this error.
Try removing the GROUP BY at the end.
I realize that the below wasnt properly indexed. If you keep the group by, update the field from MEMBERSHIP_EFF_DT to t.MEMBERSHIP_EFF_DT
group by MEMBERSHIP.EFF_DT
I did try this but that still didn't work. I did find a solution for what I needed. I just used a summary tool and the Max action on the eff date field and then used a join field to join the data with the max date and got what I needed.
Thank you all so much for your assistance.