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.
| Author |
Topic |
|
ekellysql
Starting Member
16 Posts |
 Posted - 2006-03-26 : 11:32:46
|
| hey guys, so I wrote a sql to tell me all the customers who made their first purchase within a certain date range. To ensure that the first purchase happened within this date range I use a left outer join to query the transaction table before the start date of my date range to make sure no records exist for said customer before the date range I'm looking at. Here's my sql. It isn't too slow but I just want to know if I'm making it as efficently as I could.SELECT DISTINCT cm.memid, cm.uName, cm.Firstname, cm.lastname, cm.email, cm.madeon, PrePeriodPurCheck.MemId As FoundPriorPurchase FROM clientmems cm INNER JOIN transactiondetails td ON td.memid = cm.memid LEFT OUTER JOIN (SELECT Distinct cms.MemId FROM clientmems cms INNER JOIN transactiondetails td ON td.memid = cms.memid WHERE cms.Bad = 0 AND playcost IS NOT NULL AND (td.trandate < '01/01/06') AND ((td.cc_status = 'B') OR (td.cc_status = 'V')) ) As PrePeriodPurCheck On PrePeriodPurCheck.MemId = cm.MemId WHERE cm.Bad = 0 AND playcost IS NOT NULL AND td.trandate >= '01/01/06' AND td.trandate < '03/26/06' AND ((td.cc_status = 'B') OR (td.cc_status = 'V')) AND PrePeriodPurCheck.MemId IS NULL GROUP BY cm.memid, cm.uName, cm.Firstname, cm.lastname, cm.email, cm.madeon, PrePeriodPurCheck.MemId ORDER BY cm.memid |
|
|
|
|
|
|
|
Exclusion -n- Inclusion of records with sql joins