Hello,
My table was created by importing from an Excel spreadsheet. Typical fields in the rows are a date and various stock values like Open, High, Low, and Close. Unfortunately, many of the values have the wrong characteristics. These are my problems:
1. The date field has time in addition to the date. I don't want the
time in this field.
2. Many of the amount fields have a large precision, for example,
1.9399999999999999. I want to allow for a precision of 5 and a
scale of 2.
Can I make changes to my table at this point? I looked into Design Table but I don't see any feature allowing me to make changes 'on the fly'.
Any suggestions are welcome.
JoeHowdy,
Usually you can query a datetime column to extract just the date, so dont worry too much about that.
The column precision can be changed ( on the fly as it were ) using an alter table command ( see BOL ) that will automatically change the column precision and in the process round the values to what you want.
Cheers,
SG
( PS - theres nothing quite like a V8 Holden ute.....)|||Thank you for the reply. It occurs to me that one can end up creating a great many different queries if one is interested in comparing possible results/outputs. If DBA's want to save their queries for possible use later do they typically like to store them in a standard folder? Or should one create a special folder within the 'Databases' folder?
Thanks again. I am still new to working with Query Analyzer.
Joe|||I would also like to ask anyone if he or she could advise me as to how I can display only a date (without the time) when I do a query using Query Analyzer. I really don't want to see time displayed.
Can someone assist?
Thanks again.
Joe|||Howdy,
Well, sadly SQL doesnt handle splitting out dates from datetime fields very well.
Assuming you had a column called DATE in a table called INFO, if you want to display JUST the date, you need to extract the hour, min, seconds as characher values then reconstruct into a character format ( and later change to datetime , which by the way gives a defualt date of 01/01/1900)
Now, assuming you have a small table called INFO, with one column called DATE with one value of 2003-10-10 17:23:34
If you xxtract using time the following code -
select convert(varchar(2),datepart(hh,DATE))
+':'+convert(varchar(2),datepart(mm,DATE))
+':'+convert(varchar(2),datepart(ss,DATE))
from INFO
This gives -
17:23:34 ( but in varchar format).
Note too that single digit values WILL NOT have a '0' put in front of them unless you test for it & code for it accordingly.
Unless you get all dates into the same format - e.g. all varchar/char or all in datetime, mixing & matching will give you a headache.
Cheers,
SG.
Showing posts with label fields. Show all posts
Showing posts with label fields. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
How to Change 200 to 2.00
Hi,
Can anyone help me on this problem please?
When i use sql statement to pull out data, one of the fields will pull out data like 200 or 300 or sumthing like that.
But now i need to pull out the data as 2.00 instead of 200.
How do i do that?
ThanxSelect Cast(200/100 As Decimal(38, 2))|||The simplest way to turn 200 into 2.00 is to invest it in AT&T stock.
Barring that, why don't you just divide by 100.00? Note that you will need to include the decimal point and placeholders in your divisor so that the result will include 2 digits of precision.
Can anyone help me on this problem please?
When i use sql statement to pull out data, one of the fields will pull out data like 200 or 300 or sumthing like that.
But now i need to pull out the data as 2.00 instead of 200.
How do i do that?
ThanxSelect Cast(200/100 As Decimal(38, 2))|||The simplest way to turn 200 into 2.00 is to invest it in AT&T stock.
Barring that, why don't you just divide by 100.00? Note that you will need to include the decimal point and placeholders in your divisor so that the result will include 2 digits of precision.
How to cast a numeric database field to character
Hello.
I have a report and need to concatenate two numeric database fields, a month
and a year, into a string with a slash (/) between them and put it on the
report header.
How can I do this?
Thanks in advance,
MikeUse CStr(Month) & "/" & CStr(Year)
"MikeL" wrote:
> Hello.
> I have a report and need to concatenate two numeric database fields, a month
> and a year, into a string with a slash (/) between them and put it on the
> report header.
> How can I do this?
> Thanks in advance,
> Mike
>
>
I have a report and need to concatenate two numeric database fields, a month
and a year, into a string with a slash (/) between them and put it on the
report header.
How can I do this?
Thanks in advance,
MikeUse CStr(Month) & "/" & CStr(Year)
"MikeL" wrote:
> Hello.
> I have a report and need to concatenate two numeric database fields, a month
> and a year, into a string with a slash (/) between them and put it on the
> report header.
> How can I do this?
> Thanks in advance,
> Mike
>
>
Friday, March 9, 2012
How to call a stored procedure from a function
Hi
The idea is to generate the sequence for some of the fields in the table.
Since the identity property sets the sequence to only one of the field in
the table, decided to have a UDF that would create a new sequence value
generated for the fields.
But when the function is called i get the error
"Only functions and extended stored procedures can be executed from within a
function." Please suggest
The below table will hold the names of the fields that would require the
sequence to be generated and the last value generated updated by the stored
procedure.
CREATE table SEQ_GENERATOR_TBL
( seq_name varchar(50) not null,
last_value bigint default 0 not null);
GO
insert into SEQ_GENERATOR_TBL(seq_name)
values('SEQ_IS_GLOBAL_IDENTIFIER');
CREATE function SEQ_GENERATOR_FUNC
(@.p_seq_name varchar(50))
RETURNS bigint
AS
BEGIN
DECLARE @.ret_next_value bigint
SET @.ret_next_value = (select last_value+1 as next_value
from SEQ_GENERATOR_TBL
WHERE seq_name = @.p_seq_name);
EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
RETURN @.ret_next_value;
END
GO
CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
@.p_seq_name varchar(50),
@.p_curr_value bigint
AS
BEGIN
BEGIN TRANSACTION;
UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
WHERE seq_name = @.p_seq_name;
COMMIT TRANSACTION;
RETURN;
END
GOJP
Do you expect the same sequence as the IDENTITY property is set to? Have you
considered using computed column?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:CD9841BA-3ED6-4140-BC3D-3588FC522226@.microsoft.com...
> Hi
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from within
> a
> function." Please suggest
> The below table will hold the names of the fields that would require the
> sequence to be generated and the last value generated updated by the
> stored
> procedure.
> CREATE table SEQ_GENERATOR_TBL
> ( seq_name varchar(50) not null,
> last_value bigint default 0 not null);
> GO
> insert into SEQ_GENERATOR_TBL(seq_name)
> values('SEQ_IS_GLOBAL_IDENTIFIER');
> CREATE function SEQ_GENERATOR_FUNC
> (@.p_seq_name varchar(50))
> RETURNS bigint
> AS
> BEGIN
> DECLARE @.ret_next_value bigint
> SET @.ret_next_value = (select last_value+1 as next_value
> from SEQ_GENERATOR_TBL
> WHERE seq_name = @.p_seq_name);
> EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
> RETURN @.ret_next_value;
> END
> GO
> CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
> @.p_seq_name varchar(50),
> @.p_curr_value bigint
> AS
> BEGIN
> BEGIN TRANSACTION;
> UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
> WHERE seq_name = @.p_seq_name;
> COMMIT TRANSACTION;
> RETURN;
> END
> GO
>|||Where do you want to show the data?
If you use reports do the numbering there
Madhivanan|||JP (JP@.discussions.microsoft.com) writes:
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from
> within a function." Please suggest
Rework and redesign. A function must not change database state, why
updates are not permitted, and neither calls to stored procedure as
they could do about anything.
Itzik Ben-Gan discussed a couple of solution in his column T-SQL Black
Belt in SQL Server Magazine a couple of issues back.
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
The idea is to generate the sequence for some of the fields in the table.
Since the identity property sets the sequence to only one of the field in
the table, decided to have a UDF that would create a new sequence value
generated for the fields.
But when the function is called i get the error
"Only functions and extended stored procedures can be executed from within a
function." Please suggest
The below table will hold the names of the fields that would require the
sequence to be generated and the last value generated updated by the stored
procedure.
CREATE table SEQ_GENERATOR_TBL
( seq_name varchar(50) not null,
last_value bigint default 0 not null);
GO
insert into SEQ_GENERATOR_TBL(seq_name)
values('SEQ_IS_GLOBAL_IDENTIFIER');
CREATE function SEQ_GENERATOR_FUNC
(@.p_seq_name varchar(50))
RETURNS bigint
AS
BEGIN
DECLARE @.ret_next_value bigint
SET @.ret_next_value = (select last_value+1 as next_value
from SEQ_GENERATOR_TBL
WHERE seq_name = @.p_seq_name);
EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
RETURN @.ret_next_value;
END
GO
CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
@.p_seq_name varchar(50),
@.p_curr_value bigint
AS
BEGIN
BEGIN TRANSACTION;
UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
WHERE seq_name = @.p_seq_name;
COMMIT TRANSACTION;
RETURN;
END
GOJP
Do you expect the same sequence as the IDENTITY property is set to? Have you
considered using computed column?
"JP" <JP@.discussions.microsoft.com> wrote in message
news:CD9841BA-3ED6-4140-BC3D-3588FC522226@.microsoft.com...
> Hi
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from within
> a
> function." Please suggest
> The below table will hold the names of the fields that would require the
> sequence to be generated and the last value generated updated by the
> stored
> procedure.
> CREATE table SEQ_GENERATOR_TBL
> ( seq_name varchar(50) not null,
> last_value bigint default 0 not null);
> GO
> insert into SEQ_GENERATOR_TBL(seq_name)
> values('SEQ_IS_GLOBAL_IDENTIFIER');
> CREATE function SEQ_GENERATOR_FUNC
> (@.p_seq_name varchar(50))
> RETURNS bigint
> AS
> BEGIN
> DECLARE @.ret_next_value bigint
> SET @.ret_next_value = (select last_value+1 as next_value
> from SEQ_GENERATOR_TBL
> WHERE seq_name = @.p_seq_name);
> EXEC UPD_SEQ_GENERATOR_PROC @.p_seq_name, @.ret_next_value;
> RETURN @.ret_next_value;
> END
> GO
> CREATE PROCEDURE UPD_SEQ_GENERATOR_PROC
> @.p_seq_name varchar(50),
> @.p_curr_value bigint
> AS
> BEGIN
> BEGIN TRANSACTION;
> UPDATE SEQ_GENERATOR_TBL SET last_value = @.p_curr_value
> WHERE seq_name = @.p_seq_name;
> COMMIT TRANSACTION;
> RETURN;
> END
> GO
>|||Where do you want to show the data?
If you use reports do the numbering there
Madhivanan|||JP (JP@.discussions.microsoft.com) writes:
> The idea is to generate the sequence for some of the fields in the table.
> Since the identity property sets the sequence to only one of the field in
> the table, decided to have a UDF that would create a new sequence value
> generated for the fields.
> But when the function is called i get the error
> "Only functions and extended stored procedures can be executed from
> within a function." Please suggest
Rework and redesign. A function must not change database state, why
updates are not permitted, and neither calls to stored procedure as
they could do about anything.
Itzik Ben-Gan discussed a couple of solution in his column T-SQL Black
Belt in SQL Server Magazine a couple of issues back.
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
Wednesday, March 7, 2012
How to calculate the total days between open and close date
Hi All,
I have a table call case and case_status have two fields, date and status as below:
date status
04/01/2006 open
04/05/2006 closed
04/10/2006 open
04/15/2006 closed
Whenever i open and closed the case, one record is insert into the case_status table.
Now I would need to calculate the total days of the case in storeprocedure.
Anyone can help me please.
Aung
This articledoes something similar. check if it helps.|||One try:
CREATE
PROCEDURE [dbo].[caseDays]AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from-- interfering with SELECT statements.SETNOCOUNTON;RETURN(SELECTSUM(datediff( dd, c.openDate, d.closedDate))as myCaseDateFROM(SELECT a.cDateas openDate, row_NUmber()over(ORDERBY a.cDate)as ROWNUMBERFROM case_statusAS aWHERE(a.status='open'))as cinner
join(SELECT b.cDateas closedDate, row_NUmber()over(ORDERBY b.cDate)as ROWNUMBERFROM case_statusAS bWHERE(b.status='closed'))as dON c.ROWNUMBER=d.ROWNUMBER)END
I hope this one will be close to your solution.
Limno
|||Thanks for your responsed.
But my problem is total days in two date between open and closed. I still facing this problem.
Thanks
Aung
|||Hello:
datediff( dd,openDate,closedDate)
This function will give you how many days between open and closed days.
If this is not what you want, give a little more details about your problem.
Limno
|||Wouldn't the table also need a CaseID field so you know what case was being opened and closed?How to calculate the midpoint
I have three fields date, low, high. I need to calculate the midpoint
of low and high and display it.
Hi amj1020,
of low and high and display it.
Date,Low,High
20071106,92.03,92.13
20071106,88.77,88.87
20071106,90.20,90.30
20071106,95.21,95.31
20071106,93.13,93.23
20071106,91.01,91.11On Wed, 07 Nov 2007 06:48:09 -0800, amj1020 wrote:
Quote:
Originally Posted by
>I have three fields date, low, high. I need to calculate the midpoint
>of low and high and display it.
Hi amj1020,
SELECT "date", (low + high) / 2.0 AS midpoint
FROM YourTable;
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
How to calculate record size
Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
>> will record size be diferent if I save in each field value 0 versus if I
>> save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>>I should add that I am using SQL SErver 2000.
>> Thanks
>> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
>> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int.
>> Now, will record size be diferent if I save in each field value 0 versus
>> if I save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>>
>
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
>> will record size be diferent if I save in each field value 0 versus if I
>> save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>>I should add that I am using SQL SErver 2000.
>> Thanks
>> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
>> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>> Hi,
>> Can anyone help me to calculate record size for a table.
>> 1. Lets say I have a table with 10 fields, each of them is type int.
>> Now, will record size be diferent if I save in each field value 0 versus
>> if I save value NULL?
>> 2. What if table has all column as NOT NULL? Will then SQL Server still
>> create NULL bitmap for record:
>> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
>> 3. BOL has different size calculations for fixed-length columns and
>> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
>> fixed length or variable length? Which data types are variable length? I
>> do know that varchar, varbinary, nvarchar is variable. But are there any
>> other?
>>
>> Thank you for help
>>
>>
>
How to calculate record size
Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for helpI should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>
How to calculate record size
Hi,
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for help
I should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>
|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>
|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>
Can anyone help me to calculate record size for a table.
1. Lets say I have a table with 10 fields, each of them is type int. Now,
will record size be diferent if I save in each field value 0 versus if I
save value NULL?
2. What if table has all column as NOT NULL? Will then SQL Server still
create NULL bitmap for record:
Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
3. BOL has different size calculations for fixed-length columns and variable
length column. Is NUMERIC(19,2) NOT NULL field considered to be fixed length
or variable length? Which data types are variable length? I do know that
varchar, varbinary, nvarchar is variable. But are there any other?
Thank you for help
I should add that I am using SQL SErver 2000.
Thanks
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
> Hi,
> Can anyone help me to calculate record size for a table.
> 1. Lets say I have a table with 10 fields, each of them is type int. Now,
> will record size be diferent if I save in each field value 0 versus if I
> save value NULL?
> 2. What if table has all column as NOT NULL? Will then SQL Server still
> create NULL bitmap for record:
> Null Bitmap (Null_Bitmap) = 2 + (( Num_Cols + 7) / 8 )
> 3. BOL has different size calculations for fixed-length columns and
> variable length column. Is NUMERIC(19,2) NOT NULL field considered to be
> fixed length or variable length? Which data types are variable length? I
> do know that varchar, varbinary, nvarchar is variable. But are there any
> other?
>
> Thank you for help
>
>
|||Kalen Delaney's "Inside SQL server 2000" will give you the full story on
this but in summary:
1. NO, Size will always be the same.
2. If I remember right if there are NO nullable columns then the null bitmap
is not stored in the row.
3. Add text (and ntext) as effectively variable length. How much is stored
in the row is dependant on whether the text is to be stored within the row,
or a pointer to seperate text pages, and hence could vary from 16 bytes
upwards. Your specific example of numeric (19,2) is fixed length. BOL has
defined physical sizes for all the different data types.
Mike John
"NoSpam" <NoSpam@.NoSpam.com> wrote in message
news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>I should add that I am using SQL SErver 2000.
> Thanks
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:uvsHEJMzEHA.2040@.tk2msftngp13.phx.gbl...
>
|||Mike,
Thanks for the information.
"Mike John" <Mike.John@.knowledgepool.com> wrote in message
news:e2BQoSPzEHA.1564@.TK2MSFTNGP09.phx.gbl...
> Kalen Delaney's "Inside SQL server 2000" will give you the full story on
> this but in summary:
> 1. NO, Size will always be the same.
> 2. If I remember right if there are NO nullable columns then the null
> bitmap is not stored in the row.
> 3. Add text (and ntext) as effectively variable length. How much is stored
> in the row is dependant on whether the text is to be stored within the
> row, or a pointer to seperate text pages, and hence could vary from 16
> bytes upwards. Your specific example of numeric (19,2) is fixed length.
> BOL has defined physical sizes for all the different data types.
> Mike John
> "NoSpam" <NoSpam@.NoSpam.com> wrote in message
> news:e5fliKMzEHA.3184@.TK2MSFTNGP10.phx.gbl...
>
Friday, February 24, 2012
how to calculate cost of a trigger?
I wanna used derived fields to improve select performance of my system.
How can I calculate the trigger cost in the system?
Triggers are resource intensive objects, you should look for alternatives before implementing them. You can run the profiler to find out the cost of any code you run through SQL Server. You will find the Profiler under tools in Management studio. Hope this helps.|||I wanna usedderived fields to improve select performance of my system.How can I calculate the trigger cost in the system?
We are designing the DATA MODELand The TASK table it has two mutually exclusive fields.
WHOID > PersonID,OrganizationID
WHATID > DocumentID,EstateID,LeadID,RequirementID,
and on the Task UI we display the names of the Related EntityTypes and this causes us 10 inner/Joins to pull the data from database.
One of the designer in the team suggested putting derived fields such as
WHONAME > PersonName,OrganizationName
WHATNAME > DocumentName,EstateType,LeadName,RequirementName
So from performance perspective,We are really confused as putting triggers cause implicit locks on the system where as the other option causes 10 joins.
There is one to many relationship between WHOID and TASK(..3 related entitytypes mutually exclusive)
and also there is one to many relationship between WHATID and TASK(..7 related entitytypes mutually exclusive)
7 joins + 3 joins = 10 joins.
it is a really simple UI infact.
The Edit screen of TASK EDIT
--------------
Subject > char
FromDate > datetime
Todate > datetime
Priority > char
State > char
Email > char
Whoname> Related Person,Organization or Lead NAME
WhatName>Related Document,Estate,Requirement,Case,Oppurtunity Name ..etc
------
I would love not to have ten joins but the requirement forces us that way|||
What you need is simple DRI(declarative referenctial integrity) enabled on the tables in Management studio, so Updates will Cascade and if needed Deletes will Cascade. I know you need a DDL( data definition langauge) person in your team because a Table design comes down to files and association, while relationship is determined by upper and lower bound Cardinality. If you follow that most of your tables will disappear and what is now a JOIN will become a simple additional condition which in SQL Server can be added with the AND operator to a JOIN operation.
Some people do just DDL(data definition language) for a living. Run a search for DRI(declarative referential integerity) in SQL Server BOL (books online). Hope this helps.
|||thanks caddre.that looks interesting.
http://www.cvalde.net/document/declaRefIntegVsTrig.htm
but I have a hard time understanding how it will change my WHONAME and WHATNAME.
if the it does it would be great.
|||I could be wrong but what you have looks like objects to me because if those are two tables you need just two IDENTITY or it becomes a composite. Try the link below and download and install AdventureWorks 2005 most tables you need in a business application is in there. Look at the tables pay attention to the constraints and DRI (declarative referential integrity). Hope this helps.
http://www.microsoft.com/downloads/details.aspx?familyid=e719ecf7-9f46-4312-af89-6ad8702e4e6e&displaylang=en
|||Is there a problem with the 10 joins?
Personally, I would just create a view to simplify the select statement that has all your required joins in it. Just make sure your tables are indexed properly, and you shouldn't have an issue.
|||Hmmm I have tested it with 10 million records and the derived approach is 20X faster.
I really don't wanna use triggers but for now I'll just try the links caddre send.
|||If you aren't displaying all 10 million rows at a time within the UI, you may find it beneficial to do your limiting select first, then doing the 10 joins once your have your limited resultset back, kinda like:
SELECT ...
FROM (Your limited query here)
JOIN ...
JOIN ...
JOIN ..
although why SQL Server isn't already doing that for you is odd. A bad query plan, or inaccurate statistics most likely. Before spending the time on trying to work around SQL, I'd first run your query through the tuning wizard and see what it comes up with, you may be suprised. If you've already done that, my appologies.
You want to try an indexed view approach as well, which may give you what you are looking for. Something like:
CREATE MyView WITH SCHEMABINDING AS
SELECT (Your 10 fields here),t1.Name,t2.Name,t3.Name,t4.Name,...
FROM MyTable mt
JOIN EntityRef t1 ON (mt.key=t1.key)
...
Then create a primary key on the view, then create your indexes (Whatever you are limiting/searching on)
You'll incur much of the same overhead as triggers though, so test your results.
|||As a side note, Caddre has mentioned a lot of differing technologies, one of which is DRI. DRI by itself won't help to solve your issue, but by leveraging it, if possible, it could reduce the complexity of your database design, and increase your performance.
For example, take table1, and table2 here:
Table Name > Fields
Table1>EntityID,col1,col2,col3,col4...
Table2>EntityID,Name
Normally DRI is used to link both entityIDs in table1 and table2 in a foreign key relationship. This by itself will not gain you any performance at all, infact in many operations it will decrease overall performance substantially. However, if you redesign your tables, like so:
Table1>Name,col1,col2,col3,col4...
Table2>Name
Now by using DRI to maintain the relationship between Name (via a foreign key), then it's quite possible you will see a performance increase since you have just eliminated a join in your selects. Foreign keys can be used to "replicate" changes in the name field done in table2 to all records in table1 that use that name Now that's one VERY expensive update, but, it does insure that all instances in table1 that link to a particular name all use the exact same name. This is usually why you use identifiers in your base tables to begin with, and since those changes are very infrequent (Or should be), it's a decent trade off in performance, but you can make a pretty big mess of your database structure (and data) by taking this route if you aren't careful though. These in SQL Server are also called Cascading Deletes and Updates.
Using our above new schema, if you issue this:
UPDATE table2 SET Name='new value' where Name='old value'
SQL Server will actually do something like this:
BEGIN TRANSACTION
UPDATE table2 SET Name='new value' where Name='old value'
UPDATE table1 SET Name='new value' where Name='old value'
COMMIT TRANSACTION
With better error handling of course. If there are any errors, it's all rolled back, but it's called cascading be cause a delete in table2 would delete some records in table1, which in turn may delete some records in table3, etc.
Now with all that out of the way, you have to realize by replacing your small little ID field with a probably much larger field, sure you lose the join, but you've increased your row size. Indexes will be larger, and pages will contain less rows. Operations that require to scan large portions of your table will take longer, as well. So test a lot, on not just on what you need to do now, but think and test the ramifications on all your other queries as well.
|||I have thought another method today which might be wrong ...instead of using DRI and triggers to maintain the data I am thinking about writing a class just for the purpose of putting data and updating data into my derived field.And I will tell from code behind that I need to update in anycase the update happens in my main tables of derived coloumns.
The advantages of this approach over triggers.
1.No implicit transactions
2.easy to debug
3.Faster than triggers
4.No unneccessary updates
5.Could write code to maintain and check the alldata.
Disadvantages I see
1.Triggers are more safe in terms of integrity.
2.Less chance to forget to update the derived fields.
*I am really confused some how but the requirement of the application forces us not to use three things that will load the SQL CPU load.
A:Triggers
B.UNIONS,Exits
C.Joins
|||I fail to see how most of what you've listed as "advantages" are advantages. I would say:
Advantages:
1. No implicit transactions (All SQL Statements are an implied transaction, you haven't eliminated them, just simplified it -- But that's an advantage still)
2. Easy to debug -- For your application, if it's the only one accessing the data, yes.
3. Faster than triggers -- I doubt it. Sounds like you are going to have to make an extra round trip between the server and client to pull back the name field that you want to put in the field, which will make it many times slower, but holding locks for slightly less time. Unless you are caching the table(s) the 10 joins are getting their names from, in which case, you need to add the added application/web server memory cost as a disadvantage as well.
4. No unneccessary updates.
5. Could write code to maintain and check the alldata. -- This is not an advantage. It's a solution to the problem caused by the derived fields. It should be listed under disadvantages as "Have to write code to maintain and check the alldata".
Disadvantages:
1. Less safe in terms of integrity than triggers.
2. Possible data out of sync.
3. Increased application code, and extra logic that must be placed into every application that might need to update the data (or each routine within your application).
4. Have to write code to periodically maintain and check database integrity.
--
Please look into the indexed views I mentioned earlier, which doesn't suffer from the integrity issues, doesn't require more application logic (Other than you select from one view, and update a base table, but you're doing that from stored procedures now anyhow, right?) If the indexed views don't work for you, you could also use a BEFORE UPDATE/INSERT trigger, which aren't the nicest thing in the world to write, but keeps many of your listed advantages, and eliminates the disadvantages. (Guaranteed database integrity + no unneccessary updates + smaller lock time + faster than a client-side lookup)
As for your confusion on what you shouldn't use... (Triggers, UNIONS, Exits (did you mean EXISTS?), and joins)... Sounds like you've gotten information from someone who fell in love with a dumb record manager from the 1980's (Paradox, BTrieve, ISAM, or MySQL -- granted the latter is evolving beyond it's dumb record manager roots).
|||
I agree with Motley, because there is a reason the Object world have not created a replacement for Peter Chen 1976, the math that gave us what is now DRI(declarative referential integrity). I think you will run into data integrity problems as your application grow.
But the last part of your post is ok except JOINS because INNER JOINS with the AND operator will not eat up your CPU if you use management studio to show you execution cost and use the Profiler to optimize the cost of your SQL statement.
|||indexed views > I would Love to use them butif I am not mistaken they have lots of rules to follow and I got to buy the enterprise editon and the 4 CPU price of SQL is4 X 20.000$where the standart edition is 4 X 6.000 USD for web applications.
for now I 'll be taking the risk of replication data inorder to have performance.
*I prefered to have a normalized and a clean data model where I can reuse the data logic without dublicating it.Pratically I could not find a way out for that in the physical design.
**Faster than triggers --> I might use threads.(which might mean more trouble but...)
|||You are mistaken. They work in standard edition (Even SQL Express/MSDE), however, you must name the view directly. In enterprise edition if you have an indexed view, and a select/update doesn't mention the view, but it would benefit from it, it will automatically use it.
However, you are correct, there are some limitations, one of which is no LEFT JOINS, and no UNION. Just do them later (In another view that bases itself off the indexed view and left joins/unions with other data).
The nice part is, you can build it, test it, and see if it'll fit your needs in a matter of minutes.
How to calculate a value which takes the prev rows value for calculation.
Hi All,
Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.
For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).
I tried the following formula
if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}
but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??
Thanks in advance
RashmiHi All,
Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.
For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).
I tried the following formula
if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}
but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??
Thanks in advance
Rashmi
Please can anyone suggest a solution? Please can you help Madhi?|||If OnFirstRecord = true Then ({@.CurrDateTime}; Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs})
Else Previous({Test});
Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs});|||this formula will help you
change currentdatetime with ur need.
global datetimevar z;
if OnFirstRecord then
(
currentdatetime;
z:=currentdatetime;
)
else
(
z+10;
z:=z+10
)
Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.
For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).
I tried the following formula
if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}
but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??
Thanks in advance
RashmiHi All,
Please help me with this problem
I have two fields in my report- Production hours and Scheduled Start Time.
For the first record in the report the scheduled start time field will have the current date time field value ,but for consecutive records it is the sum of Prod-hrs+scheduled start time (of the Previous record).
I tried the following formula
if OnFirstRecord then
{@.CurrDateTime}
else
Previous({@.Schd_Start_Time})+{dpRptCSReport.prod_hrs}
but get the error ' A formula cannot refer to itself, either directly or indirectly'
Can you please suggest a way out??
Thanks in advance
Rashmi
Please can anyone suggest a solution? Please can you help Madhi?|||If OnFirstRecord = true Then ({@.CurrDateTime}; Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs})
Else Previous({Test});
Global NumberVar Test :=
{@.Schd_Start_Time})+{dpRptCSReport.prod_hrs});|||this formula will help you
change currentdatetime with ur need.
global datetimevar z;
if OnFirstRecord then
(
currentdatetime;
z:=currentdatetime;
)
else
(
z+10;
z:=z+10
)
How to calculate a date difference in days
Suppose I have these two days fields
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM
ddold 1/1/2005 12:00:00 AM
ddnew 2/1/2007 12:00:00 AM
How can i get the DateDifference of these two dates in days.
Use DateDiff(DateInterval.Day, Fields!Date1.Value, Fields!Date2.Value)
Where date1 is the start date and date2 is end date.
Shyam
|||Hello Kamii,
If you're wanting to do this from your SQL query...
select datediff(d, ddold, ddnew)
If from Reporting Services, use this as your expression...
=DateDiff("d", Fields!ddold.Value, Fields!ddnew.Value)
Jarret
|||Can u please mark my post as answer?Sunday, February 19, 2012
How to build a table which holds varying numbers of fields?
I'm using a stored procedure with multiple parameters, and depending on
the parameters offered, tables with varying fields are returned.
How can I create a table in the layout window which will accomadate
these variations?
Thanks!Only matrixes have dynamic fields... however
for list or table, create the list or table with ALL of the fields, then
conditionally hide/show the fields at run time using the visibility
attribute..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Balding by the handful" wrote:
> I'm using a stored procedure with multiple parameters, and depending on
> the parameters offered, tables with varying fields are returned.
> How can I create a table in the layout window which will accomadate
> these variations?
> Thanks!
>
the parameters offered, tables with varying fields are returned.
How can I create a table in the layout window which will accomadate
these variations?
Thanks!Only matrixes have dynamic fields... however
for list or table, create the list or table with ALL of the fields, then
conditionally hide/show the fields at run time using the visibility
attribute..
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Balding by the handful" wrote:
> I'm using a stored procedure with multiple parameters, and depending on
> the parameters offered, tables with varying fields are returned.
> How can I create a table in the layout window which will accomadate
> these variations?
> Thanks!
>
Subscribe to:
Posts (Atom)