Combining 3 tables in the same database, while limiting the scope of the search time

Mistymanor 0
Viewed 38

Combining 3 tables in the same database: The 3 tables under the same database called Files - the table names im interested in are RXInboundFileQueue, EligibilityFileQueue, and CostFileQueue. All 3 tables share the OriginalName and FileCatalogedOn columns. For an output I'm looking for a query that combines all 3 tables into 1 view and displays each tables files listed within the last 7 days. The code below I wrote outputs every file from each table starting from todays date - but I can't figure a good way to write an outer join to merge these results and only look at a 7 day look behind.

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT
    OriginalName,  
    FileCatalogedOn
FROM RXInboundFileQueue
ORDER BY FileCatalogedOn DESC

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT
    OriginalName,
    FileCatalogedOn
FROM EligibilityInboundFileQueue
ORDER BY FileCatalogedOn DESC

DECLARE @DateFrom DateTime = (SELECT CAST(GetDate() as Date))
DECLARE @DateTo DateTime = (SELECT DATEADD(DAY, 1, @DateFrom))

SELECT
    OriginalName,
    FileCatalogedOn
FROM CostFileInboundFileQueue
ORDER BY FileCatalogedOn DESC
outer-join sql-server sql
1 Answers
SELECT
    OriginalName,  
    FileCatalogedOn,
    'rx' as filetype
FROM RXInboundFileQueue
WHERE FILECatalogedOn >= DATEADD(DAY, -7, GetDate())


UNION ALL

SELECT
    OriginalName,
    FileCatalogedOn,
    'eligibility' as filetype
FROM EligibilityInboundFileQueue
WHERE FILECatalogedOn >= DATEADD(DAY, -7, GetDate())

UNION ALL

SELECT
    OriginalName,
    FileCatalogedOn,
    'costfile' as filetype
FROM CostFileInboundFileQueue
WHERE FILECatalogedOn >= DATEADD(DAY, -7, GetDate())

ORDER BY FileCatalogedOn DESC
Hogan0
Related