1) I'm not going to argue database design, since it seems that it's already in place.
2) Looking at the code, I have to presume that StartOnCallDate, StartOnCallTime, and Duration are Integers. If you're trying to do that with DATETIME, then there are other issues to deal with.
To answer your question...
First off, the math in your where clause is still including the time (hours and seconds). So this stuff:
DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
Gives this result:
SELECT DATEADD(ms , 0 - DATEPART(ms , GETDATE()) ,
DATEADD(s , 0 - DATEPART(s , GETDATE()) , GETDATE()))
-----------------------
2011-08-12 15:35:00.000
So, when you compare DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899') to that mess above, it will almost never be equal. That's because when you use DATEADD(DAY...) to that hard-coded date, you will only end up with values that are days:
SELECT DATEADD(DAY, 40800, '12/31/1899')
-----------------------
2011-09-15 00:00:00.000
To drop the hours and seconds, you could do a bunch more DATEADD stuff, or you could just do something like this:
(SQL Server 2008):
CONVERT (date, GETDATE())
(SQL Server 2005/2008 options):
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)
Those will get you JUST the date. If you throw one of those into your where clause, it will allow you to filter the data, showing you all values that match the current DAY (anything that is going to occur today):
WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME) AND
mOnCallAdd.SchedName = 'arc im'
To give you an idea of the values you are comparing, pull each of those out (with sample data) into a select statement:
SELECT
DATEADD(DAY, 40766, '12/31/1899'),
CAST(CONVERT(VARCHAR(10), getdate(), 101) AS DATETIME)
----------------------- -----------------------
2011-08-12 00:00:00.000 2011-08-12 00:00:00.000
Now, to filter out anything that might have already passed, you want to add a condition to your WHERE clause that checks the ending time to determine if that is earlier that the current time.
DATEADD(MINUTE, mOnCallAdd.duration,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate()
This makes the entire where clause:
WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
DATEADD(MINUTE, mOnCallAdd.duration,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
mOnCallAdd.SchedName = 'arc im'
EDIT:
If you only want items to show up that are currently happening, you need to qualify on both your end time (as above) and your start time. To qualify your start time, you need to get your start time and make sure that the current time is greater than or equal to the current time:
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate()
This makes the entire WHERE clause:
WHERE DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')=
CAST(CONVERT(VARCHAR(10), getdate(), 111) AS DATETIME) AND
DATEADD(MINUTE, mOnCallAdd.duration,
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899'))) > GetDate() AND
DATEADD(MINUTE, mOnCallAdd.StartOnCallTime,
DATEADD(DAY, mOnCallAdd.StartOnCallDate, '12/31/1899')) <= GetDate() AND
mOnCallAdd.SchedName = 'arc im'
Best Answer
How about the following query? This query will take the exchange on the query date if it exists. If not, it will find the most recent exchange date prior to the query date and use that date's exchange rate.
Looking up exchange rates in batch
If you need to find the exchange rate for multiple rows at a time, you can use
CROSS APPLY
to find the most recent exchange rate for each row. If you make sure that you have an appropriate index on(Currency, Exch_date)
that includes theExch_value
column, looking up the exchange rate for each row will perform a single seek with no additional sort needed. You can see a full demonstration in this SQL Fiddle.