SQL Server how to select from a table where StartDate and EndDate is within 6 months ago

All we need is an easy explanation of the problem, so here it is.

So I have a table with some columns as listed in the below select statement.

SELECT [AccountName], 
       [AccountNumber], 
       [Tenor], 
       [Currency], 
       [TotalCredit], 
       [TotalDebit], 
       [CreditCount], 
       [DebitCount], 
       [AccountBalance], 
       [StatementStartDate], 
       [StatementEndDate] 
  FROM [TAF.EligibilityService].[dbo].[AccountStatements]

SQL Server how to select from a table where StartDate and EndDate is within 6 months ago

I will like to select records that exist between 6 months ago and today using the StatementStartDate and StatementEndDate.

For example, if the start date is 1 year ago, as long as the end date is still within 6 months ago, the record should be returned.

Thanks in advance.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

This wraps a known value, GETDATE(), in a function, which only requires a single calculation, instead of wrapping the column in a function, which would require evaluation the calculation for every row.

SELECT [AccountName], 
       [AccountNumber], 
       [Tenor], 
       [Currency], 
       [TotalCredit], 
       [TotalDebit], 
       [CreditCount], 
       [DebitCount], 
       [AccountBalance], 
       [StatementStartDate], 
       [StatementEndDate]   
  FROM [TAF.EligibilityService].[dbo].[AccountStatements]
 WHERE StatementStartDate >= dateadd(month, -6, getdate())
    OR StatementEndDate>= dateadd(month, -6, getdate());

Based on indexes, data size and performance needs, splitting the OR into two UNION statements may be the next step. Something like:

SELECT [AccountName], 
       [AccountNumber], 
       [Tenor], 
       [Currency], 
       [TotalCredit], 
       [TotalDebit], 
       [CreditCount], 
       [DebitCount], 
       [AccountBalance], 
       [StatementStartDate], 
       [StatementEndDate]   
  FROM [TAF.EligibilityService].[dbo].[AccountStatements]
 WHERE StatementStartDate >= dateadd(month, -6, getdate())
UNION ALL
SELECT [AccountName], 
       [AccountNumber], 
       [Tenor], 
       [Currency], 
       [TotalCredit], 
       [TotalDebit], 
       [CreditCount], 
       [DebitCount], 
       [AccountBalance], 
       [StatementStartDate], 
       [StatementEndDate]   
  FROM [TAF.EligibilityService].[dbo].[AccountStatements]
 WHERE StatementEndDate>= dateadd(month, -6, getdate());

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply