May 5, 2008 at 5:49 pm
Hi all,
I have just been handed an existing SQL project despite having no experience in it :w00t:. Naturally I'm bit overwhelmed and could use some help. Enough of my ranting, here's the issue I'm having:
We have a system where we process property information for a real estate company. They send us data, we format it and then send it to various websites...simple in principal. Part of the data that gets sent to us includes info. on land size which is sent to us in Square Metres. However some websites only display the land size value in Square Hectares, therefore we have to convert SqM to SqHA.
This is where the problem is occurring - the SqHA value that is produced by the DB is incorrect, i.e. the value 5000SqM is converted to 50000SqHA, instead of 0.5SqHA. I have checked and confirmed that we are receiving the correct data from the real estate people so there must be an error how the value is been converted. Below is the code that is I am using. Can someone please have a look at it and give me some ideas. Any help will be greatly appreciated.
Thanks
Jarvis
--NOTE: 10,000 SqM = 1 SqHA
CASE
WHEN landareasqha ='' then '0'
WHEN landareasqha is null then '0'
WHEN patindex('%[A-Z]%',landareasqha) =0
and patindex('%.%',landareasqha) >0 then cast(cast(landareasqha as decimal(28,6))*10000 as int)
else cast(cast(isnull(dbo.fnNumbersFromStr(landareasqha),'') as decimal(28,6))*10000 as int)
end [Land Area (Sqm)],
--convert(varchar(8),FloorAreaSqM) AS [Floor Area (sqm)],
convert(varchar(8),LandAreaSqHA) AS [LandArea(Sqm)],
'' AS [Zoning],
'' AS [Yield],
'' AS [CommercialPropertyType],
'' AS [BusinessType],
'NZ' AS [CountryCode],
'N' AS [MultiListIndicator],
May 5, 2008 at 8:28 pm
Hmmm, slight problem here. You describe a problem converting SqM to SqHA:
sidpchandra (5/5/2008)
This is where the problem is occurring - the SqHA value that is produced by the DB is incorrect, i.e. the value 5000SqM is converted to 50000SqHA, instead of 0.5SqHA.
However, the code that you posted for us does the opposite, it converts SqHA to SqM:
CASE
WHEN landareasqha ='' then '0'
WHEN landareasqha is null then '0'
WHEN patindex('%[A-Z]%',landareasqha) =0
and patindex('%.%',landareasqha) >0 then cast(cast(landareasqha as decimal(28,6))*10000 as int)
else cast(cast(isnull(dbo.fnNumbersFromStr(landareasqha),'') as decimal(28,6))*10000 as int)
end [Land Area (Sqm)],
Perhaps this is the problem?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 6, 2008 at 9:24 am
If you change the * to / in the math, that might do what you need.
(As an aside, "square hectares" is kind of redundant, since hectares is directly a measure of surface area. Kind of like "linear kilometers", or "temperature degrees Celcius".)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 3:35 pm
It's more than just the math, Gus. The source and output column names actually indicate that it is going from Hectares to Square Meters.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 7, 2008 at 7:40 am
rbarryyoung (5/6/2008)
It's more than just the math, Gus. The source and output column names actually indicate that it is going from Hectares to Square Meters.
You're right. I didn't read it with enough detail once I saw the backwards math.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply