Select carried over reports

How do I get the carried over reports + the very first report from the following table?

Result should be : 304, 306, 309, and 312.

CREATE TABLE [dbo].[test](
[reportID] [int] NOT NULL,
[caseID] [int] NOT NULL,
[carriedOver] [bit] NULL,
[oldReportID] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (304, 4, 1, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (305, 4, 0, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (306, 4, 1, 304)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (309, 4, 1, 306)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES 
(311, 4, 0, NULL)
GO
INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES 
(312, 4, 1, 309)
GO

After a little bit of tinkering, the solution needed some tweaking when there is only one caseID.

INSERT [dbo].[test] ([reportID], [caseID], [carriedOver], [oldReportID]) 
VALUES (100, 1, 0, NULL)

GO

declare @caseID int = 1
SELECT t.reportID, tr.*, t.*
FROM dbo.test t
INNER JOIN  (  SELECT ROW_NUMBER() OVER (ORDER BY reportID) AS RowNum, 
               reportID
               FROM dbo.test
               WHERE caseID = @caseID  ) tr on tr.reportID = t.reportID
 WHERE ( exists ( SELECT 1 FROM dbo.test t1 
                  WHERE t1.reportID = t.oldReportID 
                  and t1.caseID = @caseID ) or

          exists ( SELECT 1 FROM dbo.test t2 
                   WHERE t2.oldReportID = t.reportID 
                   and t2.caseID = @caseID  ) or

          tr.rowNum < 2 )
 and caseID = @caseID
 ORDER BY 1 asc

Answer

You can use EXISTS :

select t.*
from test t 
where exists (select 1 from test t1 where t.oldreportid = t1.reportid) or
      exists (select 1 from test t1 where t1.oldreportid = t.reportid);

Advertisement

Answer