How to find clashes - SQL Server Forums

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to find clashes

Author

Topic

vishva
Starting Member

1 Post

Posted - 2014-04-18 : 05:37:14

COURSE_REFERENCE SESSION_ID
---------------- ----------
1781185018 2014-04-18S08.45E10.00
-497192093 2014-04-18S08.45E10.00

In the above table session_id(timings) is same for both courses(1781185018, -497192093), So now i want to find out the clashes as below

COURSE_REFERENCE1 COURSE_REFERENCE2 CLASH_COUNT
---------------- ----------------- -----------
1781185018 -497192093 2

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-04-18 : 06:33:05

[code]

DECLARE @TEMP TABLE(COURSE_REFERENCE INT ,SESSION_ID VARCHAR(MAX))
INSERT INTO @TEMP VALUES(1781185018, '2014-04-18S08.45E10.00'),(-497192093, '2014-04-18S08.45E10.00')

--Method_1 :
SELECT [1781185018] AS CourseID_1,[-497192093] AS CourseID_2,(SELECT count(SESSION_ID) FROM @TEMP WHERE SESSION_ID=SESSION_ID)AS NoOfClashes FROM @TEMP
PIVOT( MAX(COURSE_REFERENCE) FOR COURSE_REFERENCE IN ([1781185018],[-497192093]))Pvt

--Method_2 :
SELECT
T1.COURSE_REFERENCE course_id1,
T2.COURSE_REFERENCE course_id2,
(SELECT count(SESSION_ID) FROM @TEMP WHERE SESSION_ID=SESSION_ID)AS NoOfClashes
FROM @TEMP T1
INNER JOIN @TEMP T2
ON T1.COURSE_REFERENCE < T2.COURSE_REFERENCE
AND T1.SESSION_ID = T2.SESSION_ID

[/code]

---------------
Murali Krishna

You live only once ..If you do it right once is enough.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources