December 2, 2008 at 10:19 am
Hi.
Not sure if this is the right place to ask or not, so apologies if not.
Basically I have a table, let's just call it data with the following columns.
id - primary key.
cust_id - id of customer in database.
sales_id - id of assigned salesman to customer.
start_date - Date salesman was assigned to customer.
end_date - Date salesman was removed from customer.
A customer can have multiple records in this table for different salesmen and usually only one record with a current salesman in that it will have a start_date but a NULL end date.
We currently have a scenario whereby some of these start and end dates are becoming overlapped or gaps are showing.
eg.
customers salesman 1 (CS1)
start_date: 01-01-2000
end_date: 01-01-2002
customers salesman 2 (CS2)
start_date: 01-01-2001
end_date: NULL
For a customer these should follow so the CS1 end_date should be 01-01-2001
There may also be records where there is a gap end_date of CS1 01-01-2001 start_date of CS2 01-04-2001
Not all customers in the table experience this, and there should never be a gap or an overlap. Not all customers will have more than one record.
I need to write a query to identify the records where there is a gap or an overlap, but I really don't know where to start.
Can anyone offer any help or advice?
Many thanks.
December 2, 2008 at 10:58 am
Hi 4D
This should start you off.
[font="Courier New"]DROP TABLE #Data
CREATE TABLE #Data ([id] INT, cust_id INT, sales_id CHAR(3), start_date DATETIME, end_date DATETIME)
INSERT INTO #Data ([id], cust_id, sales_id, start_date, end_date)
SELECT 1, 1, 'CS1', '01-01-2000', '01-01-2002' UNION ALL
SELECT 1, 1, 'CS2', '01-01-2001', NULL
SELECT a.*, b.*
FROM #Data a
INNER JOIN #Data b ON b.cust_id = a.cust_id
AND b.sales_id <> a.sales_id
AND b.start_date > a.start_date AND b.start_date < a.end_date
[/font]
You can add extra conditions to the INNER JOIN AND b.start_date > a.start_date AND b.start_date < a.end_date
using OR but be sure to use appropriate brackets like this:
AND ((b.start_date > a.start_date AND b.start_date < a.end_date) OR a.start_date IS NULL)
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 3, 2008 at 2:46 am
Thank you very much for that, that has helped a lot.
Joins are certainly not my strongest point. 🙂
Cheers.
December 4, 2008 at 9:26 am
You will not go very far if you do not master the join construct.
I suggest you start with
How to use the LEFT vs. RIGHT OUTER JOIN in SQL
[font="Courier New"]http://searchsqlserver.techtarget.com/generic/0,295582,sid87_gci1286374,00.html#[/font]
How to create a SQL inner join and outer join: Basics to get started
[font="Courier New"]http://searchsqlserver.techtarget.com/tip/0,289483,sid87_gci1340999,00.html?track=NL-417&ad=677258&asrc=EM_NLT_5202719&uid=5360970[/font]
The JOIN is the Cornerstone of Powerful Queries
[font="Courier New"]http://www.google.com/search?hl=en&q=%22The+JOIN+is+the+Cornerstone+of+Powerful+Queries%22&btnG=Google+Search&aq=f&oq=[/font]
and move on to
Hidden RBAR: Triangular Joins
[font="Courier New"]http://www.sqlservercentral.com/articles/T-SQL/61539/[/font]
Also, beware of the effect of the evil-incarnate NULL.
How to join SQL Server tables where columns include NULL values
[font="Courier New"]http://www.mssqltips.com/tip.asp?tip=1447[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply