May 30, 2008 at 6:48 am
Hi all,
I need to do a JOIN between tables on a key but also on between certain dates.
The problem is the way the date is recorded.
Here is a simplified example:
TransactionTemplate table:
TemplateID StartDate EndDate PricingFee
1 01/05/2005 31/04/2006 10%
2 01/07/2008 01/01/1900* 20%
*01/01/1900 indicates that EndDate is Not Applicable.
Transaction Table:
TransactionID TemplateID DateApplicable Amount
1 1 20/05/2005 200
2 2 05/07/2009 300
What I have currently is the following:
SELECT *
FROM Transaction
INNER JOIN TransactionTemplate
ON Transaction.TemplateID = TransactionTemplate.TemplateID
AND Transaction.DateApplicable BETWEEN (TransactionTemplate.StartDate) AND (TransactionTemplate.EndDate)
I'm not sure how to deal with the end date set as 01/01/1900? Can I use a CASE in the JOIN ?:unsure:
Any help would be appreciated.
May 30, 2008 at 6:59 am
Yes you can have a CASE statement in the join, but it will hurt performance somewhat. If you're joining two tables, I would consider dealing with the invalid end dates up front (in the table), and fix them first. It's a business decision to make - but in the cases where I wanted an indeterminate end date, I defaulted them to something far into the future or NULL, so that my criteria still work (depending on the crit.).
So I'd probably consider setting those end dates with 1/1/1900 to, say 12/31/2099. That way - no CASE statement.
You have to be careful with BETWEEN and datetime fields. Because datetime holds both date AND time, it can easily get you reporting incorrect info because you're not taking the time component into consideration.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 30, 2008 at 7:02 am
Yes you can use case in the join. A simpler solution would be to use a data out in the future for an end date instead of an historical date. If you are using the datetime datatype your end date could be 9/9/9999 or smalldatetime 1/1/2079.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 30, 2008 at 7:24 am
Thanks for the input guys. Ya the future date seems like a good idea, will bring it up with the higher ups 😉
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply