July 4, 2014 at 7:47 am
I have this Date Values (DateFrom, DateUntil) selected, which lie in the current Date Range - or in the future.
Now I would like to have:
- If getdate () between Date From and DateUntil, then take this.
- If there is only future values, then take the most obvious diagnosis suggests most value.
---------------------------------
DateFrom DateUtil
2011-09-01 2014-08-31
2014-09-01 2014-10-31
2014-11-01 2015-06-30
2015-07-01 NULL
----------------------------------
Regards
Nicole
[:)]
July 4, 2014 at 7:51 am
So, based on your posted data. What's the result that you're expecting?
July 4, 2014 at 7:59 am
info 58414 (7/4/2014)
I have this Date Values ??(DateFrom, DateUntil) selected, which lie in the current Date Range - or in the future.Now I would like to have:
- If getdate () between Date From and DateUntil, then take this.
- If there is only future values??, then take the most obvious diagnosis suggests most value.
---------------------------------
DateFrom DateUtil
2011-09-01 2014-08-31
2014-09-01 2014-10-31
2014-11-01 2015-06-30
2015-07-01 NULL
----------------------------------
Regards
Nicole
[:)]
What determines that the row is the "most obvious diagnosis suggests most value"? All you've give us to work with here is "DateFrom" and "DateUtil".
Solving the first part of your issue is easy enough. First, we'll put your sample data in to a better format: -
IF OBJECT_ID('tempdb..#testEnvironment') IS NOT NULL
BEGIN;
DROP TABLE #testEnvironment;
END;
SELECT DateFrom,
DateUtil
INTO #testEnvironment
FROM ( VALUES ( '2011-09-01', '2014-08-31'),
( '2014-09-01', '2014-10-31'),
( '2014-11-01', '2015-06-30'),
( '2015-07-01', NULL)
) a ( DateFrom, DateUtil );
Now any of the unpaid volunteers on this site can execute the above and have a copy of your sample data stored in a temporary table, which makes it much easier to test and develop a solution 🙂
Next, we look at a query: -
SELECT *
FROM [#testEnvironment] AS te
WHERE te.DateFrom <= GETDATE()
AND te.DateUtil >= GETDATE();
Which returns: -
DateFrom DateUtil
---------- ----------
2011-09-01 2014-08-31
The next thing to ask yourself is, "how many results are your expecting?". From the way you worded your original question, it sounds like you always want to return something. So how do we determine what to return?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply