finding length of text field

I need to find the length of a text field.The len function doesn't work with
text fields.
Is there an alternative?
Thanks!DATALENGTH()
HP wrote:
> I need to find the length of a text field.The len function doesn't work wi
th
> text fields.
> Is there an alternative?
> Thanks!|||Look in the BOL for DATALENGTH
HTH, jens Suessmeyer.|||HP (HP@.discussions.microsoft.com) writes:
> I need to find the length of a text field.The len function doesn't work
> with text fields.
As Jens and Trey said there is datalength().
In case you are using ntext, beware that datalength() returns the length
in bytes, and includes trailing blanks. For instance:
SELECT len(N'August '), datalength(N'August ')
Returns (6, 18).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Look in the BOL for DATALENGTH.
HTH, Jens Suessmeyer.

Finding End of Month

Does anyone have a VB function they could pass along for
converting a date to the end of the last month?
I have a report that will be run for the period ending the prior
month and want the parameter date to default as follows:
Assum they are running it on 12/21, date should default to 11/30/2005
Thanks!"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
@.TK2MSFTNGP15.phx.gbl:
> Does anyone have a VB function they could pass along for
> converting a date to the end of the last month?
> I have a report that will be run for the period ending the prior
> month and want the parameter date to default as follows:
> Assum they are running it on 12/21, date should default to 11/30/2005
> Thanks!
>
>
DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Outstanding, many thanks!
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||Asher
I am using timedate format; can it be set to the end of the month 11:59 pm?
"Asher_N" <compguy666@.hotmail.com> wrote in message
news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)|||If your using 2005, you use my misc date project at
http://channel9.msdn.com/ShowPost.aspx?PostID=147390
Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
hour:minute:second:millisecond of that day (i.e. adding one more ms would
move the date to start of next day.)
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
> Asher
> I am using timedate format; can it be set to the end of the month 11:59
> pm?
> "Asher_N" <compguy666@.hotmail.com> wrote in message
> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>|||William
Thanks for your reply but I an still on RS 2000
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
> If your using 2005, you use my misc date project at
> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
> hour:minute:second:millisecond of that day (i.e. adding one more ms would
> move the date to start of next day.)
> --
> William Stacey [MVP]
> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>|||I missed the VB function need. Here is a c# method you can convert:
public static DateTime GetEndOfMonth(DateTime date)
{
int daysInMonth = DateTime.DaysInMonth(date.Year, date.Month);
return new DateTime(date.Year, date.Month, daysInMonth, 23, 59,
59, 999);
}
private void button9_Click(object sender, EventArgs e)
{
DateTime date = DateTime.Parse("12/22/2005"); // Any date.
DateTime lastMth = date.AddMonths(-1);
DateTime endOfLast = GetEndOfMonth(lastMth);
Console.WriteLine("End of Last Month:" + endOfLast);
}
--
William Stacey [MVP]
"Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
news:unxvFipBGHA.3496@.TK2MSFTNGP11.phx.gbl...
> William
> Thanks for your reply but I an still on RS 2000
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:eNviqHpBGHA.3896@.TK2MSFTNGP09.phx.gbl...
>> If your using 2005, you use my misc date project at
>> http://channel9.msdn.com/ShowPost.aspx?PostID=147390
>> Then use GetEndOfMonth(DateTime) UDF. This gives you the last day and
>> hour:minute:second:millisecond of that day (i.e. adding one more ms would
>> move the date to start of next day.)
>> --
>> William Stacey [MVP]
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in message
>> news:%235%236n4nBGHA.216@.TK2MSFTNGP15.phx.gbl...
>> Asher
>> I am using timedate format; can it be set to the end of the month 11:59
>> pm?
>> "Asher_N" <compguy666@.hotmail.com> wrote in message
>> news:Xns9733AA89568F9compguy666hotmailcom@.207.46.248.16...
>> "Mike Harbinger" <MikeH@.Cybervillage.net> wrote in news:u#uZqUnBGHA.516
>> @.TK2MSFTNGP15.phx.gbl:
>> Does anyone have a VB function they could pass along for
>> converting a date to the end of the last month?
>> I have a report that will be run for the period ending the prior
>> month and want the parameter date to default as follows:
>> Assum they are running it on 12/21, date should default to 11/30/2005
>> Thanks!
>>
>>
>> DateAdd(DateInterval.Day, (DatePart(DateInterval.Day, Now)) * -1, Now)
>>
>>
>

finding bad date

I have a database where a varchar field is being used to store dates. I wrot
e
the following function to check if the data is a valid date. If it is not a
valid date, Null is returned.
ALTER function f_ToDate(@.inDate varchar(50))
returns datetime
begin
declare @.returndate datetime
if isdate(@.inDate) = 0
select @.returndate = null
else
select @.returndate = convert(datetime,@.inDate,101)
return @.returndate
end
In my database, I have a table called Generation with a field called
CreateDate. When I run the following query, I get an error message stating
that “syntax error converting datetime from character string”
select dbo.f_ToDate(Generation.CreationDate) from Generation
To check, I ran the following query and it returned all null records which
is expected
select CreationDate from general where isdate(CreationDate) = 0
The following returns no records which is expected.
select CreationDate from general where isdate(CreationDate) = 0 and
CreationDate is not Null.
I can’t find the record that is causing “select
dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.I have had a similar issue w/ Dates. A quick and dirty way that I used was t
o
export out the records using DTS to a table that mirrored the working one.
Only change I made was to change the varchar date field to a true datetime
field in the temp table. In my dts package I set the max number of errors to
the max (999) and started the export.
In my case I had 632 fewer records in my temp table after the export. I ran
a NOT EXISTS against the tables and was able to find the 632 records w/ bad
dates in my production table.
If the number of records w/ bad dates is GT 999, you will have to do the
export in bacthes to find all the bad recs.
Just a thought!
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Emma,
The convert function with style 101 is supposed to be used when converting
from datetime to char or varchar. Can you execute:
select case when isdate(CreationDate) = 0 then null else convert(datetime,
CreationDate)
from Generation
AMB
"Emma" wrote:

> I have a database where a varchar field is being used to store dates. I wr
ote
> the following function to check if the data is a valid date. If it is not
a
> valid date, Null is returned.
> ALTER function f_ToDate(@.inDate varchar(50))
> returns datetime
> begin
> declare @.returndate datetime
> if isdate(@.inDate) = 0
> select @.returndate = null
> else
> select @.returndate = convert(datetime,@.inDate,101)
> return @.returndate
> end
>
> In my database, I have a table called Generation with a field called
> CreateDate. When I run the following query, I get an error message stating
> that “syntax error converting datetime from character string”
> select dbo.f_ToDate(Generation.CreationDate) from Generation
> To check, I ran the following query and it returned all null records which
> is expected
> select CreationDate from general where isdate(CreationDate) = 0
> The following returns no records which is expected.
> select CreationDate from general where isdate(CreationDate) = 0 and
> CreationDate is not Null.
> I can’t find the record that is causing “select
> dbo.f_ToDate(Generation.CreationDate) from Generation” to fail.
>|||Thanks Jay for your response. I did just what you said. The number of record
s
exported to the temp table was the same as in my original table, and the
query work on the temp table and not on the original. I can’t figure it ou
t.
"Jay Kusch" wrote:
> I have had a similar issue w/ Dates. A quick and dirty way that I used was
to
> export out the records using DTS to a table that mirrored the working one.
> Only change I made was to change the varchar date field to a true datetime
> field in the temp table. In my dts package I set the max number of errors
to
> the max (999) and started the export.
> In my case I had 632 fewer records in my temp table after the export. I ra
n
> a NOT EXISTS against the tables and was able to find the 632 records w/ ba
d
> dates in my production table.
> If the number of records w/ bad dates is GT 999, you will have to do the
> export in bacthes to find all the bad recs.
> Just a thought!
> "Emma" wrote:
>|||Thanks. That worked.
"Alejandro Mesa" wrote:
> Emma,
> The convert function with style 101 is supposed to be used when converting
> from datetime to char or varchar. Can you execute:
> select case when isdate(CreationDate) = 0 then null else convert(datetime,
> CreationDate)
> from Generation
>
> AMB
> "Emma" wrote:
>

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
Why is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
MG
|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
David Portas
sql

finding a row number

Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MGWhy is that important? Rows are in a table in no particular order. For it
to have meaning, the ROW_NUMBER() function has to have an ORDER BY
associated with it.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
Is there a function or something similar in sql 2005 that can tell you what
row a record is in a table without any indexes?
For example, if I tried to execute something like
select row_number() from mytable where id = '30532'
and the record were the 100th record, I'd get back a result of 100.
--
MG|||Wrong newsgroup but you could try
SELECT *
FROM
(
SELECT row_number() OVER (ORDER BY column_your_dbtable_is_sortedby
asc) as rownum, id
FROM mytable
) a
WHERE id = '30532'
"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG|||"Hurme" <michael.geles@.thomson.com> wrote in message
news:59CFB5F8-0709-4FB7-B178-4127F99A2FDC@.microsoft.com...
> Is there a function or something similar in sql 2005 that can tell you
> what
> row a record is in a table without any indexes?
> For example, if I tried to execute something like
> select row_number() from mytable where id = '30532'
> and the record were the 100th record, I'd get back a result of 100.
> --
> MG
Tables have no order so there is no such thing as a determinate row number.
"100th record" is arbitrary and meaningless unless you state some ordering
based on the data itself.
If you just want some arbitrary row to call the "100th" you can just do TOP
1:
SELECT TOP 1 col1, col2, col3
FROM mytable;
--
David Portas

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, and
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way in
SQL Server to locate or capture a query that is submitted by a third party
software package?
Scott Ford
Information Services
Starlite Entertainment
Scott Ford wrote:
> I have a retail software that has a reporting function for its sales. It has
> a SQL Server 2000 database. The table links are extremely hard to define, and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.
|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:

> Scott Ford wrote:
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, and
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way in
SQL Server to locate or capture a query that is submitted by a third party
software package?
--
Scott Ford
Information Services
Starlite EntertainmentScott Ford wrote:
> I have a retail software that has a reporting function for its sales. It has
> a SQL Server 2000 database. The table links are extremely hard to define, and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
--
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:
> Scott Ford wrote:
> > I have a retail software that has a reporting function for its sales. It has
> > a SQL Server 2000 database. The table links are extremely hard to define, and
> > I want to be able to write custom reports. If I can see the query that it
> > submits I can figure out how to make my own reports for it. Is there a way in
> > SQL Server to locate or capture a query that is submitted by a third party
> > software package?
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>

Finding a query submitted by software

I have a retail software that has a reporting function for its sales. It has
a SQL Server 2000 database. The table links are extremely hard to define, an
d
I want to be able to write custom reports. If I can see the query that it
submits I can figure out how to make my own reports for it. Is there a way i
n
SQL Server to locate or capture a query that is submitted by a third party
software package?
--
Scott Ford
Information Services
Starlite EntertainmentScott Ford wrote:
> I have a retail software that has a reporting function for its sales. It h
as
> a SQL Server 2000 database. The table links are extremely hard to define,
and
> I want to be able to write custom reports. If I can see the query that it
> submits I can figure out how to make my own reports for it. Is there a way
in
> SQL Server to locate or capture a query that is submitted by a third party
> software package?
I'd use SQL Profiler - it captures all queries.
Start profiler with recording the profiler data into a table. Use the
software then stop the profiler. Then analyze the profiler log.|||Thanks Yuras .. I had just discovered the Profiler minutes before your post,
but it helps to have verification that this is the tool I need.
--
Scott Ford
Information Services
Starlite Entertainment
"Yuras" wrote:

> Scott Ford wrote:
> I'd use SQL Profiler - it captures all queries.
> Start profiler with recording the profiler data into a table. Use the
> software then stop the profiler. Then analyze the profiler log.
>

Financial function

Hello,

I seek a solution for the cacule of YIELD TO MATURITY of the bonds. In Excel av YIELD() but SQL server ?

Do you have an idea?

Thank you

I do not know the exact logic of the function but you should be able to code the same in a UDF or procedure in SQL Server. In SQL Server 2005, you can implement the same using C#/VB.NET function/procedure. Alternatively, you can invoke the Excel function via OLE automation or pass-through query provided you have to data in a Excel sheet. This approach however is not that pretty and probably overkill.

Anyway, what is the reason for trying to implement the function in SQL Server? You can get the data from SQL Server into Excel easily and perform the computation there. You can then persist results back to the database if you want to. And you can automate this whole process using say DTS or SSIS package for example.

|||SET NOCOUNT ON
CREATE TABLE Cash_Flows
(
cashflow DECIMAL(8,2),
valuta DATETIME
)
INSERT INTO Cash_Flows VALUES (-95.92,'20060115')
INSERT INTO Cash_Flows VALUES (5,'20070115')
INSERT INTO Cash_Flows VALUES (5,'20080115')
INSERT INTO Cash_Flows VALUES (5,'20090115')
INSERT INTO Cash_Flows VALUES (105,'20100115')

GO

CREATE FUNCTION yield_to_maturity(@.issue_date SMALLDATETIME)
RETURNS DECIMAL(15,14)
AS BEGIN
DECLARE @.ytm_tmp FLOAT
DECLARE @.ytm FLOAT
DECLARE @.pv_tmp FLOAT
DECLARE @.pv FLOAT

SET @.ytm_tmp = 0
SET @.ytm = 0.1
SELECT @.pv_tmp = SUM(cashflow) FROM Cash_Flows
SET @.pv = (SELECT SUM(cashflow/POWER(1.0+@.ytm,(DATEDIFF(month,@.issue_date, valuta)* 30 + DAY(valuta)-DAY(@.issue_date)
- CASE WHEN(@.issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END )/
360.0 )) FROM Cash_Flows)

WHILE ABS(@.pv) >= 0.000001
BEGIN
DECLARE @.t FLOAT
SET @.t = @.ytm_tmp
SET @.ytm_tmp = @.ytm
SET @.ytm = @.ytm + (@.t-@.ytm)*@.pv/(@.pv-@.pv_tmp)
SET @.pv_tmp = @.pv
SET @.pv = (SELECT SUM(cashflow/POWER(1.0+@.ytm,(DATEDIFF(month,@.issue_date, valuta) * 30 + DAY(valuta)-DAY(@.issue_date)
- CASE WHEN(@.issue_date)>=30 AND DAY(valuta) = 31 THEN 1 ELSE 0 END
) /360.0))
FROM Cash_Flows)
END
RETURN @.ytm
END
GO

SELECT dbo.yield_to_maturity('20060115')
DROP FUNCTION yield_to_maturity
DROP TABLE Cash_Flows
GO
SET NOCOUNT OFF

The fundamental principle of every asset valuation is that the fair value of an asset equals the present value of its cash flows. So, every asset valuation consists more or less of these three steps.
1. Estimate the expected cash flows
2. Determine one or more appropriate discount rates, that you will use to discount the cash flows
3. Calculate Present Value of 1. using 2.

While this is a nice exercise, the above covers only the most basic cases. You can only calculate plain vanilla bonds without embedded derivatives with that formula. Also, it doesn't take into account common day conventions like ISMA 251 or money market conventions. The formula rather uses a simple 30/360 day convention, which nowadays isn't common anymore in Europe. You would need to extend that on your own.
Calculating the YTM is an iterative process while not really is what SQL Server excels at. Like UC said, you would rather do this in a spreadsheet application with a specialised Add-In or some other front-end language|||Isn't the sigature automatically inserted anymore?
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt? http://www.insidesql.de/blogs

Final Part - Min() Function

I have the final part of my report to complete. Yesterday I posted the most confusing set of threads ever on a public forum. So to cut the confusion, I am starting this thread again as now I know exactly what I need.

I have two tables each of which have a function that gathers data from them based around a set of parameters. This is then manipulated by a third function and the results of which are passed into a Stored Procedure. Here is the T-SQL syntax used for the SP;

SELECT fnWTRalldataReport.Areacode, fnWTRalldataReport.siteref, fnWTRalldataReport.estatename, fnWTRalldataReport.Securitised,

fnWTRalldataReport.unitref, fnWTRalldataReport.unittype, fnWTRalldataReport.unittype_count, fnWTRalldataReport.tenantname,

fnWTRalldataReport.tenantstatus, fnWTRalldataReport.tenantstatus_count, fnWTRalldataReport.unitstatus, fnWTRalldataReport.unitstatus_count,

fnWTRalldataReport.floortotal, fnWTRalldataReport.floortotocc, fnWTRalldataReport.floorspaceperc, fnWTRalldataReport.initialvacarea,

fnWTRalldataReport.initialvacnet, fnWTRalldataReport.TotalRent, fnWTRalldataReport.NetRent, fnWTRalldataReport.FinalRtLsincSC,

fnWTRalldataReport.rentrolldiscperc, fnWTRalldataReport.netrentpersqft, fnWTRalldataReport.ErvTot, fnWTRalldataReport.tenancyterm,

fnWTRalldataReport.landact, fnWTRalldataReport.datadate, fnWTRalldataReport.div_mgr, fnWTRalldataReport.portfolio_mgr,

fnWTRalldataReport.propcat, fnWTRalldataReport.budgeted_net_rent, fnWTRalldataReport.budgeted_occupancy,

fnWTRbudgetdata_1.budgeted_net_rent AS budget_rent, fnWTRbudgetdata_1.budgeted_occupancy AS budget_occ

FROM dbo.fnWTRalldataReport(@.dt_src_date, @.chr_div, @.vch_portfolio_no, @.vch_prop_cat) AS fnWTRalldataReport LEFT OUTER JOIN

dbo.fnWTRbudgetdata(@.dt_src_date) AS fnWTRbudgetdata_1 ON fnWTRalldataReport.siteref = fnWTRbudgetdata_1.site_ref

The result of this SQL places a value for budget_rent and budget_occ against every row that the 1st function's result set. What I want to achieve is that where the site_ref is equal in both functions results, I want it to place the budget_rent & budget_occ value against the first row only of each site_ref only.

To explain briefly the structure. Table one has various fields including site_ref and unit_ref. There are many unit_ref's per site_ref in this table. Table 2 has only site_ref and budget info. Someone yesterday suggested that I could achieve this my using something along the lines of the Min() function e.g. Min(unit_ref).

Could someone please elaborate on this for me. I have gone through my SQL book and read about min() and also BOL, but I can't quite work the syntax out to put the budget info against only one line per site based around the lowest unit_ref per site_ref.

This might seem confusing, but it is easier to read than the other thread I assure you.

Regards

If this is just for display, then I would suggest you just let the UI sort through this.

If not, you could try using temp tables to hold the results of the function calls:

select *, cast(0 as bit) as maxUnitRef
into #FNWTRALLDATAREPORT
from dbo.FNWTRALLDATAREPORT(parms)

... same with the other, no added column though

Then

UPDATE #FNWTRALLDATAREPORT
SET maxUnitRef = 1
WHERE unitRef = (select max(unitRef)
from #FNWTRALLDATAREPORT as f2
where f2.siteRef = #FNWTRALLDATAREPORT.siteRef)

Then in the final output just:

case when maxUnitRef = 1 then #FNWTRALLDATAREPORT.BUDGETED_NET_RENT else null end as BUDGETED_NET_RENT..

Does this make any sense? You might also be able to fabricate the maxUnitRef column in the function. Either way it is far easier to do this in the UI, instead of using SQL.

|||

Thank you for this, I will sort through it now. Yes in answer to your question, it is purely for display purposes only on the report.

Regards

Finacial function

I'm looking for financial function in T/SQL.
I need to implement the Rate-Function like in VBA.
Thanks
EricEric,
Here is a shot at it. It's probably much less reliable than the VBA
function,
but maybe you can tweak it some. Note I define a pv function also, since
rate() is iterated to be a good pv() predictor.
create function pv (
@.rate decimal(10,9),
@.nper int,
@.pmt decimal(18,4),
@.fv decimal(18,4),
@.type int
) returns decimal(18,4) as begin
return
(-@.fv -
@.pmt*(1+@.rate*@.type)*(power(1+@.rate,@.npe
r)-1)/@.rate)/power(1+@.rate,@.nper)
end
go
create function rate (
@.nper int,
@.pmt decimal(18,4),
@.pv decimal(18,4),
@.fv decimal(18,4) = 0.0,
@.type int = 0,
@.guess float = 0.1
) returns decimal(10,9) as begin
declare @.guesspv decimal(18,4)
declare @.nextguesspv decimal(18,4)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
declare @.error decimal(18,17)
declare @.nextguess float
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
declare @.tries int set @.tries = 20
while @.error > 0.000001 begin
if @.tries = 0 return null
set @.guess = @.guess +
(@.pv-@.guesspv)/(@.nextguesspv-@.guesspv)*(@.nextguess-@.guess)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
set @.tries = @.tries - 1
end
return @.guess
end
go
select dbo.rate(10*1, -1000, 6500, default, default, default)
go
drop function rate, pv
-- Steve Kass
-- Drew University
-- A32884BB-911C-472A-90F2-C72346B90995
Eric wrote:

>I'm looking for financial function in T/SQL.
>I need to implement the Rate-Function like in VBA.
>Thanks
>Eric
>
>|||One option is to create a VB6 class that has methods that implement the
requisite financial functions (perhaps calling VBA financial functions). You
then call your financial functions from TSQL by using the extended stored
procedures sp_OACreate, sp_OAMethod, and sp_OADestroy.
Here's what you'd need to do:
1. Compile your VB6 class into a DLL. You better have good error handling in
it otherwise you risk crashing your SQL Server.
2. Place that DLL into the WIN32 directory on your SQL Server, and REGISTER
it on your SQL Server by using RegSvr32.exe
3. Call methods of your VB6 class/dll from any stored procedure, using
something like the following (some of the names have been changed to protect
the innocent):
BEGIN
blah blah blah
DECLARE @.Object int -- holds a reference to your object instantiated
from the vb6 class.
-- Set a string equal to your entire method call - including any
parameter values.
SET @.MethodToCall = 'CalculatePayment(' + @.RatePercent + ', ' +
@.RateIncrease + ', ' + @.Months + ', ' + @.Fees + ')'
--Instantiate an instance of VB6 class and put it's reference in
@.object
EXEC sp_OACreate 'Your_VB_DLL.ClassName, @.object OUT
--Run the financial function (e.g., CalculatePayment method) of your
class - and place it's output into @.Return
EXEC sp_OAMethod @.object, @.MethodToCall, @.return OUT
-- Destroy the instance of our DataConversion class now that we're
done with it.
EXEC sp_OADestroy @.object
blah blah blah
END
4. Any time you upgrade your VB6 DLL, be sure to Unregister the old one
(using RegSvr32.exe... -U), then replace the old DLL with the new one, and
then register the new one like in step 2 above. No need to restart your SQL
Server.
-HTH
"Eric" <jug@.nospam.nospam> wrote in message
news:u0QX3AabFHA.724@.TK2MSFTNGP12.phx.gbl...
> I'm looking for financial function in T/SQL.
> I need to implement the Rate-Function like in VBA.
> Thanks
> Eric
>|||Steve, Do you also have a similar function to calculate the Pmt amount?
========================================
======================

quote:

---

Originally posted by Steve Kass
Eric,
Here is a shot at it. It's probably much less reliable than the VBA
function,
but maybe you can tweak it some. Note I define a pv function also, since
rate() is iterated to be a good pv() predictor.
create function pv (
@.rate decimal(10,9),
@.nper int,
@.pmt decimal(18,4),
@.fv decimal(18,4),
@.type int
) returns decimal(18,4) as begin
return
(-@.fv -
@.pmt*(1+@.rate*@.type)*(power(1+@.rate,@.npe
r)-1)/@.rate)/power(1+@.rate,@.nper)
end
go
create function rate (
@.nper int,
@.pmt decimal(18,4),
@.pv decimal(18,4),
@.fv decimal(18,4) = 0.0,
@.type int = 0,
@.guess float = 0.1
) returns decimal(10,9) as begin
declare @.guesspv decimal(18,4)
declare @.nextguesspv decimal(18,4)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
declare @.error decimal(18,17)
declare @.nextguess float
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
declare @.tries int set @.tries = 20
while @.error > 0.000001 begin
if @.tries = 0 return null
set @.guess = @.guess +
(@.pv-@.guesspv)/(@.nextguesspv-@.guesspv)*(@.nextguess-@.guess)
set @.guesspv = dbo.pv(@.guess, @.nper, @.pmt, @.fv, @.type)
set @.error = abs(log(@.guesspv/@.pv))
set @.nextguess = @.guess+@.error*@.guess
set @.nextguesspv = dbo.pv(@.nextguess, @.nper, @.pmt, @.fv, @.type)
set @.tries = @.tries - 1
end
return @.guess
end
go
select dbo.rate(10*1, -1000, 6500, default, default, default)
go
drop function rate, pv
-- Steve Kass
-- Drew University
-- A32884BB-911C-472A-90F2-C72346B90995
Eric wrote:

>I'm looking for financial function in T/SQL.
>I need to implement the Rate-Function like in VBA.
>Thanks
>Eric
>
>

---

Filters in combination with the aggregate function

Hi all,
I have a report and use the aggregate function in a table. Now I want
to show the top 50 from that table. But when I use a filter on a group
and use the top N function i get a error message:
The aggregate function "Aggregate" cannot be used in a report that
contains any filters.
How can i filter the top 50 with aggregate functions in a report?
Regards,
Robbert
HollandIn your dataset you can use your select statement.
Select Top(50) Field1
From Sometable
Group By Field1
That will set the whole dataset to show only your top 50. THen you can
graph it or do whatever you want with those records.
<robbert_visscher@.hotmail.com> wrote in message
news:1155037378.956732.155110@.i3g2000cwc.googlegroups.com...
> Hi all,
> I have a report and use the aggregate function in a table. Now I want
> to show the top 50 from that table. But when I use a filter on a group
> and use the top N function i get a error message:
> The aggregate function "Aggregate" cannot be used in a report that
> contains any filters.
> How can i filter the top 50 with aggregate functions in a report?
> Regards,
> Robbert
> Holland
>|||My query is on a Cube. If I use top 50 it is not working. Now i have
used topcount to select the top 50 but is it not possible to do een top
50 in a report with aggregate functions? If not, why?