October 6, 2011 at 4:57 am
Hi,
My original query contains many table joins where I need to filter on the returned values. This example contains just 2 tables. Basically from the query below, I need to only return the Last/Greatest/Max Entry_Date for each group-set if found from Table_2 within the last 7 days.
This is a MUCH simplified version, but your appreciated answers will no doubt assist me.
NOTE: The date values are in mdy format. I reset these to dmy before I execute it.
Table_1
Name Last_LO_Date
Neal 10/10/2011 12:00:00 AM
Paul 10/5/2011 12:00:00 AM
Table_2
Name Entry_Date
Neal 10/8/2011 12:00:00 AM
Neal 10/9/2011 12:00:00 AM
Neal 10/6/2011 12:00:00 AM
Paul 10/4/2011 12:00:00 AM
Paul 9/9/2011 12:00:00 AM
My extract query is:
SET DATEFORMAT DMY
SELECT
T1.[Name],
CASE
WHEN T1.Last_LO_Date = T2.Entry_Date --mpk.Activity_Date = (DATEADD(Day, -7, pt.VCTContractDate)
THEN T2.Entry_Date
WHEN DATEADD(Day, -1, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
WHEN DATEADD(Day, -2, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
WHEN DATEADD(Day, -3, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
WHEN DATEADD(Day, -4, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
WHEN DATEADD(Day, -5, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
WHEN DATEADD(Day, -6, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
WHEN DATEADD(Day, -7, T1.Last_LO_Date) = T2.Entry_Date
THEN T2.Entry_Date
END AS Last_Entry
FROM dbo.Table_1 T1 left join dbo.Table_2 T2 ON
T1.[Name] = T2.[Name]
Output:
Name Last_Entry
Neal 2011-10-08 00:00:00
Neal 2011-10-09 00:00:00
Neal 2011-10-06 00:00:00
Paul 2011-10-05 00:00:00
Paul NULL
In this example I only need to return 2 rows, which are:
Neal 2011-10-09 00:00:00
Paul 2011-10-05 00:00:00
NOTE: There are millions of rows to join/search on in several of the tables
Any ideas please?
Thanks in advance,
October 6, 2011 at 5:59 am
SELECT
t1.[Name]
,MAX(t2.Entry_Date) Last_Entry
FROM
Table_1 t1
LEFT JOIN
Table_2 t2 ON t1.[Name] = t2.[Name]
GROUP BY
t1.Name
John
October 6, 2011 at 6:44 am
Thanks John.
I couldn't see the wood-for-the-trees in the larger query.
Regards,
October 6, 2011 at 7:54 am
You don't need to join on Table_1 for this particular query, but that could just be an artifact of the simplified data.
In any case, you might want to group your data BEFORE joining to table_1 rather than after.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 6, 2011 at 8:09 am
That's worth considering. Maybe the query optimizer is smart enough to do that automatically; maybe it isn't. The only way you'll know is by comparing the two actual execution plans when you run the queries against your real data.
John
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy