How to return one row based upon date in microsoft sql

Question

I have an internal Department that will be switching account numbers in the future. I am trying to build a stored procedure to return only one of two rows based upon an effective date.

Here's the data makeup

|ID | EffectiveDate | AccountNum |
|-- | ------------- | ---------- |
| 1 |  2021-01-01   |   350000   |
| 2 |  2021-09-01   |   950000   |

I know this returns all the data

SELECT Id, EffectiveDate, AccountNum
        FROM Account

This returns only the first row and never the 2nd:

 SELECT Id, EffectiveDate, DeptNum
    FROM Account 
    WHERE EffectiveDate <= GETDATE()

How would I dynamically return only 1 row based upon today's date?

So if the Effective date is less than today's date get row one. If the Effective Date is equal to or greater than todays date get row two.

select top 1 ... order by EffectiveDate desc?

Charlieface
– Charlieface

2021-03-19 15:46:50 +00:00
Commented Mar 19, 2021 at 15:46 — Charlieface
– Charlieface, Commented Mar 19, 2021 at 15:46

BrewUpCode · Accepted Answer · 2021-03-21 06:08:42Z

0

I figured out the solution. Select only the top 1 deptnum from one of two possible answers given that the EffectiveDate is less than or equal to the current date and use an order by desc too. This ensures that the original EffectiveDate is obtained because the future EffectiveDate has not passed current date. When the future date is equal to or is earlier than the current date then it's value will be returned.

select TOP 1 deptnum
from Account
where EffectiveDate <= GETDATE()
order by EffectiveDate desc

answered Mar 21, 2021 at 6:08

BrewUpCode

113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return one row based upon date in microsoft sql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related