MSSQL query problem comparing dates

use

UPDATE #Report -- Rate
SET Rate = (select cast(Rate as decimal(8,4)) as Rate from pb_exchangeratehistory where #report.docgamingdate = pb_exchangeratehistory.entrygamingdate)

decimal(4,4) will only convert the decimal part. if the number have a value left to the point then should produce teh error you get... so you should change it to decimal(8,4) or something like that

I did it that way am getting this error:

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.


I tried to perform a distinct select and group by no luck.

try this code... But its highly possible that you have records having the same date... if you  store many currency in this tablle then you should add the money unit to the query to prevent  Subquery returned more than 1 value error.

UPDATE R -- Rate
SET R.Rate = cast(H.Rate as decimal(8,4)
FROM pb_exchangeratehistory H 
		INNER JOIN #Report R ON R.docgamingdate = H.entrygamingdate

Select allOpen in new window

Actually the dates in the two tables are identical I am trying to match a currency rate by date and using the date as a referent, the dates would be the same and the time defaults to midnight on each entry.

I get this error on that code:

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'FROM'.

I found the issue with the query you posted however im not getting any records updated

>however im not getting any records updated
means that there is no match for the join:
>ON R.docgamingdate = H.entrygamingdate

either or both column have then time portions that do not match.
please clarify, after also possibly reading this article:
https://www.experts-exchange.com/articles/Database/Miscellaneous/DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html

Make sure both table has the same datatype,...means one should not have smalldatetime and other datetime...
If the time is default to midnight then try the following... if the following not updating any records means ...I believe both table have different dates...

UPDATE R -- Rate
SET R.Rate = cast(H.Rate as decimal(8,4)  )
FROM pb_exchangeratehistory H  
INNER JOIN #Report R ON convert(char(10),R.docgamingdate,101) = convert(char(10),H.entrygamingdate,101)

Going back to when you changed your original query to use decimal(8,4) and got the "Subquery returned more than 1 value" error, it would appear that the subquery was finding more than one row in pb_exchangeratehistory with the same date.  If so, then the first question is:

 Is that correct, or should there be only one row for each date?

 If there actually could be more than one row for a date, which row do you want to use?  Can you just use TOP 1 to use the first rate you find, or do you need to be more specific?

 If there should be just one row for each date, then I guess you need to figure out why there are more and what you want to do about them.  Should the "extra" rows be deleted?

 If you want to check pb_exchangeratehistory to see if there are any dates with more than one row, you can start with something like this:

select top 10 count (*) as count, entrygamingdate
from pb_exchangeratehistory
group by entrygamingdate
order by count (*) desc


 The TOP 10 is just to limit the result set.  Change as desired.

 James

It might be your alias

UPDATE #Report -- Rate
SET Rate = (select cast(Rate as decimal(4,4)) as RateDec 
From pb_exchangeratehistory 
Where #report.docgamingdate = pb_exchangeratehistory.entrygamingdate)

======== or
UPDATE #Report -- Rate
SET Rate = (select cast(Rate as decimal(4,4))  
From pb_exchangeratehistory 
Where #report.docgamingdate = pb_exchangeratehistory.entrygamingdate)

Select allOpen in new window

I will try the one, but we need to find the rate associated to the date the document was generated, for example, Feb 1st I had a rate in the system as ..5 and on today the rate is .2, but I need to populate the rate that was in effect on feb 1st, that rate might of been put in Jan 23rd.

So we need to pull only the 1 in effect at the time.

I hope that helps!

Thanks!

If you are getting multiple rows returned from your
   (select cast(Rate as decimal(4,4)) ...)
clause, then that is certainly going to cause an error.

I don't know enough about your data to opine further.

both columns being compared are datetime columns and both will have midnight as the time portion.  But as stated above, I am merely looking to get the rate in effect on the date of the document generation.


Document Generated 02-01-2010 for example had the rate of .5 entered into the system on 01-31-2010.
Rate changed 02-02-2010 and todays date is 02-04-2010.

So when the report is run, it needs to know the rate on 02-01-2010 as my example.

Hope this helps.

Opps - further before 01-31 a rate of .4 might of existed before 01-31 and entered into the system 01-28.

There are probably 90 records in the history table.

For clarification, I am also using the following as the type of currency:

EURO                
POUNDS              
CAD

Etc, so if the currency in question was EURO then we would look for EURO.

1. you need to add the currency type in your Where clause
2. you need to qualify the date differently.  You need to match the Max(pb_exchangeratehistory.entrygamingdate) which is <= #report.docgamingdate

can you show me an example? a little lost on that one...

should look something like this:

UPDATE #Report -- Rate 
SET Rate = (select cast(Rate as decimal(4,4))   
From pb_exchangeratehistory  
Where  pb_exchangeratehistory.entrygamingdate = (Select Max(MD.entrygamingdate)
From  pb_exchangeratehistory As MD 
Where  MD.entrygamingdate <= #report.docgamingdate )
)

Select allOpen in new window

NOTE: you will need to add the currency type to this query's conditions

I think there are 10 or so different ones that could be on the report

I'm not sure I understand.  You have one applicable rate for a currency.  Each row in your temporary table represents a single currency.

im pulling that data from a few tables placing into a temp table... the pb_ table is a perm table it has dates that the rate was posted in it, i need to compare the two dates, i hope that helps out :)

YES! Thats right!!! :)

Ill test it when i am back in the office tomorrow if thats cool?

Yep that is fine.... I can even give it a proper test before then :)

Might want to make that "greater than" a "greater than or equal" ie   >=   instead of > when comparing dates...

Thank you! This worked perfectly...

Great news. So very happy it worked for you, and very happy I was able to help.