September 14, 2012 at 10:48 am
Looking to see how to do this type of join.
VaxTable as VT
- table2RecordID
- vaxDate
- customerID
- dollarAmount
CustomerRequestTable as RT
- table2RecordID
- recordCreateDate
- customerID
- dollarAmount
- statusID
CustomerRequestTable contains records manually entered to show that the customer has requested a dollarAmount.
Another system, a VMS, is where the actual dollarAmount "transaction" is posted. The VMS spits out a nightly csv text file of the customerID and dollarAmount; the csv file also has the VMS' datetime stamp for each "transaction" for that day.
I import the csv into VT and then try to match customerID and dollarAmount in VT to RT. However, VT.vaxDate may not be the same date as RT.recordCreateDate, so I can't use the dates as a direct relation there (IssuePoint). When I go beyond 6 days, I get Cartesian results.
For the most part, the relation is working if I limit RT's records to the last 6 days. The reason is a customer may have the same dollarAmount request on a weekly basis. While it is rare, a customer can request the same dollarAmount within those 6 days; as such, I don't get an absolute match.
So, is there a way to "join" on VT.vaxDate and RT.recordCreateDate when they are within 4 days of each other (need to include the variance of a 3-day weekend)? That is, dollarAmount and customerID are the same, but RT.recordCreateDate may be 8/31/12 and VT.vaxDate may be 9/4/12.
September 14, 2012 at 12:48 pm
Gary Noter (9/14/2012)
Looking to see how to do this type of join.VaxTable as VT
- table2RecordID
- vaxDate
- customerID
- dollarAmount
CustomerRequestTable as RT
- table2RecordID
- recordCreateDate
- customerID
- dollarAmount
- statusID
CustomerRequestTable contains records manually entered to show that the customer has requested a dollarAmount.
Another system, a VMS, is where the actual dollarAmount "transaction" is posted. The VMS spits out a nightly csv text file of the customerID and dollarAmount; the csv file also has the VMS' datetime stamp for each "transaction" for that day.
I import the csv into VT and then try to match customerID and dollarAmount in VT to RT. However, VT.vaxDate may not be the same date as RT.recordCreateDate, so I can't use the dates as a direct relation there (IssuePoint). When I go beyond 6 days, I get Cartesian results.
For the most part, the relation is working if I limit RT's records to the last 6 days. The reason is a customer may have the same dollarAmount request on a weekly basis. While it is rare, a customer can request the same dollarAmount within those 6 days; as such, I don't get an absolute match.
So, is there a way to "join" on VT.vaxDate and RT.recordCreateDate when they are within 4 days of each other (need to include the variance of a 3-day weekend)? That is, dollarAmount and customerID are the same, but RT.recordCreateDate may be 8/31/12 and VT.vaxDate may be 9/4/12.
Pretty sparse on the details but I think something like this is what you are looking for.
select [columns]
from Table1 RT
join Table2 VT on RT.RecordDate >= DATEADD(d, -6, VT.vaxDate)
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 14, 2012 at 1:45 pm
Thx for the response.
I did try a variant of what you noted prior to seeing your post and it worked.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply