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