Subscribe To

Subscribe to SQL Server and XML by Email
Labels

Friday, February 27, 2009

TSQL Challenge 1

Moved to http://beyondrelational.com/tc/challenge1

3 comments:

  1. My answer :

    SELECT a.Code, a.aname, bc.bname, bc.cname FROM @a a
    LEFT OUTER JOIN
    (
    SELECT b.bname , c.cname, b.RowN, c.RowN AS cRowN,
    CASE WHEN b.Code IS NOT NULL THEN b.Code ELSE c.Code END as Code
    FROM ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS RowN FROM @b b) b
    FULL OUTER JOIN ( SELECT * , ROW_NUMBER() OVER(PARTITION BY Code ORDER BY Code) AS RowN FROM @c c) c
    ON b.Code = c.Code
    AND b.RowN = c.RowN
    ) bc
    On a.Code = bc.Code

    ReplyDelete
  2. kwangsa,
    Thank you for submitting your entry. The tricky part of this query is to make it work in SQL Server 2000. Your query uses ROW_NUMBER() that is available only in SQL Server 2005.

    I will post the next challenge in a day or two.

    regards
    Jacob

    ReplyDelete
  3. I'm sure there's a far shorter way of doing it, but this only uses standard SQL :
    ----------------------------------

    DECLARE @d TABLE (code INT, aname VARCHAR(10), bname VARCHAR(10), cname VARCHAR(10))

    DECLARE @q1b AS INT
    DECLARE @q1c AS INT
    DECLARE @q2 AS VARCHAR(10)
    DECLARE @q3 AS VARCHAR(10)
    DECLARE @q4 AS VARCHAR(10)

    SET @q1b = (SELECT TOP 1 a.code FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
    SET @q1c = (SELECT TOP 1 a.code FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))

    WHILE @q1b IS NOT NULL AND @q1c IS NOT NULL
    BEGIN

    SET @q1b = (SELECT TOP 1 a.code FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
    SET @q1c = (SELECT TOP 1 a.code FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))

    IF (@q1b IS NOT NULL AND @q1b < @q1c) OR @q1c IS NULL
    INSERT INTO @d
    SELECT TOP 1 @q1b,a.aname,b.bname,NULL
    FROM @a AS a, @b AS b
    WHERE a.code = b.code
    AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname)
    ELSE
    IF @q1b IS NULL OR (@q1c IS NOT NULL AND @q1c < @q1b)
    INSERT INTO @d
    SELECT TOP 1 @q1c,a.aname,NULL,c.cname
    FROM @a AS a, @c AS c
    WHERE a.code = c.code
    AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname)
    ELSE
    BEGIN
    SET @q2 = (SELECT TOP 1 a.aname FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
    SET @q3 = (SELECT TOP 1 b.bname FROM @a AS a, @b AS b WHERE a.code = b.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.bname = b.bname))
    SET @q4 = (SELECT TOP 1 c.cname FROM @a AS a, @c AS c WHERE a.code = c.code AND NOT EXISTS (SELECT * FROM @d AS d WHERE d.code = a.code AND d.cname = c.cname))
    INSERT INTO @d SELECT @q1c,@q2,@q3,@q4
    END
    END

    INSERT INTO @d
    SELECT a.code,a.aname,NULL,NULL
    FROM @a AS a
    WHERE NOT EXISTS (SELECT * FROM @b AS b WHERE b.code = a.code)
    AND NOT EXISTS (SELECT * FROM @c AS c WHERE c.code = a.code)

    SELECT * FROM @d

    ReplyDelete

About Me
Jacob Sebastian
Tutorials
* XQuery Tutorials
My Links
SQL Server White Papers
SQL Server 2008
My Articles
XML Workshop RSS Feed
Contact me
Readers
Free Hit Counter
Web Site Hit Counters
SQL Server Bloggers
Blog Directories
blogarama - the blog directory Programming Blogs - BlogCatalog Blog Directory
 
Copyright Jacob Sebastian