8bitcat 8bitcat - 7 months ago 19
SQL Question

SQL Server : join records in same table against each other

I have a table which looks like this.

What I want to achieve


  1. I want to select the rows that belong together(according to the rules I have provided)

  2. I then want to search for F type rows with a date bigger than 2016-01-01 and O type rows which has happened under year 2015.



Sample data:

type docnr connection1 connection2 date
F 40195.000000 36950.000000 0.000000 2016-01-29 00:00
O 36950.000000 0.000000 40195.000000 2015-01-29 00:00


As you can see the type of the row determines where the connection to the other lies.


  • TYPE F: Connection to the other row is located in connection2

  • TYPE O: Connection to the other row is located in connection1



If I were to select all row belonging to each other how would I do that?

So far I have tried to create a self reference to the table

SELECT *
FROM
(SELECT
faktoofen.datum1 as faktdatum,
faktoofen.doknr as faktdok,
ordoofen.doknr as orddok,
ordoofen.datum1 as orddatum
FROM
[FTG1].[dbo].[oof] as faktoofen,
[FTG1].[dbo].[oof] as ordoofen
WHERE
(faktoofen.doknr = ordoofen.koppl_dok2
OR ordoofen.doknr = faktoofen.koppl_dok1)
) as subq1
WHERE
YEAR(subq1.orddatum) = '2015'
AND YEAR(subq1.faktdatum) = '2016'


This seems a bit clumsy, I can feel a Union would work here but I can't see how.

Answer

you might be able to do this using a few cte's and UNION ALL

;WITH TypeF AS 
(
    -- F type rows with a date bigger than 2016-01-01
    SELECT * FROM [FTG1].[dbo].[oof] WHERE [type] = 'F' and [date] >= '2016-01-02'
),
TypeO AS
(
    -- O type rows which has happened under year 2015
    SELECT * FROM [FTG1].[dbo].[oof] WHERE [type] = 'O' and YEAR([date]) = 2015
)

--get only F type rows with matching O type records
SELECT  *
FROM    TypeF
WHERE   [connection1] IN (SELECT    [docnr]
                          FROM      TypeO)
UNION ALL

--get only O type rows with matching F type records
SELECT  *
FROM    TypeO
WHERE   [connection2] IN (SELECT    [docnr]
                          FROM      TypeF)
Comments