abs786123 abs786123 - 2 months ago 7
SQL Question

referential Integrity and complicated joins

A27,346467413,68723601
A28,346467414,68723601
A28,346467416,68723601
A27,349454768,8908697809
A28,349454769,8908697809
A28,349454771,8908697809


I have the above sample from a table I need to do some mind boggling sql on. Basically whats been asked of me is that for each A27, two proceeding A28 belong to that A27 and the information need to be tied together. However, I have been asked to create a table for each A_Number so A27 will be in its own table and A28 will be in its own table two. What I need to do is do a Join on them so each A27 can link to its corresponding A28 that follow after it. Just wondering how I would go about it if it were in separate tables and what the Join and SQL would look like? Keep in mind non of the data makes any sense as its invoice reconciliation stuff.

enter image description here

I have uploaded the sample file attached

enter image description here

Answer
;WITH cte AS (
    SELECT
       *
       ,ROW_NUMBER() OVER (ORDER BY Column2) AS RowNumber
    FROM
       @Table
)

SELECT
     *
    ,DENSE_RANK() OVER (ORDER BY A27.Column2) as GroupingId
FROM
    cte A27
    LEFT JOIN cte A28
    ON A28.RowNumber IN (a27.RowNumber + 1, a27.RowNumber + 2)
    AND A28.Column1 = 'A28'
WHERE
    A27.Column1 = 'A27'

Because based on your image Column3 isn't unique to only the row numbers you want I didn't go the route of using it. Instead I used a ROW_NUMBER based on Column2 which appears to be an increment primary key and then simply LEFT SELF JOIN. This is for the 2 A28 rows AFTER each A27 Row. if you want "preceding" like your OP says but doesn't suggest then simply reverse it by changing the JOIN condition:

ON A28.RowNumber IN (a27.RowNumber - 1, a27.RowNumber - 2)

Note I did add a GroupingID to give you way of identifying which A27 and A28's are together it could be used as a new primary key in your A27 table and a foreign key in your A28 table to preserve the relationship.

Here is the test data I used.

DECLARE @Table AS TABLE (Column1 CHAR(3), Column2 BIGINT, Column3 BIGINT)
INSERT INTO @Table VALUES
('A27',346467408,68723601)
,('A28',346467409,68723601)
,('A28',346467411,68723601)
,('A27',346467413,68723601)
,('A28',346467414,68723601)
,('A28',346467416,68723601)
,('A27',349454768,8908697809)
,('A28',349454769,8908697809)
,('A28',349454771,8908697809)

Edit: As per your comment of how to do it when in separate tables. You can use a union all to combine the tables:

DECLARE @A27 AS TABLE (Column1 CHAR(3), Column2 BIGINT, Column3 BIGINT)
DECLARE @A28 AS TABLE (Column1 CHAR(3), Column2 BIGINT, Column3 BIGINT)
INSERT INTO @A27 VALUES
('A27',346467408,68723601)
,('A27',346467413,68723601)
,('A27',349454768,8908697809)
INSERT INTO @A28 VALUES
('A28',346467409,68723601)
,('A28',346467411,68723601)
,('A28',346467414,68723601)
,('A28',346467416,68723601)
,('A28',349454769,8908697809)
,('A28',349454771,8908697809)

;WITH cteUnion AS (
    SELECT
       Column1, Column2, Column3
    FROM
       @A27

    UNION ALL

    SELECT
       Column1, Column2, Column3
    FROM
       @A28
)

, cteRowNum AS (
    SELECT * ,ROW_NUMBER() OVER (ORDER BY Column2) AS RowNumber
    FROM
       cteUnion
)

SELECT *, DENSE_RANK() OVER (ORDER BY a27.Column2) as GroupingId
FROM
    cteRowNum a27
    LEFT JOIN cteRowNum a28
    ON a28.RowNumber IN (a27.RowNumber + 1, a27.RowNumber + 2)
    AND a28.Column1 = 'A28'
WHERE
    a27.Column1 = 'A27'

If the tables are larger or performance may be an issue you might want to build a temp table and do it that way.

IF OBJECT_ID('tempdb..#Combined') IS NOT NULL
    BEGIN
        DROP TABLE #Combined
    END

CREATE TABLE #Combined (
    TableName CHAR(3)
    ,Column2 BIGINT
)

INSERT INTO #Combined (TableName, Column2)
SELECT Column1, Column2
FROM
    @A27

INSERT INTO #Combined (TableName, Column2)
SELECT Column1, Column2
FROM
    @A28

;WITH cteRowNum AS (
    SELECT * ,ROW_NUMBER() OVER (ORDER BY Column2) AS RowNumber
    FROM
       #Combined
)

SELECT *, DENSE_RANK() OVER (ORDER BY a27.Column2) as GroupingId
FROM
    cteRowNum a27
    LEFT JOIN cteRowNum a28
    ON a28.RowNumber IN (a27.RowNumber + 1, a27.RowNumber + 2)
    AND a28.TableName = 'A28'
WHERE
    a27.TableName = 'A27'
Comments