Styjun

How to travel between two stationary worlds in the least amount of time? (time dilation)

Show that there are infinitely more problems than we will ever be able to compute

Why would a propellor have blades of different lengths?

Contributing to a candidate as a Foreign National US Resident?

how to convert Timestring to seconds

Has there ever been a cold war other than between the U.S. and the U.S.S.R.?

AB5E type molecule

Book series about using wormholes and time travel to reduce decades of space travel to days

What instances can be solved today by modern solvers (pure LP)?

Campanolo record power meter crank with veloce crankset

Adding gfci reset

administrative duties kill the research spirit?

Do intermediate subdomains need to exist?

What does the ash content of broken wheat really mean?

PhD: When to quit and move on?

Using Sed to add counter to keyword

How can one synthesise a conjugated alkyne chain?

Could you sell yourself into slavery in the USA?

Are there advantages in writing by hand over typing out a story?

Why is the order of my features changed when using readFeatures in OpenLayers v4.6.5?

Park the computer

3D nonogram – What's in the box?

How did שְׁלֹמֹה (shlomo) become Solomon?

What is meant by perfect, imperfect consonance and dissonance?

Select carried over reports

Insert into … values ( SELECT … FROM … )What are the use cases for selecting CHAR over VARCHAR in SQL?How do I perform an IF…THEN in an SQL SELECT?SQL exclude a column using SELECT * [except columnA] FROM tableA?When should I use cross apply over inner join?How do I UPDATE from a SELECT in SQL Server?Select first row in each GROUP BY group?SQL Server SELECT into existing tableSelect statement to find duplicates on certain fieldsGetting error when varchar value used in select query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

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

sql sql-server tsql

share|improve this question

edited Mar 27 at 11:52

CFNinja

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

add a comment | 

0

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

sql sql-server tsql

share|improve this question

edited Mar 27 at 11:52

CFNinja

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

add a comment | 

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

sql sql-server tsql

share|improve this question

edited Mar 27 at 11:52

CFNinja

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

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

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

sql sql-server tsql

sql sql-server tsql

share|improve this question

edited Mar 27 at 11:52

CFNinja

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

share|improve this question

edited Mar 27 at 11:52

CFNinja

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

asked Mar 25 at 18:42

CFNinjaCFNinja

1,80044 gold badges2525 silver badges5454 bronze badges

1 Answer
1

active

oldest

votes

2

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);

share|improve this answer

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  nice :) thank you
  
  – CFNinja
  Mar 25 at 18:55
  

  
  
  
  

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55344515%2fselect-carried-over-reports%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

2

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);

share|improve this answer

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  nice :) thank you
  
  – CFNinja
  Mar 25 at 18:55
  

  
  
  
  

add a comment | 

2

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);

share|improve this answer

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  nice :) thank you
  
  – CFNinja
  Mar 25 at 18:55
  

  
  
  
  

add a comment | 

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);

share|improve this answer

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges

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);

share|improve this answer

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges

answered Mar 25 at 18:48

Yogesh SharmaYogesh Sharma

36.4k55 gold badges1515 silver badges4040 bronze badges