0

I have two tables that store similar data but for different time periods:

MainTable: contains records for the current year.

ArchiveTable: contains records from previous years.

Both tables have a column named DateColumn (of type DATETIME). I need to write a T‑SQL query (or set of queries) that behaves as follows based on two input parameters, @StartDate and @EndDate:

Current Year Only: If both dates fall within the current year, fetch data only from MainTable.

Previous Years Only: If both dates are before the current year, fetch data only from ArchiveTable.

Mixed Range: If the date range spans previous years and the current year, fetch data from both tables.

For example, if the current year is 2025:

@StartDate = '2025-03-01' and @EndDate = '2025-03-31' should return rows from MainTable only.

@StartDate = '2024-11-01' and @EndDate = '2024-12-31' should return rows from ArchiveTable only.

@StartDate = '2024-12-15' and @EndDate = '2025-01-15' should return rows from both tables (split at the boundary).

I have considered using IF…ELSE logic to decide which table to query, but I’m looking for advice on the best or most elegant way to implement this (perhaps in a single query, using UNION ALL or dynamic SQL).

What are the best practices or alternative methods for implementing this logic? Can this be achieved in a single query or via other techniques that might simplify maintenance?

Improve this question
3
  • Sample data and expected results would help immensely. Did you try a simple UNION ALL query with separate WHERE filters? Commented Apr 1 at 13:15
  • If, for example, the @StartDate='2025-03-01 00:00:00', @EndDate='2025-06-01 00:00:00', do you want to return all data from the current table, or just the ones falling inside the interval? I also suppose there is going to be additional filtering, right? Commented Apr 1 at 18:57
  • 1
    "@StartDate = '2024-12-15' and @EndDate = '2025-01-15' should return rows from both tables (split at the boundary)." Split at the boundary? What do you mean? Two result set from single query? Commented Apr 1 at 19:00

1 Answer 1

Reset to default
2

single query, using UNION ALL

I think it's the easiest solution.

If you put check constraint to both tables

alter table ArchiveTable add constraint chk_ArchiveTable_DateColumn check(DateColumn<'2025')
alter table MainTable add constraint chk_MainTable_DateColumn check(DateColumn>='2025')

Optimizer could eliminate scanning the other table

check the execution plan

select * from
(
select * from MainTable
union all 
select * from ArchiveTable
) a
where a.DateColumn between '2025-03-01' and '2025-03-31'
Improve this answer
2
  • agree with Sergey, can also be wrapped up in a view to encapsulate the logic Commented Apr 2 at 6:21
  • this worked for me, just had to create index on DateColumn Commented Apr 3 at 12:56

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.