I have a table which looks like this.
What i want to achieve
I want to select the rows that belong together(according to the rules
I have provided).
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.
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
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
(faktoofen.doknr = ordoofen.koppl_dok2
OR ordoofen.doknr = faktoofen.koppl_dok1)
) as subq1
where YEAR(subq1.orddatum) = '2015'
AND YEAR(subq1.faktdatum) = '2016'
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)