MSSQL query problem comparing dates

smyers051972
smyers051972 used Ask the Experts™
on
Hi,

I have a query that I am updating my temp table and need to use dates as a comparator.  This mainly is due to foreign exchange rates.  I need to insert the rate of the currency into a temp table based upon the date the document was generated.  Below is my code:

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


I converted to decimal to make sure the rate would be like .7654 and not .765432199984298 as an example.  The problem I am getting is the following error:

Arithmetic overflow error converting float to data type numeric.
The statement has been terminated.


Any help greatly appreciated!

Thanks!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
use

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

Commented:
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

Author

Commented:
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.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Commented:
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

Open in new window

Author

Commented:
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.

Author

Commented:
I get this error on that code:

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

Author

Commented:
I found the issue with the query you posted however im not getting any records updated
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>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:
http://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
Top Expert 2014

Commented:
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)

Open in new window

Author

Commented:
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!
Top Expert 2014

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.

Author

Commented:
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.
Top Expert 2014

Commented:
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

Author

Commented:
can you show me an example? a little lost on that one...
Top Expert 2014

Commented:
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 )
)

Open in new window

Top Expert 2014

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

Author

Commented:
I think there are 10 or so different ones that could be on the report
Top Expert 2014

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

Author

Commented:
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 :)
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
OK.... Think you need something like :


UPDATE #Report -- Rate
SET Rate = (select top 1 cast(Rate as decimal(18,4)) as Rate
                   from pb_exchangeratehistory
                   where #report.docgamingdate > pb_exchangeratehistory.entrygamingdate
--                 and whatever else you need to compare to such as currency codes etc
                   order by pb_exchangeratehistory.entrygamingdate desc )

So, what we are getting is the most recent rate from history before the document date....

So, if history has

01 Jan 2010  1.1111111
05 Jan 2010  1.5555555
09 Jan 2010  1.9999999
21 Jan 2010  2.1111111

and report has document date of 08 Jan 2010
then it will update with 1.5555

and report has document date of 02 Feb 2010
then it will update with 2.1111

Author

Commented:
YES! Thats right!!! :)

Ill test it when i am back in the office tomorrow if thats cool?
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
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...

Author

Commented:
Thank you! This worked perfectly...
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial