NULL in dimensions: num

Showing posts with label num. Show all posts

Monday, March 26, 2012

number of years,months and days in between two dates

Num of Months in between Date1 '07-05-2005'
and Date2 '07-01-2006' is 11 months 26 days.
SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
is wrong
I want a Query such that It should give number of years,months and days in
between two dates Is that possible ?
Thanks in Advance.
Rajesh.Rajesh
There is no stright forward function. I have written some code for vb. I wil
try to convert into sql. Even there you have many ways of computing it which
depends on your comany policy.
In my company If partial days are there even in Feb we treat thirty days as
month.
"Rajesh" wrote:

> Num of Months in between Date1 '07-05-2005'
> and Date2 '07-01-2006' is 11 months 26 days.
>
> SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
> is wrong
> I want a Query such that It should give number of years,months and days in
> between two dates Is that possible ?
> Thanks in Advance.
> Rajesh.|||RAJESH
Here is VB FUNCTION. You can easily convert into T-sql
but I request you to remember that it depends on a specific method of
calculating days,years and months as per our company policy. you can change
this according your method
---
Option Compare Database
Option Explicit
Function dmy(startdate As Date, enddate As Date, dateval As Integer) As
Integer
Dim StartYear As Integer
Dim StartMonth As Integer
Dim StartDay As Integer
Dim EndYear As Integer
Dim EndMonth As Integer
Dim EndDay As Integer
Dim nomonths As Integer
Dim nodays As Integer
Dim noyears As Integer
Dim temp1 As Date
Dim temp2 As Date
Dim tempdays1 As Integer
Dim tempdays2 As Integer
Dim tempmonths As Integer
Dim enddateofthe_firstdate As Date
Dim enddateofthe_lastdate As Date
Dim td1, td2 As Integer
Dim Onemonthaheadofstartdate As Date
'calculate end date of the month for any date
enddateofthe_firstdate = DateSerial(Year(startdate), Month(DateAdd("m",
1, startdate)), 1) - 1
'calculate end of the Lastdate
enddateofthe_lastdate = DateSerial(Year(enddate), Month(DateAdd("m", 1,
enddate)), 1) - 1
'if month & year of both the dates are same, find out number of days
If (Month(startdate) = Month(enddate)) And (Year(startdate) =
Year(enddate)) And (Day(startdate) <> 1) And (Day(enddateofthe_lastdate) <>
Day(enddate)) Then
nodays = DateDiff("d", startdate, enddate) + 1
nomonths = 0
noyears = 0
Else
'Finding whether full month or not when the same month and year
If (Day(startdate) = 1) And (Day(enddateofthe_lastdate) = Day(enddate))
And (Month(startdate) = Month(enddate)) And (Year(startdate) = Year(enddate)
)
Then
nomonths = 1
nodays = 0
noyears = 0
End If
'1st of following month of start date
temp1 = DateSerial(Year(startdate), Month(startdate) + 1, 1)
'if spanned over two months, three scenarios may occur
Onemonthaheadofstartdate = DateAdd("m", 1, startdate)
If Month(enddate) = Month(Onemonthaheadofstartdate) And Year(enddate) =
Year(Onemonthaheadofstartdate) Then
If enddateofthe_lastdate = enddate And Day(startdate) = 1 Then
nomonths = 2
nodays = 0
Else
If enddateofthe_lastdate = enddate Then
nomonths = 1
nodays = DateDiff("d", startdate, enddateofthe_firstdate) + 1
Else
If Day(startdate) = 1 Then
nomonths = 1
nodays = DateDiff("d", temp1, enddate) + 1
Else
nomonths = 0
nodays = DateDiff("d", startdate, enddate) + 1
End If
End If
End If
If nodays >= 30 Then
nomonths = 1
nodays = nodays - 30
End If
Else
'to find last date of preceedig month if more than one month
temp2 = DateSerial(Year(enddate), Month(enddate), 1) - 1
tempmonths = DateDiff("m", temp1, temp2) + 1
'find out no of days in the start date portion
tempdays1 = Day(enddateofthe_firstdate) - Day(startdate) + 1
'left over days in enddate
tempdays2 = DateDiff("d", DateSerial(Year(enddate), Month(enddate), 1),
enddate) + 1
If Day(startdate) = 1 Then
tempmonths = tempmonths + 1
tempdays1 = 0
End If
If Day(enddate) = Day(enddateofthe_lastdate) Then
tempmonths = tempmonths + 1
tempdays2 = 0
End If
'total no. of days
td1 = tempdays1 + tempdays2
td2 = Int(td1 / 30)
nodays = (td1 - (td2 * 30))
nomonths = tempmonths + td2
If nomonths >= 12 Then
noyears = Int(nomonths / 12)
nomonths = nomonths - noyears * 12
End If
End If
End If
Select Case dateval
Case 1
dmy = nodays
Case 2
dmy = nomonths
Case 3
dmy = noyears
End Select
End Function
----
--
Regards
R.D
"Rajesh" wrote:

> Num of Months in between Date1 '07-05-2005'
> and Date2 '07-01-2006' is 11 months 26 days.
>
> SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
> is wrong
> I want a Query such that It should give number of years,months and days in
> between two dates Is that possible ?
> Thanks in Advance.
> Rajesh.|||Hi Rajesh,
Check out http://www.codeproject.com/csharp/DateTimeLib.asp
Cheers,
JP
----
A program is a device used to convert,
data into error messages
----
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:1DCD1059-FFD0-4296-9552-69C49B9F80B4@.microsoft.com...
> Num of Months in between Date1 '07-05-2005'
> and Date2 '07-01-2006' is 11 months 26 days.
>
> SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
> is wrong
> I want a Query such that It should give number of years,months and days in
> between two dates Is that possible ?
> Thanks in Advance.
> Rajesh.

Monday, March 12, 2012

num to letter

Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
joujThis is a multi-part message in MIME format.
--030202070805000708070300
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
jouj,
You'd probably be better off with an auxiliary table that has the
mappings of number to words since a UDF will either have to spell out
the values to a great degree (a huge case statement) or implement some
fairly complicated string parsing logic, which would slow down any
queries that used your UDF quite considerably.
Consider a table like:
Num Word
-- --
1 One
2 Two
3 Three
etc., etc.
and then just join that table into your queries with a straight-forward
inner join. That would be infinitely more efficient.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:
>Hi all,
>I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
>any help is very appreciated.
>Regards
>jouj
>
>
--030202070805000708070300
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>jouj, 
 
You'd probably be better off with an auxiliary table that has the
mappings of number to words since a UDF will either have to spell out
the values to a great degree (a huge case statement) or implement some
fairly complicated string parsing logic, which would slow down any
queries that used your UDF quite considerably. 
 
Consider a table like: 
 
NumÂ Â Word 
-- -- 
1Â Â Â Â One 
2Â Â Â Â Two 
3Â Â Â Â Three 
 
etc., etc. 
and then just join that table into your queries with a straight-forward
inner join.Â That would be infinitely more efficient. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
jouj wrote:
<blockquote cite="mid49A4EDB6-D88E-47A2-80DA-B135627DB81B@.microsoft.com"
type="cite">
<pre wrap="">Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
jouj
</pre>
</blockquote>
</body>
</html>
--030202070805000708070300--|||Can I get the UDF and work on the performance?
I will use carefully.
--
--
G.Haddad
--
"Mike Hodgson" wrote:
> jouj,
> You'd probably be better off with an auxiliary table that has the
> mappings of number to words since a UDF will either have to spell out
> the values to a great degree (a huge case statement) or implement some
> fairly complicated string parsing logic, which would slow down any
> queries that used your UDF quite considerably.
> Consider a table like:
> Num Word
> -- --
> 1 One
> 2 Two
> 3 Three
> etc., etc.
> and then just join that table into your queries with a straight-forward
> inner join. That would be infinitely more efficient.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
>
> jouj wrote:
> >Hi all,
> >I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
> >any help is very appreciated.
> >
> >Regards
> >jouj
> >
> >
> >
>|||http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
--
David Portas
SQL Server MVP
--|||This is a multi-part message in MIME format.
--020404080608020406020302
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Why are you so keen on writing a UDF? They have their place (a very
small, limited place) in SQL solutions but SQL is a set-based language
and UDFs essentially require row-by-row processing. If you tell us the
problem you're trying to solve we may be able to come up with a better
solution than a UDF (except if the problem is some university tutorial
question that says "write a UDF to convert numbers to their character
representations", in which case shame on you).
If you absolutely *must* have a UDF, here's one I just knocked up
quickly. You'd have to tart it up a little if you wanted niceties like
"and"s or commas (e.g. six thousand, four hundred and fifty two). It
assumes we're talking in base 10 and you have to limit it (I limited it
to 1-999 but it could be very easily extended just by changing the limit
and a bit of cut & paste of the "hundreds" section - obvious).
create function dbo.NumToString (@.n int)
returns varchar(100) as
begin
-- Value to return
declare @.s varchar(100)
-- Set some boundaries
if (@.n < 1 or @.n > 999)
set @.s = null
else
begin
set @.s = ''
-- Calculate the hundreds
if (@.n > 99)
begin
select @.s = case ((@.n/100) % 10)
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end + ' hundred '
end
-- Calculate the tens
if (@.n > 9)
begin
select @.s = @.s +
case ((@.n/10) % 10)
when 0 then ''
when 1 then
case (@.n % 10)
when 0 then 'ten'
when 1 then 'eleven'
when 2 then 'twelve'
when 3 then 'thirteen'
when 4 then 'fourteen'
when 5 then 'fifteen'
when 6 then 'sixteen'
when 7 then 'seventeen'
when 8 then 'eighteen'
when 9 then 'nineteen'
end
when 2 then 'twenty'
when 3 then 'thirty'
when 4 then 'forty'
when 5 then 'fifty'
when 6 then 'sixty'
when 7 then 'seventy'
when 8 then 'eighty'
when 9 then 'ninety'
end + ' '
end
-- Calculate the single digits
if (((@.n/10) % 10) != 1)
begin
select @.s = @.s +
case (@.n % 10)
when 0 then ''
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end
end
end
return (@.s)
end
go
As you can see it's much uglier than
select a.num, b.word from MyTable a
inner join NumWords as b on a.num = b.num
and the performance of it would be dreadful compared to this select
query (assuming you had the NumWords.num column indexed).
I really have to ask again - why does it have to be a UDF?
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:
>Can I get the UDF and work on the performance?
>I will use carefully.
>
--020404080608020406020302
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Why are you so keen on writing a UDF?Â They have their place (a
very small, limited place) in SQL solutions but SQL is a set-based
language and UDFs essentially require row-by-row processing.Â If you
tell us the problem you're trying to solve we may be able to come up
with a better solution than a UDF (except if the problem is some
university tutorial question that says "write a UDF to convert numbers
to their character representations", in which case shame on you). 
 
If you absolutely must have a UDF, here's one I just knocked up
quickly.Â You'd have to tart it up a little if you wanted niceties like
"and"s or commas (e.g. six thousand, four hundred and fifty two).Â It
assumes we're talking in base 10 and you have to limit it (I limited it
to 1-999 but it could be very easily extended just by changing the
limit and a bit of cut & paste of the "hundreds" section - obvious). 
</tt>
<blockquote><tt>create function dbo.NumToString (@.n int) 
returns varchar(100) as 
begin 
Â Â Â -- Value to return 
Â Â Â declare @.s varchar(100) 
 
Â Â Â -- Set some boundaries 
Â Â Â if (@.n < 1 or @.n > 999) 
Â Â Â Â Â Â set @.s = null 
Â Â Â else 
Â Â Â Â Â Â begin 
Â Â Â Â Â Â set @.s = '' 
Â Â Â Â Â Â -- Calculate the hundreds 
Â Â Â Â Â Â if (@.n > 99) 
Â Â Â Â Â Â Â Â Â begin 
Â Â Â Â Â Â Â Â Â select @.s = 
Â Â Â Â Â Â Â Â Â Â Â Â case ((@.n/100) % 10) 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 1 then 'one' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 2 then 'two' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 3 then 'three' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 4 then 'four' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 5 then 'five' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 6 then 'six' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 7 then 'seven' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 8 then 'eight' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 9 then 'nine' 
Â Â Â Â Â Â Â Â Â Â Â Â end + ' hundred ' 
Â Â Â Â Â Â Â Â Â end 
 
Â Â Â Â Â Â -- Calculate the tens 
Â Â Â Â Â Â if (@.n > 9) 
Â Â Â Â Â Â Â Â Â begin 
Â Â Â Â Â Â Â Â Â select @.s = @.s + 
Â Â Â Â Â Â Â Â Â Â Â Â case ((@.n/10) % 10) 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 0 then '' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 1 then 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â case (@.n % 10) 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 0 then 'ten' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 1 then 'eleven' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 2 then 'twelve' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 3 then 'thirteen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 4 then 'fourteen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 5 then 'fifteen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 6 then 'sixteen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 7 then 'seventeen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 8 then 'eighteen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 9 then 'nineteen' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â end 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 2 then 'twenty' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 3 then 'thirty' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 4 then 'forty' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 5 then 'fifty' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 6 then 'sixty' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 7 then 'seventy' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 8 then 'eighty' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 9 then 'ninety' 
Â Â Â Â Â Â Â Â Â Â Â Â end + ' ' 
Â Â Â Â Â Â Â Â Â end 
 
Â Â Â Â Â Â -- Calculate the single digits 
Â Â Â Â Â Â if (((@.n/10) % 10) != 1) 
Â Â Â Â Â Â Â Â Â begin 
Â Â Â Â Â Â Â Â Â select @.s = @.s + 
Â Â Â Â Â Â Â Â Â Â Â Â case (@.n % 10) 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 0 then '' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 1 then 'one' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 2 then 'two' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 3 then 'three' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 4 then 'four' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 5 then 'five' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 6 then 'six' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 7 then 'seven' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 8 then 'eight' 
Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â when 9 then 'nine' 
Â Â Â Â Â Â Â Â Â Â Â Â end 
Â Â Â Â Â Â Â Â Â end 
Â Â Â Â Â Â end 
 
Â Â Â return (@.s) 
end 
go 
</tt></blockquote>
<tt>As you can see it's much uglier than 
</tt>
<blockquote><tt>select a.num, b.word from MyTable a 
Â Â Â inner join NumWords as b on a.num = b.num 
</tt></blockquote>
<tt>and the performance of it would be dreadful compared to this select
query (assuming you had the NumWords.num column indexed). 
 
I really have to ask again - why does it have to be a UDF?</tt> 
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
jouj wrote:
<blockquote cite="midB9DC52BC-1586-4853-8C49-426B2AAF5E08@.microsoft.com"
type="cite">
<pre wrap="">Can I get the UDF and work on the performance?
I will use carefully.
</pre>
</blockquote>
</body>
</html>
--020404080608020406020302--|||This is a multi-part message in MIME format.
--010506010601040406050901
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Is that from Steve Kass? Even that is fairly ugly (no offense Steve).
Better than my 10 minute effort but ugly all the same. I guess I could
have put it in a loop based on the calculated magnitude, concatenating
the magnitude with the digit each iteration of the loop thereby making
the code a little shorter but it still would have been yuk (to use the
technical term). I don't understand why so many people want to use UDFs
to solve their problems; IMHO that's almost always the wrong approach.
Itzik Ben-Gan, of SolidQualityLearning fame
(http://www.solidqualitylearning.com/view.aspx?source=S_BG), is a bit of
a fan of auxiliary tables and I think in this example that would be perfect.
<Mike hops down off his soapbox...>
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Portas wrote:
>http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
>
>
--010506010601040406050901
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Is that from Steve Kass? Even that is fairly ugly (no offense
Steve). Better than my 10 minute effort but ugly all the same. I
guess I could have put it in a loop based on the calculated magnitude,
concatenating the magnitude with the digit each iteration of the loop
thereby making the code a little shorter but it still would have been
yuk (to use the technical term). I don't understand why so many people
want to use UDFs to solve their problems; IMHO that's almost always the
wrong approach. Itzik Ben-Gan, of SolidQualityLearning fame
(<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.solidqualitylearning.com/view.aspx?source=S_BG</a>),">http://www.solidqualitylearning.com/view.aspx?source=S_BG">http://www.solidqualitylearning.com/view.aspx?source=S_BG</a>), is a bit
of a fan of auxiliary tables and I think in this example that would be
perfect. 
 
<Mike hops down off his soapbox...> 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
David Portas wrote:
<blockquote cite="mid96udndwV_op1lQHfRVn-ow@.giganews.com" type="cite">
<pre wrap=""><a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://www.users.drew.edu/skass/sql/NameMoney.sql.txt</a>">http://www.users.drew.edu/skass/sql/NameMoney.sql.txt">http://www.users.drew.edu/skass/sql/NameMoney.sql.txt</a>
</pre>
</blockquote>
</body>
</html>
--010506010601040406050901--|||Dear Mike,
in my application, I need sometimes to convert a number > 1,000,000,000.00.
so is it optimal to save this number of records in a single table?
That's why I needed the UDF.
Thanks anyway.
Regards.
jouj|||This is a multi-part message in MIME format.
--070701040504070200020401
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Admittedly it would make for a rather large table (eg. if you assume the
table has an int & a varchar that averages about 50 bytes per row (just
a guess), for a billion rows you talking about 50G). However, if you
put a clustered index on the int column, the query would only need to do
an index seek to get the relevant string, which would mean somewhere
between 3 - 5 logical reads (ie. the number of levels needed in the
index for that many rows) and that should be lightning fast in
comparison to the UDF and no matter how many rows you add to your
auxiliary table the logical reads would still only be in the order or 4
or 5 or 6.
It would take a long time to populate the table with 1,000,000,000+ rows
but it could be done overnight (or offline) with a script similar to the
UDF code and once it's populated everything would be sweet (assuming you
had the disk space to spare).
Anyway, I understand where you're coming from a bit better now.
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:
>Dear Mike,
>in my application, I need sometimes to convert a number > 1,000,000,000.00.
>so is it optimal to save this number of records in a single table?
>That's why I needed the UDF.
>Thanks anyway.
>Regards.
>jouj
>
>
--070701040504070200020401
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
<title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>Admittedly it would make for a rather large table (eg. if you
assume the table has an int & a varchar that averages about 50
bytes per row (just a guess), for a billion rows you talking about
50G).Â However, if you put a clustered index on the int column, the
query would only need to do an index seek to get the relevant string,
which would mean somewhere between 3 - 5 logical reads (ie. the number
of levels needed in the index for that many rows) and that should be
lightning fast in comparison to the UDF and no matter how many rows you
add to your auxiliary table the logical reads would still only be in
the order or 4 or 5 or 6. 
 
It would take a long time to populate the table with 1,000,000,000+
rows but it could be done overnight (or offline) with a script similar
to the UDF code and once it's populated everything would be sweet
(assuming you had the disk space to spare). 
 
Anyway, I understand where you're coming from a bit better now. 
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
-- 
 mike
hodgson | database administrator
| mallesons stephen jaques 
T +61 (2) 9296 3668 | F +61
(2) 9296 3885 | M +61
(408) 675 907 
E
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> | W <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a> 
</div>
 
 
jouj wrote:
<blockquote cite="mid6FD5ADE9-B94B-4BE8-AE25-0BE66D2A6369@.microsoft.com"
type="cite">
<pre wrap="">Dear Mike,
in my application, I need sometimes to convert a number > 1,000,000,000.00.
so is it optimal to save this number of records in a single table?
That's why I needed the UDF.
Thanks anyway.
Regards.
jouj
</pre>
</blockquote>
</body>
</html>
--070701040504070200020401--

num to letter

Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
joujjouj,
You'd probably be better off with an auxiliary table that has the
mappings of number to words since a UDF will either have to spell out
the values to a great degree (a huge case statement) or implement some
fairly complicated string parsing logic, which would slow down any
queries that used your UDF quite considerably.
Consider a table like:
Num Word
-- --
1 One
2 Two
3 Three
etc., etc.
and then just join that table into your queries with a straight-forward
inner join. That would be infinitely more efficient.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Hi all,
>I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
>any help is very appreciated.
>Regards
>jouj
>
>|||Can I get the UDF and work on the performance?
I will use carefully.
--
--
G.Haddad
--
"Mike Hodgson" wrote:

> jouj,
> You'd probably be better off with an auxiliary table that has the
> mappings of number to words since a UDF will either have to spell out
> the values to a great degree (a huge case statement) or implement some
> fairly complicated string parsing logic, which would slow down any
> queries that used your UDF quite considerably.
> Consider a table like:
> Num Word
> -- --
> 1 One
> 2 Two
> 3 Three
> etc., etc.
> and then just join that table into your queries with a straight-forward
> inner join. That would be infinitely more efficient.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* [url]http://www.mallesons.com[/url
]
>
> jouj wrote:
>
>|||http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
David Portas
SQL Server MVP
--|||Why are you so keen on writing a UDF? They have their place (a very
small, limited place) in SQL solutions but SQL is a set-based language
and UDFs essentially require row-by-row processing. If you tell us the
problem you're trying to solve we may be able to come up with a better
solution than a UDF (except if the problem is some university tutorial
question that says "write a UDF to convert numbers to their character
representations", in which case shame on you).
If you absolutely *must* have a UDF, here's one I just knocked up
quickly. You'd have to tart it up a little if you wanted niceties like
"and"s or commas (e.g. six thousand, four hundred and fifty two). It
assumes we're talking in base 10 and you have to limit it (I limited it
to 1-999 but it could be very easily extended just by changing the limit
and a bit of cut & paste of the "hundreds" section - obvious).
create function dbo.NumToString (@.n int)
returns varchar(100) as
begin
-- Value to return
declare @.s varchar(100)
-- Set some boundaries
if (@.n < 1 or @.n > 999)
set @.s = null
else
begin
set @.s = ''
-- Calculate the hundreds
if (@.n > 99)
begin
select @.s =
case ((@.n/100) % 10)
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end + ' hundred '
end
-- Calculate the tens
if (@.n > 9)
begin
select @.s = @.s +
case ((@.n/10) % 10)
when 0 then ''
when 1 then
case (@.n % 10)
when 0 then 'ten'
when 1 then 'eleven'
when 2 then 'twelve'
when 3 then 'thirteen'
when 4 then 'fourteen'
when 5 then 'fifteen'
when 6 then 'sixteen'
when 7 then 'seventeen'
when 8 then 'eighteen'
when 9 then 'nineteen'
end
when 2 then 'twenty'
when 3 then 'thirty'
when 4 then 'forty'
when 5 then 'fifty'
when 6 then 'sixty'
when 7 then 'seventy'
when 8 then 'eighty'
when 9 then 'ninety'
end + ' '
end
-- Calculate the single digits
if (((@.n/10) % 10) != 1)
begin
select @.s = @.s +
case (@.n % 10)
when 0 then ''
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end
end
end
return (@.s)
end
go
As you can see it's much uglier than
select a.num, b.word from MyTable a
inner join NumWords as b on a.num = b.num
and the performance of it would be dreadful compared to this select
query (assuming you had the NumWords.num column indexed).
I really have to ask again - why does it have to be a UDF?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Can I get the UDF and work on the performance?
>I will use carefully.
>|||Is that from Steve Kass? Even that is fairly ugly (no offense Steve).
Better than my 10 minute effort but ugly all the same. I guess I could
have put it in a loop based on the calculated magnitude, concatenating
the magnitude with the digit each iteration of the loop thereby making
the code a little shorter but it still would have been yuk (to use the
technical term). I don't understand why so many people want to use UDFs
to solve their problems; IMHO that's almost always the wrong approach.
Itzik Ben-Gan, of SolidQualityLearning fame
(http://www.solidqualitylearning.com...spx?source=S_BG), is a bit of
a fan of auxiliary tables and I think in this example that would be perfect.
<Mike hops down off his soapbox...>
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Portas wrote:

>http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
>
>|||Dear Mike,
in my application, I need sometimes to convert a number > 1,000,000,000.00.
so is it optimal to save this number of records in a single table?
That's why I needed the UDF.
Thanks anyway.
Regards.
jouj|||Admittedly it would make for a rather large table (eg. if you assume the
table has an int & a varchar that averages about 50 bytes per row (just
a guess), for a billion rows you talking about 50G). However, if you
put a clustered index on the int column, the query would only need to do
an index seek to get the relevant string, which would mean somewhere
between 3 - 5 logical reads (ie. the number of levels needed in the
index for that many rows) and that should be lightning fast in
comparison to the UDF and no matter how many rows you add to your
auxiliary table the logical reads would still only be in the order or 4
or 5 or 6.
It would take a long time to populate the table with 1,000,000,000+ rows
but it could be done overnight (or offline) with a script similar to the
UDF code and once it's populated everything would be sweet (assuming you
had the disk space to spare).
Anyway, I understand where you're coming from a bit better now.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Dear Mike,
>in my application, I need sometimes to convert a number > 1,000,000,000.00.
>so is it optimal to save this number of records in a single table?
>That's why I needed the UDF.
>Thanks anyway.
>Regards.
>jouj
>
>

num to letter

Hi all,
I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
any help is very appreciated.
Regards
jouj
jouj,
You'd probably be better off with an auxiliary table that has the
mappings of number to words since a UDF will either have to spell out
the values to a great degree (a huge case statement) or implement some
fairly complicated string parsing logic, which would slow down any
queries that used your UDF quite considerably.
Consider a table like:
Num Word
-- --
1 One
2 Two
3 Three
etc., etc.
and then just join that table into your queries with a straight-forward
inner join. That would be infinitely more efficient.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Hi all,
>I need a UDF that converts numbers to letters (ex: 22 -> twenty two).
>any help is very appreciated.
>Regards
>jouj
>
>
|||Can I get the UDF and work on the performance?
I will use carefully.
--
G.Haddad
"Mike Hodgson" wrote:

> jouj,
> You'd probably be better off with an auxiliary table that has the
> mappings of number to words since a UDF will either have to spell out
> the values to a great degree (a huge case statement) or implement some
> fairly complicated string parsing logic, which would slow down any
> queries that used your UDF quite considerably.
> Consider a table like:
> Num Word
> -- --
> 1 One
> 2 Two
> 3 Three
> etc., etc.
> and then just join that table into your queries with a straight-forward
> inner join. That would be infinitely more efficient.
> --
> *mike hodgson* |/ database administrator/ | mallesons stephen jaques
> *T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
> *E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
>
> jouj wrote:
>
|||http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
David Portas
SQL Server MVP
|||Why are you so keen on writing a UDF? They have their place (a very
small, limited place) in SQL solutions but SQL is a set-based language
and UDFs essentially require row-by-row processing. If you tell us the
problem you're trying to solve we may be able to come up with a better
solution than a UDF (except if the problem is some university tutorial
question that says "write a UDF to convert numbers to their character
representations", in which case shame on you).
If you absolutely *must* have a UDF, here's one I just knocked up
quickly. You'd have to tart it up a little if you wanted niceties like
"and"s or commas (e.g. six thousand, four hundred and fifty two). It
assumes we're talking in base 10 and you have to limit it (I limited it
to 1-999 but it could be very easily extended just by changing the limit
and a bit of cut & paste of the "hundreds" section - obvious).
create function dbo.NumToString (@.n int)
returns varchar(100) as
begin
-- Value to return
declare @.s varchar(100)
-- Set some boundaries
if (@.n < 1 or @.n > 999)
set @.s = null
else
begin
set @.s = ''
-- Calculate the hundreds
if (@.n > 99)
begin
select @.s =
case ((@.n/100) % 10)
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end + ' hundred '
end
-- Calculate the tens
if (@.n > 9)
begin
select @.s = @.s +
case ((@.n/10) % 10)
when 0 then ''
when 1 then
case (@.n % 10)
when 0 then 'ten'
when 1 then 'eleven'
when 2 then 'twelve'
when 3 then 'thirteen'
when 4 then 'fourteen'
when 5 then 'fifteen'
when 6 then 'sixteen'
when 7 then 'seventeen'
when 8 then 'eighteen'
when 9 then 'nineteen'
end
when 2 then 'twenty'
when 3 then 'thirty'
when 4 then 'forty'
when 5 then 'fifty'
when 6 then 'sixty'
when 7 then 'seventy'
when 8 then 'eighty'
when 9 then 'ninety'
end + ' '
end
-- Calculate the single digits
if (((@.n/10) % 10) != 1)
begin
select @.s = @.s +
case (@.n % 10)
when 0 then ''
when 1 then 'one'
when 2 then 'two'
when 3 then 'three'
when 4 then 'four'
when 5 then 'five'
when 6 then 'six'
when 7 then 'seven'
when 8 then 'eight'
when 9 then 'nine'
end
end
end
return (@.s)
end
go
As you can see it's much uglier than
select a.num, b.word from MyTable a
inner join NumWords as b on a.num = b.num
and the performance of it would be dreadful compared to this select
query (assuming you had the NumWords.num column indexed).
I really have to ask again - why does it have to be a UDF?
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Can I get the UDF and work on the performance?
>I will use carefully.
>
|||Is that from Steve Kass? Even that is fairly ugly (no offense Steve).
Better than my 10 minute effort but ugly all the same. I guess I could
have put it in a loop based on the calculated magnitude, concatenating
the magnitude with the digit each iteration of the loop thereby making
the code a little shorter but it still would have been yuk (to use the
technical term). I don't understand why so many people want to use UDFs
to solve their problems; IMHO that's almost always the wrong approach.
Itzik Ben-Gan, of SolidQualityLearning fame
(http://www.solidqualitylearning.com/...px?source=S_BG), is a bit of
a fan of auxiliary tables and I think in this example that would be perfect.
<Mike hops down off his soapbox...>
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
David Portas wrote:

>http://www.users.drew.edu/skass/sql/NameMoney.sql.txt
>
>
|||Dear Mike,
in my application, I need sometimes to convert a number > 1,000,000,000.00.
so is it optimal to save this number of records in a single table?
That's why I needed the UDF.
Thanks anyway.
Regards.
jouj
|||Admittedly it would make for a rather large table (eg. if you assume the
table has an int & a varchar that averages about 50 bytes per row (just
a guess), for a billion rows you talking about 50G). However, if you
put a clustered index on the int column, the query would only need to do
an index seek to get the relevant string, which would mean somewhere
between 3 - 5 logical reads (ie. the number of levels needed in the
index for that many rows) and that should be lightning fast in
comparison to the UDF and no matter how many rows you add to your
auxiliary table the logical reads would still only be in the order or 4
or 5 or 6.
It would take a long time to populate the table with 1,000,000,000+ rows
but it could be done overnight (or offline) with a script similar to the
UDF code and once it's populated everything would be sweet (assuming you
had the disk space to spare).
Anyway, I understand where you're coming from a bit better now.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
jouj wrote:

>Dear Mike,
>in my application, I need sometimes to convert a number > 1,000,000,000.00.
>so is it optimal to save this number of records in a single table?
>That's why I needed the UDF.
>Thanks anyway.
>Regards.
>jouj
>
>

num of checks written in 4 day period for more than $400

I have a transaction table which has Date as datetime field, amount and account number. i want to find out count of checks that were written in a period of 4 days which exceeded i.e. > $400, between 401 and 500, > 501 for a single month. the table has data for more than a year and i want the results then grouped in monthly format like in
OCT between 300 & 400 #30 (30 customers gave checks total worth $300-$400 within any 4 consecutive days period in the month of OCT )
between 400 & 500 # 20
> 501 # 10

NOV between 300 & 400 #30
between 400 & 500 # 20
> 501 # 10

and so on for a 6 month period.You will need to use a self join. Something like this:

select Table1.CustomerID, sum(Table2.CheckValue)
from YourTable Table1 inner join YourTable Table2
on Table1.CustomerID = Table2.CustomerID
and Table1.CheckDate > Table2.CheckDate
and datediff(day, Table2.CheckDate, Table1.CheckDate) <= 4
group by Table1.CustomerID
having sum(Table2.CheckValue) > 400

Monday, March 26, 2012

number of years,months and days in between two dates

Monday, March 12, 2012

num to letter

num to letter

num to letter

num of checks written in 4 day period for more than $400

NULL in dimensions

Blog Archive

About Me