SQL Server – Creating Re-Usable Queries

sql serversql-server-2008-r2t-sql

I have 3 tables with the exact same DDL (I know that is a horrid set-up), but each one has a different values for a field. I am hoping to be able to have a way to write ONE query for all 3 selects as opposed to having to use 3 different select queries. Here is sample DDL and the individual queries, could this be merged into 1 query?

Create Table InfoForJane
(
  genid int IDENTITY(1,1) PRIMARY KEY
  ,name varchar(500)
  ,numofdays varchar(100)
)
Create Table InfoForJoe
(
  genid int IDENTITY(1,1) PRIMARY KEY
  ,name varchar(500)
  ,numofdays varchar(100)
)
Create Table InfoForJessie
(
  genid int IDENTITY(1,1) PRIMARY KEY
  ,name varchar(500)
  ,numofdays varchar(100)
)

And here are the queries that I would ideally like merged into one

Select 
genid
,name
,case
  when numofdays NOT IN ('1-3','4-6','7-9') then '1-3'
  else numofdays
end As [NumofDays]
from rashapa.munich.dbo.InfoForJane

Select
genid
,name
,case
  when numofdays NOT IN ('1-5','6-10','11-14') then '1-5'
  else numofdays
end As [NumofDays]
from rashapa.munich.dbo.InfoForJoe

Select 
genid
,name
,case
  when numofdays NOT IN ('1-2','3-8','8-12') then '1-2'
  else numofdays
end As [NumofDays]
from rashapa.munich.dbo.InfoForJessie

Best Answer

I think you are looking for the "UNION ALL" operator. See the example below. If you are going to be using this a lot, you could even turn it into a view and then query the view. I've added a column "SourceTable" but that is my personal preference when doing things like this, your needs may vary and it may be superfluous for your environment. 

SELECT  genid
      , name
      , SourceTable = 'InfoForJane'
      , CASE WHEN numofdays NOT IN ( '1-3', '4-6', '7-9' ) THEN '1-3'
             ELSE numofdays
        END AS [NumofDays]
FROM    rashapa.munich.dbo.InfoForJane
UNION ALL
SELECT  genid
      , name
      , SourceTable = 'InfoForJoe'
      , CASE WHEN numofdays NOT IN ( '1-5', '6-10', '11-14' ) THEN '1-5'
             ELSE numofdays
        END AS [NumofDays]
FROM    rashapa.munich.dbo.InfoForJoe
UNION ALL
SELECT  genid
      , name
      , SourceTable = 'InfoForJessie'
      , CASE WHEN numofdays NOT IN ( '1-2', '3-8', '8-12' ) THEN '1-2'
             ELSE numofdays
        END AS [NumofDays]
FROM    rashapa.munich.dbo.InfoForJessie;
Related Question