Gathering information about space in a database using transact sq

Hi!,
I want to gather information about the total space, space used and some
others
statistics like the task pad in the enterprise manager, but using a transact
sql script. I didn't find system tables or useful procedures to do that , the
sp_spaceused give some information but I would like to made some statistics
with this values,
any ideas'
thanks!Hi
Run Profiler and Open up taskpad in EM, and see what EM executes agaisnt the
DB. This gives you a good idea of what they are using.
Regards
Mike
"Her" wrote:
> Hi!,
> I want to gather information about the total space, space used and some
> others
> statistics like the task pad in the enterprise manager, but using a transact
> sql script. I didn't find system tables or useful procedures to do that , the
> sp_spaceused give some information but I would like to made some statistics
> with this values,
> any ideas'
> thanks!
>

Gathering information about space in a database using transact sq

Hi!,
I want to gather information about the total space, space used and some
others
statistics like the task pad in the enterprise manager, but using a transact
sql script. I didn't find system tables or useful procedures to do that , th
e
sp_spaceused give some information but I would like to made some statistics
with this values,
any ideas'
thanks!Hi
Run Profiler and Open up taskpad in EM, and see what EM executes agaisnt the
DB. This gives you a good idea of what they are using.
Regards
Mike
"Her" wrote:

> Hi!,
> I want to gather information about the total space, space used and some
> others
> statistics like the task pad in the enterprise manager, but using a transa
ct
> sql script. I didn't find system tables or useful procedures to do that ,
the
> sp_spaceused give some information but I would like to made some statistic
s
> with this values,
> any ideas'
> thanks!
>

Gathering information about space in a database using transact sq

Hi!,
I want to gather information about the total space, space used and some
others
statistics like the task pad in the enterprise manager, but using a transact
sql script. I didn't find system tables or useful procedures to do that , the
sp_spaceused give some information but I would like to made some statistics
with this values,
any ideas?
thanks!
Hi
Run Profiler and Open up taskpad in EM, and see what EM executes agaisnt the
DB. This gives you a good idea of what they are using.
Regards
Mike
"Her" wrote:

> Hi!,
> I want to gather information about the total space, space used and some
> others
> statistics like the task pad in the enterprise manager, but using a transact
> sql script. I didn't find system tables or useful procedures to do that , the
> sp_spaceused give some information but I would like to made some statistics
> with this values,
> any ideas?
> thanks!
>

function or formula that convert numbers into words

Hello
I am using crystal report 9. i get total of all item purchsed by customer. what is need a any function or formula that convert numbers into words. i.e 32125 should be converted to thirty two thousand one hundred twenty five only.
With regardsCreate a formula and in forumula section (module) there were so many options like functions conversions etc and in that section look for numbers to text option and using that you can achieve what you are looking for.|||I'm not sure about CR9, but in CR10 you can use the ToWords function.

Function Help

I am using SQL 2000.
I have a table with Shipping Transactions in it (below).
Each record listed is a package, but the Amount shown is the total shipping
cost for all packages within a Shipment (Tracking)
I am trying to write a FUNCTION that will return the Total Shipment Amount
for a given PT (462714). Any Amount associated with a record that is voided
would not be included.
So, using the data below the function should return:
61.73
(61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
354925898 was voided so the 46.04 associated with it is excluded.
Recorded PT Order Tracking Total Weight Amount Void
1 462714 1589651 352414201 247 46.04 NO
2 462714 1589651 352414201 247 46.04 NO
3 462714 1589651 352414201 247 46.04 NO
19 462714 1589651 354925898 247 46.04 NO
20 462714 1589651 354925898 247 46.04 NO
21 462714 1589651 354925898 247 46.04 NO
22 462714 1589651 354925898 247 46.04 NO
23 462714 1589651 354925898 247 46.04 NO
24 462714 1589651 354925898 247 46.04 NO
25 462714 1589651 354925898 247 46.04 NO
26 462714 1589651 354925898 247 46.04 NO
27 462714 1589651 354925898 247 46.04 NO
28 462714 1589651 354925898 247 46.04 Y
29 462714 1589651 354925898 247 46.04 Y
30 462714 1589651 354925898 247 46.04 Y
31 462714 1589651 354925898 247 46.04 Y
32 462714 1589651 354925898 247 46.04 Y
33 462714 1589651 354925898 247 46.04 Y
34 462714 1589651 354925898 247 46.04 Y
35 462714 1589651 354925898 247 46.04 Y
36 462714 1589651 354925898 247 46.04 Y
37 462714 1589651 354111785 56 15.69 N
38 462714 1589651 354111785 56 15.69 NO
Any help would be greatly appreciated.
Thank youA number of ways, here is one (untested):
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
WHERE Void = 'N'
AND PT = 462714
GROUP BY order
) x
(Try and get the query itself working before incorporating it into a
function. Much easier to debug in QA that way, imho.)
A
"Kevin L" <no_spam@.not_real_email.com> wrote in message
news:%23yxDhihPGHA.720@.TK2MSFTNGP14.phx.gbl...
>I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total
> shipping cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is
> voided would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>|||There is some duplicate data (as far as the fields you are looking at for
returning the total) - the following function weeds through them to return
the sum. I might suggest finding some relation to this table to filter
rather than having to use the distincts in the function. This should get yo
u
started:
CREATE FUNCTION [dbo].[GetPTTotal] (
@.PT int
)
RETURNS money AS
BEGIN
declare @.amount money
declare @.trackingamounts table (tracking int, amount money, void bit default
0)
--grab all the records which might indicate a valid amount
insert @.trackingamounts (tracking, amount)
select distinct tracking, amount
from dbo.shippingtransaction
where PT = @.pt
and void like 'n%'
--go back and void any tracking number which has at least one void
update ta
set void = 1
FROM @.trackingamounts ta
join ( select distinct tracking, void
from dbo.shippingtransaction
where pt = @.pt
and void = 'Y'
) tab on ta.tracking = tab.tracking
-- output the sum of any remaining valid amounts
select @.amount = sum(amount) from @.trackingamounts where void = 0
RETURN @.amount
END
GO
"Kevin L" wrote:

> I am using SQL 2000.
> I have a table with Shipping Transactions in it (below).
> Each record listed is a package, but the Amount shown is the total shippin
g
> cost for all packages within a Shipment (Tracking)
> I am trying to write a FUNCTION that will return the Total Shipment Amount
> for a given PT (462714). Any Amount associated with a record that is voide
d
> would not be included.
> So, using the data below the function should return:
> 61.73
> (61.73 = 46.04 (from 352414201) + 15.69 (from 354111785))
> 354925898 was voided so the 46.04 associated with it is excluded.
>
> Recorded PT Order Tracking Total Weight Amount Void
> 1 462714 1589651 352414201 247 46.04 NO
> 2 462714 1589651 352414201 247 46.04 NO
> 3 462714 1589651 352414201 247 46.04 NO
> 19 462714 1589651 354925898 247 46.04 NO
> 20 462714 1589651 354925898 247 46.04 NO
> 21 462714 1589651 354925898 247 46.04 NO
> 22 462714 1589651 354925898 247 46.04 NO
> 23 462714 1589651 354925898 247 46.04 NO
> 24 462714 1589651 354925898 247 46.04 NO
> 25 462714 1589651 354925898 247 46.04 NO
> 26 462714 1589651 354925898 247 46.04 NO
> 27 462714 1589651 354925898 247 46.04 NO
> 28 462714 1589651 354925898 247 46.04 Y
> 29 462714 1589651 354925898 247 46.04 Y
> 30 462714 1589651 354925898 247 46.04 Y
> 31 462714 1589651 354925898 247 46.04 Y
> 32 462714 1589651 354925898 247 46.04 Y
> 33 462714 1589651 354925898 247 46.04 Y
> 34 462714 1589651 354925898 247 46.04 Y
> 35 462714 1589651 354925898 247 46.04 Y
> 36 462714 1589651 354925898 247 46.04 Y
> 37 462714 1589651 354111785 56 15.69 N
> 38 462714 1589651 354111785 56 15.69 NO
>
> Any help would be greatly appreciated.
> Thank you
>
>|||On Thu, 2 Mar 2006 12:51:27 -0500, Aaron Bertrand [SQL Server MVP]
wrote:

>A number of ways, here is one (untested):
>SELECT SUM(amount)
>FROM
>(
> SELECT order,amount = MIN(amount)
> FROM tablename
> WHERE Void = 'N'
> AND PT = 462714
> GROUP BY order
> ) x
>(Try and get the query itself working before incorporating it into a
>function. Much easier to debug in QA that way, imho.)
Hi Aaron,
Looking at the sample data Kevin posted, I suspect that it should be
SELECT SUM(amount)
FROM
(
SELECT order,amount = MIN(amount)
FROM tablename
AND PT = 462714
GROUP BY order
HAVING MAX(Void) < 'Y'
) x
One of the orders had 'NO' for voided in some rows and 'Y' in some
others, and he wanted to exclude the whole order.
BTW, Kevin: Please add a CHCEK constraint to the Void column so that
you'll have to deal with only one value for yes and one for no - this
mess with NO, N and Y (end maybe YES as well) all interspersed just
gives you unneeded problems.
Hugo Kornelis, SQL Server MVP|||> One of the orders had 'NO' for voided in some rows and 'Y' in some
> others, and he wanted to exclude the whole order.
Ah, good catch.