SQL Help.

Question

Post reply

SQL Help.

Viewing 3 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

4D SSC Enthusiast Points: 128 More actions · Answer 2

Thank you very much for that, that has helped a lot.

Joins are certainly not my strongest point. 🙂

Cheers.

J-440512 SSCertifiable Points: 6285 More actions · Answer 3

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]