Does anyone know how to pass a parameter from a stored
procedure to a DataAdapter?
the following is my code:
i used the DataAdapter wizard to set its properties with
my stored procedure.
//create a data adapter
SqlDataAdapter da = sqlDataAdapter1;
//create a data set and fill it by
calling Fill method
DataSet ds = new DataSet("Cust");
da.Fill(ds,"Customers");
//attach data set's default view
to the data grid control
DataGrid1.DataSource = ds;
DataGrid1.DataMember = "Customers";
DataGrid1.DataBind();
**When i run it, it asks for the stored procedure's
parameter which is the value of a text field in my asp.net
application.For performance reasons, you don't want to use the wizards to generate
code in ADO.NET -- you'd be much better off just deleting all the
wizard-generated stuff and starting from scratch. I'd recommend
getting a copy of ADO.NET by David Sceppa, Microsoft Press.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Wed, 12 Nov 2003 14:33:58 -0800, "zoe"
<anonymous@.discussions.microsoft.com> wrote:
>Does anyone know how to pass a parameter from a stored
>procedure to a DataAdapter?
>the following is my code:
>i used the DataAdapter wizard to set its properties with
>my stored procedure.
> //create a data adapter
> SqlDataAdapter da =>sqlDataAdapter1;
> //create a data set and fill it by
>calling Fill method
> DataSet ds = new DataSet("Cust");
> da.Fill(ds,"Customers");
>
> //attach data set's default view
>to the data grid control
> DataGrid1.DataSource = ds;
> DataGrid1.DataMember = "Customers";
> DataGrid1.DataBind();
>
>**When i run it, it asks for the stored procedure's
>parameter which is the value of a text field in my asp.net
>application.
Showing posts with label following. Show all posts
Showing posts with label following. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Calling stored proc with default params from .NET not working.
All,
I have the following :
ALTERPROCEDURE [dbo].[sp_FindNameJon]
@.NameNamevarchar(50)='',
@.NameAddressvarchar(50)='',
@.NameCityvarchar(50)='',
@.NameStatevarchar(2)='',
@.NameZipvarchar(15)='',
@.NamePhonevarchar(25)='',
@.NameTypeIdint=0,
@.BureauIdint,
@.Pageint=1,
@.Countint=100000
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SETNOCOUNTON;
DECLARE @.SqlStringnvarchar(3000),@.SelectClausenvarchar(1000), @.FromClausenvarchar(1000),@.WhereClausenvarchar(1000)
DECLARE @.ParentSqlStringnvarchar(4000)
DECLARE @.Startint, @.Endint
INSERTinto aaJonTempvalues(@.Page,'here2', @.NameCity);
And inside of aaJonTemp, I have the following :
How is this possible? If @.Page or @.NameCity is NULL, how come it doesn't default to a value in the stored proc?
Thx
jonpfl
Because a parameter value being NULL is not the same thing as not supplying a parameter at all.
The defaults basically say... If the user hasn't supplied the parameter, then use this. You have supplied a parameter, although the value is NULL. If you want to make it so that if someone sends in a NULL and you want to change it to something else, then you need to code that.
IF @.param IS NULL SET @.param=...
sqlCalling Stored Proc from a Function
I know that the rule is that you can only call an extended Stored Proc from
a
Function - NOT a normal Stored Proc which gives the following error.
"Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
Only functions and extended stored procedures can be executed from within a
function."
So, I tried to call it using sp_executesql() which IS an extended stored
proc with no luck'
Any help much appreciated. THanksYou can't use dynamic SQL in a function... What are you trying to do?
Explain your problem and perhaps we can figure out a better way to
accomplish whatever it is you need...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:BDC0F216-2E7E-4489-A6BE-076DD5BD47D8@.microsoft.com...
> I know that the rule is that you can only call an extended Stored Proc
from a
> Function - NOT a normal Stored Proc which gives the following error.
> "Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
> Only functions and extended stored procedures can be executed from within
a
> function."
> So, I tried to call it using sp_executesql() which IS an extended stored
> proc with no luck'
> Any help much appreciated. THanks|||Thanks for the reply Adam,
I am actually trying to emulate an ORACLE sequence.
I need to do a bulk insert and create "ID's" at the same time. We do not
have IDENTITY columns in our DB. Ifigured out how to do this but because I
need to do an update statement, I have to use a stored proc as you can only
do select's in a scalar function. So, to get around this, I wanted to call m
y
stored proc from a function. I need a function so I can call it in my select
.
Here are some code details. Thanks. The details are long as I was asking
about methods to achieve this in another thread but had no real resolution s
o
was just asking about the generic calling procs from functions here. Much
Thanks :0)
-- Fake Sequence to hold a number stream
CREATE TABLE sequences
(
seq varchar(100) primary key,
sequence_id int
);
ALTER PROCEDURE nextval
@.sequence varchar(100),
@.sequence_id INT OUTPUT
AS
BEGIN
set @.sequence_id = -1
UPDATE sequences
SET @.sequence_id = sequence_id = sequence_id + 1
WHERE seq = @.sequence
RETURN @.sequence_id
END
-- Function to call Stored Proc
ALTER function nextval2
( @.sequence varchar(100)) returns int
AS
BEGIN
declare @.sequence_id int
DECLARE @.sequence_id int
--EXEC dbo.nextval 'TestSeq', @.sequence_id OUTPUT
-- OR
-- exec sp_executesql N'dbo.nextval ''TestSeq'', @.sequence_id OUTPUT '
from sequences
RETURN @.sequence_id
END
go
-- Insert statement using Function
insert into glp.rf_contact
select dbo.nextval2('TestSeq'), name
from glp.contact
"Adam Machanic" wrote:
> You can't use dynamic SQL in a function... What are you trying to do?
> Explain your problem and perhaps we can figure out a better way to
> accomplish whatever it is you need...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:BDC0F216-2E7E-4489-A6BE-076DD5BD47D8@.microsoft.com...
> from a
> a
>
>|||"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
> I am actually trying to emulate an ORACLE sequence.
> I need to do a bulk insert and create "ID's" at the same time. We do not
> have IDENTITY columns in our DB. Ifigured out how to do this but because I
Don't have doesn't mean can't have :)
I highly recommend that you use an IDENTITY if you need that
functionality -- rolling your own will not work well for a variety of
reasons. The primary issue is that it will force all transactions inserting
into or updating the table to be serialized, which will totally destroy
concurrency. In addition, you really don't want a UDF called for every row
of a BULK INSERT, unless you want it to take 3 days to finish...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Amelia,
> -- Insert statement using Function
> insert into glp.rf_contact
> select dbo.nextval2('TestSeq'), name
> from glp.contact
As an alternative, you can use the following:
DECLARE @.rc AS INT, @.seqid AS INT;
SELECT IDENTITY(INT, 1, 1) AS id, name INTO #T FROM glp.contact;
SET @.rc = @.@.rowcount;
UPDATE sequences
SET @.seqid = sequence_id, sequence_id = sequence_id + @.rc;
INSERT INTO t1 SELECT @.seqid + id, name FROM #T;
DROP TABLE #T;
You can even encapsulate the whole process in a trigger and allow the users
to simply invoke the INSERTs.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
> Thanks for the reply Adam,
> I am actually trying to emulate an ORACLE sequence.
> I need to do a bulk insert and create "ID's" at the same time. We do not
> have IDENTITY columns in our DB. Ifigured out how to do this but because I
> need to do an update statement, I have to use a stored proc as you can
> only
> do select's in a scalar function. So, to get around this, I wanted to call
> my
> stored proc from a function. I need a function so I can call it in my
> select.
> Here are some code details. Thanks. The details are long as I was asking
> about methods to achieve this in another thread but had no real resolution
> so
> was just asking about the generic calling procs from functions here. Much
> Thanks :0)
> -- Fake Sequence to hold a number stream
> CREATE TABLE sequences
> (
> seq varchar(100) primary key,
> sequence_id int
> );
> ALTER PROCEDURE nextval
> @.sequence varchar(100),
> @.sequence_id INT OUTPUT
> AS
> BEGIN
> set @.sequence_id = -1
> UPDATE sequences
> SET @.sequence_id = sequence_id = sequence_id + 1
> WHERE seq = @.sequence
> RETURN @.sequence_id
> END
>
> -- Function to call Stored Proc
> ALTER function nextval2
> ( @.sequence varchar(100)) returns int
> AS
> BEGIN
> declare @.sequence_id int
> DECLARE @.sequence_id int
>
> --EXEC dbo.nextval 'TestSeq', @.sequence_id OUTPUT
> -- OR
> -- exec sp_executesql N'dbo.nextval ''TestSeq'', @.sequence_id OUTPUT '
> from sequences
> RETURN @.sequence_id
> END
> go
>
> -- Insert statement using Function
> insert into glp.rf_contact
> select dbo.nextval2('TestSeq'), name
> from glp.contact
>
>
> "Adam Machanic" wrote:
>|||Thanks so much for the Responses Adam and Itzik,
I cannot make changes to the table so adding and Identity Column is out but
your suggestion Itzik is great and may well just work! I was working along
the same lines as this but this is better than mine as it is still using a
sequence table instead of doing it all manually! That's up there for thinkin
g.
Thanks again :0)
"Itzik Ben-Gan" wrote:
> Amelia,
>
> As an alternative, you can use the following:
> DECLARE @.rc AS INT, @.seqid AS INT;
> SELECT IDENTITY(INT, 1, 1) AS id, name INTO #T FROM glp.contact;
> SET @.rc = @.@.rowcount;
> UPDATE sequences
> SET @.seqid = sequence_id, sequence_id = sequence_id + @.rc;
> INSERT INTO t1 SELECT @.seqid + id, name FROM #T;
> DROP TABLE #T;
> You can even encapsulate the whole process in a trigger and allow the user
s
> to simply invoke the INSERTs.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
>
>
a
Function - NOT a normal Stored Proc which gives the following error.
"Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
Only functions and extended stored procedures can be executed from within a
function."
So, I tried to call it using sp_executesql() which IS an extended stored
proc with no luck'
Any help much appreciated. THanksYou can't use dynamic SQL in a function... What are you trying to do?
Explain your problem and perhaps we can figure out a better way to
accomplish whatever it is you need...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:BDC0F216-2E7E-4489-A6BE-076DD5BD47D8@.microsoft.com...
> I know that the rule is that you can only call an extended Stored Proc
from a
> Function - NOT a normal Stored Proc which gives the following error.
> "Server: Msg 557, Level 16, State 2, Procedure nextval2, Line 9
> Only functions and extended stored procedures can be executed from within
a
> function."
> So, I tried to call it using sp_executesql() which IS an extended stored
> proc with no luck'
> Any help much appreciated. THanks|||Thanks for the reply Adam,
I am actually trying to emulate an ORACLE sequence.
I need to do a bulk insert and create "ID's" at the same time. We do not
have IDENTITY columns in our DB. Ifigured out how to do this but because I
need to do an update statement, I have to use a stored proc as you can only
do select's in a scalar function. So, to get around this, I wanted to call m
y
stored proc from a function. I need a function so I can call it in my select
.
Here are some code details. Thanks. The details are long as I was asking
about methods to achieve this in another thread but had no real resolution s
o
was just asking about the generic calling procs from functions here. Much
Thanks :0)
-- Fake Sequence to hold a number stream
CREATE TABLE sequences
(
seq varchar(100) primary key,
sequence_id int
);
ALTER PROCEDURE nextval
@.sequence varchar(100),
@.sequence_id INT OUTPUT
AS
BEGIN
set @.sequence_id = -1
UPDATE sequences
SET @.sequence_id = sequence_id = sequence_id + 1
WHERE seq = @.sequence
RETURN @.sequence_id
END
-- Function to call Stored Proc
ALTER function nextval2
( @.sequence varchar(100)) returns int
AS
BEGIN
declare @.sequence_id int
DECLARE @.sequence_id int
--EXEC dbo.nextval 'TestSeq', @.sequence_id OUTPUT
-- OR
-- exec sp_executesql N'dbo.nextval ''TestSeq'', @.sequence_id OUTPUT '
from sequences
RETURN @.sequence_id
END
go
-- Insert statement using Function
insert into glp.rf_contact
select dbo.nextval2('TestSeq'), name
from glp.contact
"Adam Machanic" wrote:
> You can't use dynamic SQL in a function... What are you trying to do?
> Explain your problem and perhaps we can figure out a better way to
> accomplish whatever it is you need...
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:BDC0F216-2E7E-4489-A6BE-076DD5BD47D8@.microsoft.com...
> from a
> a
>
>|||"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
> I am actually trying to emulate an ORACLE sequence.
> I need to do a bulk insert and create "ID's" at the same time. We do not
> have IDENTITY columns in our DB. Ifigured out how to do this but because I
Don't have doesn't mean can't have :)
I highly recommend that you use an IDENTITY if you need that
functionality -- rolling your own will not work well for a variety of
reasons. The primary issue is that it will force all transactions inserting
into or updating the table to be serialized, which will totally destroy
concurrency. In addition, you really don't want a UDF called for every row
of a BULK INSERT, unless you want it to take 3 days to finish...
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||Amelia,
> -- Insert statement using Function
> insert into glp.rf_contact
> select dbo.nextval2('TestSeq'), name
> from glp.contact
As an alternative, you can use the following:
DECLARE @.rc AS INT, @.seqid AS INT;
SELECT IDENTITY(INT, 1, 1) AS id, name INTO #T FROM glp.contact;
SET @.rc = @.@.rowcount;
UPDATE sequences
SET @.seqid = sequence_id, sequence_id = sequence_id + @.rc;
INSERT INTO t1 SELECT @.seqid + id, name FROM #T;
DROP TABLE #T;
You can even encapsulate the whole process in a trigger and allow the users
to simply invoke the INSERTs.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Amelia" <Amelia@.discussions.microsoft.com> wrote in message
news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
> Thanks for the reply Adam,
> I am actually trying to emulate an ORACLE sequence.
> I need to do a bulk insert and create "ID's" at the same time. We do not
> have IDENTITY columns in our DB. Ifigured out how to do this but because I
> need to do an update statement, I have to use a stored proc as you can
> only
> do select's in a scalar function. So, to get around this, I wanted to call
> my
> stored proc from a function. I need a function so I can call it in my
> select.
> Here are some code details. Thanks. The details are long as I was asking
> about methods to achieve this in another thread but had no real resolution
> so
> was just asking about the generic calling procs from functions here. Much
> Thanks :0)
> -- Fake Sequence to hold a number stream
> CREATE TABLE sequences
> (
> seq varchar(100) primary key,
> sequence_id int
> );
> ALTER PROCEDURE nextval
> @.sequence varchar(100),
> @.sequence_id INT OUTPUT
> AS
> BEGIN
> set @.sequence_id = -1
> UPDATE sequences
> SET @.sequence_id = sequence_id = sequence_id + 1
> WHERE seq = @.sequence
> RETURN @.sequence_id
> END
>
> -- Function to call Stored Proc
> ALTER function nextval2
> ( @.sequence varchar(100)) returns int
> AS
> BEGIN
> declare @.sequence_id int
> DECLARE @.sequence_id int
>
> --EXEC dbo.nextval 'TestSeq', @.sequence_id OUTPUT
> -- OR
> -- exec sp_executesql N'dbo.nextval ''TestSeq'', @.sequence_id OUTPUT '
> from sequences
> RETURN @.sequence_id
> END
> go
>
> -- Insert statement using Function
> insert into glp.rf_contact
> select dbo.nextval2('TestSeq'), name
> from glp.contact
>
>
> "Adam Machanic" wrote:
>|||Thanks so much for the Responses Adam and Itzik,
I cannot make changes to the table so adding and Identity Column is out but
your suggestion Itzik is great and may well just work! I was working along
the same lines as this but this is better than mine as it is still using a
sequence table instead of doing it all manually! That's up there for thinkin
g.
Thanks again :0)
"Itzik Ben-Gan" wrote:
> Amelia,
>
> As an alternative, you can use the following:
> DECLARE @.rc AS INT, @.seqid AS INT;
> SELECT IDENTITY(INT, 1, 1) AS id, name INTO #T FROM glp.contact;
> SET @.rc = @.@.rowcount;
> UPDATE sequences
> SET @.seqid = sequence_id, sequence_id = sequence_id + @.rc;
> INSERT INTO t1 SELECT @.seqid + id, name FROM #T;
> DROP TABLE #T;
> You can even encapsulate the whole process in a trigger and allow the user
s
> to simply invoke the INSERTs.
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "Amelia" <Amelia@.discussions.microsoft.com> wrote in message
> news:E0FAD578-E8AB-4AC3-98F5-2765B15A56FB@.microsoft.com...
>
>
Thursday, March 22, 2012
Calling Oracle Stored Procedure
Hi all
I am trying to call oracle stored procedure from SRSS 2005. I am using the syntax { Call s_test_rcur()} .
I am getting following error.
Any suggestions?
Thanks in advance
Mvr
An error occurred while retrieving the parameters in the query.
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at line 1
ADDITIONAL INFORMATION:
ORA-00911: invalid character
ORA-06512: at "SYS.DBMS_UTILITY", line 68
ORA-06512: at line 1
(System.Data.OracleClient)
Here is the Oracel stored proc.
TYPE rc_test IS REF CURSOR;
PROCEDURE s_test_rcur (
po_test_rc OUT rc_test, -- returns a record set
po_error OUT INTEGER
)
IS
BEGIN
g_err_level := 1;
OPEN po_test_rc FOR
SELECT a.ssn_id,
TO_CHAR (a.acad_yr) || TO_CHAR (a.acad_yr + 1) acad_yr,
RPAD (NVL (last_name, ' '), 30, ' ') last_name,
RPAD (NVL (first_name, ' '), 30, ' ') first_name,
NVL (middle_initial, ' ') middle_initial
from test_table
order by last_name;
po_error := 0;
EXCEPTION
WHEN OTHERS
THEN
po_error := -1;
g_error_code := SQLCODE;
g_error_msg :=
'Err level :' ||
TO_CHAR (g_err_level) ||
' ' ||
SUBSTR (SQLERRM, 1, 250);
END;
You may want to read the following related thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=356162&SiteID=1
Basically, you need to write a wrapper stored procedure that has only one OUT REF cursor and no other OUT parameters.
-- Robert
Monday, March 19, 2012
Calling All Dynamic SQL Gods!
Hello
I'm desperate for help with the following dynamic SQL. It used to work for
ages but suddenly stopped working today! I can't recall changing anything of
importance.. So I say. Anyway, I'm getting this error: "Cannot use empty
object or column names. Use a single space if necessary."
I've identified the location within the script that causes this message it's
this line:
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
3rd line of Set @.cmdSQL =.
I'm desperate for help with the following dynamic SQL. It used to work for
ages but suddenly stopped working today! I can't recall changing anything of
importance.. So I say. Anyway, I'm getting this error: "Cannot use empty
object or column names. Use a single space if necessary."
I've identified the location within the script that causes this message it's
this line:
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
3rd line of Set @.cmdSQL =.
I've been trying to insert a single space between "" which eliminates half
of the error but I can't figure out what quotes to use around 'MG' and 'MA'.
I'd be grateful if you can have a look at this and let me know how to
correct this problem.
Like I said it used to work and I'm perplexed about this sudden error. Is
there any change that can cause this behaviour?
Many thanks for your efforts!!
Have a nice day!!
Martin
Paging Script:
CREATE PROCEDURE dbo.sp_ListKeyOut(
@.page_number INT,
@.number_of_records INT,
@.cmdWHERE VARCHAR(200),
@.cmdORDERBY VARCHAR(200)
) AS
SET NOCOUNT ON
DECLARE
@.SizeString VARCHAR(5),
@.PrevString VARCHAR(5),
@.cmdSQL varchar(2000)
SET @.SizeString = CONVERT(VARCHAR, @.number_of_records)
SET @.PrevString = CONVERT(VARCHAR, @.number_of_records * (@.page_number - 1))
SET QUOTED_IDENTIFIER OFF
SET @.cmdSQL = 'SELECT COALESCE((i2b_vw_contact.Firstname + CHAR(32) +
i2b_vw_contact.Lastname),i2b_vw_company.CompanyNam e) AS CName,
i2b_vw_keytransactionlog.KeyTransactionLogID,
i2b_vw_keytransactionlog.KeyID,
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
i2b_vw_address.Address1 AS PropertyAddress, i2b_vw_contact.MobileNo,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_vw_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_vw_keytransactionlog.Retur nByDate,104) AS
ReturnByDate
FROM i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN'
IF @.cmdWHERE IS NULL OR @.cmdWHERE = ''
BEGIN
EXEC(
@.cmdSQL +
'(SELECT TOP ' + @.SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID NOT IN
(SELECT TOP ' + @.PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
ORDER BY ' + @.cmdORDERBY + ')
ORDER BY ' + @.cmdORDERBY + ') ORDER BY ' + @.cmdORDERBY
)
-- EXEC('SELECT (COUNT(*) - 1)/' + @.SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog')
END
ELSE
BEGIN
EXEC(
@.cmdSQL +
'(SELECT TOP ' + @.SizeString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @.cmdWHERE + ' AND KeyTransactionLogID NOT IN
(SELECT TOP ' + @.PrevString + ' KeyTransactionLogID FROM
i2b_vw_keytransactionlog
LEFT JOIN i2b_vw_contact ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_contact.ContactID AND i2b_vw_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_vw_company ON (i2b_vw_keytransactionlog.EntityID =
i2b_vw_company.CompanyID AND i2b_vw_keytransactionlog.IsContact = 0)
JOIN i2b_vw_key ON (i2b_vw_keytransactionlog.KeyID = i2b_vw_key.KeyID)
JOIN i2b_vw_property on (i2b_vw_key.PropertyID = i2b_vw_property.PropertyID)
JOIN i2b_vw_address ON (i2b_vw_property.AddressID =
i2b_vw_address.AddressID)
JOIN i2b_vw_proguser AS A ON (i2b_vw_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE ' + @.cmdWHERE + ' ORDER BY ' + @.cmdORDERBY + ')
ORDER BY ' + @.cmdORDERBY + ') ORDER BY ' + @.cmdORDERBY
)
-- EXEC('SELECT (COUNT(*) - 1)/' + @.SizeString + ' + 1 AS PageCount FROM
i2b_vw_keytransactionlog WHERE ' + @.cmdWHERE)
END
SET QUOTED_IDENTIFIER ON
RETURN 0
GOMartin Feuersteiner (theintrepidfox@.hotmail.com) writes:
> I'm desperate for help with the following dynamic SQL. It used to work
> for ages but suddenly stopped working today! I can't recall changing
> anything of importance.. So I say. Anyway, I'm getting this error:
> "Cannot use empty object or column names. Use a single space if
> necessary."
In T-SQL there are two ways to delimit a string literal: '' and "". ANSI
SQL permits only '', and reserves "" for quoting identifiers with
"funny" characters, so you can have column names like "order date".
Of this reason, SQL Server offers a setting QUOTED_IDENTIFIER that can
be on or off. If ON, you can only use '' to delimit string literal, if
off, you can use both '' and "".
There is functionality (indexed views, indexed computed columns) in SQL
Server that is only available if QUOTED_IDENTIFIER is ON, so stick with this
setting. This is also the default setting with many client libraries.
Unfortunately, Enterprise Manager has the bad habit of setting it off, and
since this setting is saved with the procedure this can cause confusinon at
times.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hi Erland
I knew you would go for it :-)
Well, I know that stuff with '' and ", my problem is that I can't
comprehend how many of ' I need to use e.g. on each site of 'MG' to get
it working. It causes a brain overflow!! Can you please provide me with
a hands-on example perhaps using my code?
(i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
Many thanks for your efforts!
Have a nice day!
Martin
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Martin Ashcroft (theintrepidfox@.hotmail.com) writes:
> I knew you would go for it :-)
> Well, I know that stuff with '' and ", my problem is that I can't
> comprehend how many of ' I need to use e.g. on each site of 'MG' to get
> it working. It causes a brain overflow!! Can you please provide me with
> a hands-on example perhaps using my code?
> (i2b_vw_key.KeyCode + CASE ManagementSet WHEN 1 THEN " MG" ELSE "" END +
> CASE Master WHEN 1 THEN " MA" ELSE "" END) AS KeyCode,
Check out http://www.sommarskog.se/dynamic_sq...#good_practices
for some tips.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thank you Erland!
It helped!
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
calling a udf from a computed field
I am trying desparately to build a user defined function on the Microsoft SQL Server 2000 viewer and when it is performed I get the following message:
[Microsoft][ODB SQL Server Driver][SQL Server] Maximum stored procedure, function, trigger, or view nesting level exceeded
Is there any problem using sql MAX() built in function? How can I resolve this issue?
The UDF code is:
CREATE FUNCTION TestFunc (@.Syn int)
RETURNS int AS
BEGIN
IF @.Syn IS NULL
RETURN 1
DECLARE @.NewPersonID INT
SELECT @.NewPersonID = MAX(PersonID)
FROM GO_Test
WHERE SynagogueID = @.Syn
RETURN ISNULL(@.NewPersonID + 1, 1)
END
I call the function from the 'Formula' propery of a computed field of a table like this:
([dbo].[TestFunc]([SynagogueID]))
while SynagogueID is a field on the same table.
The error message indicates that you have reached the maximum nesting level which is 32. But your code doesn't seem to indicate that this error is due to the UDF logic. What operation are you performing when you get the error? You need to trace the SQL statements and find the exact one that is causing the error. Look for nested triggers, UDFs with recursion etc.
Lastly, you should avoid using scalar UDFs especially those that perform data access in computed columns. The performance of queries referencing the computed column will suffer badly. Instead, you can define a view with a sub-select that does the same thing but much more efficiently. Ex:
select ..., coalesce((select max(g.PersonID) from GO_Test AS g where g.SynagogueID = t.SynagogueID), 0) + 1 as PerId
from tbl as t
Thursday, March 8, 2012
Calling .Net Assembly
I create a KPI with the following expression:
([Measures].[TDV Result],
StrToMember("[TDV Result Date].[Year - Quarter - Month - Date]
.[Month].&[" + MdxFunctions.MdxFunctions.Timeframe.GetCurrentMonth() + "]"),
[Entities].[EntityType].[Site],
[TDVs].[Parameter - Material].&[Coal])
MdxFunctions.MdxFunctions.Timeframe.GetCurrentMonth() is a .Net assembly and returns a string like 2007-02-01. I deploy and look at the Browser View but there is an error stating that "The '2007-02-01' string cannont be converted to the date type". How do I convert the date string?
You are passing 2007-02-01 as a key, are you sure that key of the time dimension is a string and not a date? You should either pass a key or remote &: ("[TDV Result Date].[Year - Quarter - Month - Date].[Month].["|||
I'm not entirely sure, but if I had to guess I would try adding the time component. If you are using a date datatype for the key of your month attribute, it might be expecting a value in the form of "yyyy-mm-dd hh:mm:ss"
A couple of other observations:
I think you should be able to write your .Net function to return an actual member instead of a string, which mean you would not need the StrToMember Call everytime you used GetCurrentMonth (You could look at the LinkMember function from http://www.codeplex.com/ASStoredProcedures )|||Thanks, I did notice I was missing the time part after I sent this post. I would like to just use VBA but the problem is I have other functions to get the current week, quarter, and other functions that require lengthy VBA code. If I put it in a calculation then I need to duplicate it for every cube. I tried the Time Intelligence but only get NA.|||
That's cool, I realise that there are more considerations than pure speed. :)
I just thought you should be aware that there is a performance impact, particularly if you had queries that displayed the KPI for a large number of members. But if you are now up and running and the performance is acceptable then there is no need to worry.
Wednesday, March 7, 2012
call report with datas updated
Hello,
I use sql reporting services to show reports with url and to create snapshot with his web services.
My problem is the following:
- i show a report (linked to a database with data source and dataset) with an url like :
http://srv/Reportserver/BOURGOGNE/Perso&rs:Format=HTML4.0
&rs:Command=Render&annee=2004&id=0580639E
- i change data in the database
- when a recall the report with the previous url, the data has not changed, and i must click on refresh button next the export button.
- i would like that this refresh made automatically when i call a report. for more precisions, the property/execution on this report is set to ' 'd..
thank to help me and sorry for my english...
floben.
I use sql reporting services to show reports with url and to create snapshot with his web services.
My problem is the following:
- i show a report (linked to a database with data source and dataset) with an url like :
http://srv/Reportserver/BOURGOGNE/Perso&rs:Format=HTML4.0
&rs:Command=Render&annee=2004&id=0580639E
- i change data in the database
- when a recall the report with the previous url, the data has not changed, and i must click on refresh button next the export button.
- i would like that this refresh made automatically when i call a report. for more precisions, the property/execution on this report is set to ' 'd..
thank to help me and sorry for my english...
floben.
It seems like you are rendering from session. On your URL above, append the following argument: rs:ClearSession=true
This will make sure the report gets reprocessed entirely.
See also: http://msdn2.microsoft.com/ms224719
-- Robert
|||thank, it was that..Saturday, February 25, 2012
call a UDF from another server <> Authentication
Hello
I'm trying to call a UDF from another server with the following command:
select i fromopenquery([10.0.10.240],'[survey].[dbo].[ufnGetAxis_Ana] as i')
error:
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.
Can someone give me some advice on how to pass username and password to access an object on another server?
Many thanks!
hi, first thing is it the SQL Server or other one?
if it is SQL Server, then it quit easy, you need to create a Linked Server(Direct IP Address will not work) using
sp_addlinkedserver [ @.server= ] 'server' [ , [ @.srvproduct= ] 'product_name' ] [ , [ @.provider= ] 'provider_name' ] [ , [ @.datasrc= ] 'data_source' ] [ , [ @.location= ] 'location' ] [ , [ @.provstr= ] 'provider_string' ] [ , [ @.catalog= ] 'catalog' ] then use the openquery asOPENQUERY ( linked_server ,'query' )- linked_server
Is an identifier representing the name of the linked server.
- 'query'
Is the query string executed in the linked server. The maximum length of the string is 8 KB
now try
select i fromopenquery(<linked server name>,'[survey].[dbo].[ufnGetAxis_Ana] as i')
Regards,
Thanks.
Gurpreet S. Gill
|||Many thanks Gurpreet
Are you sure about the syntax?
I get a syntax error when executing select i from openquery(myserver,'survey.dbo.ufnGetAxis_Ana(2,7) as i')
Many thanks!
Worf
|||hi try this
select i FROMopenquery(ha9,'select pubs.dbo.myFunction() as i')
this is the command, where 'ha9' is linked server , at the remote end 'pubs' is the database name, 'dbo' is owner and 'myFunction' is the name of the function, in your case it should be
select i from openquery(myserver,'select survey.dbo.ufnGetAxis_Ana(2,7) as i')
NOTE: if your Remote Serever is SQL Server, then , name of the server is the Linked server name, also need to set the login & password.
Regards,
Thanks.
Gurpreet S. Gill
|||It works Gurpreet!!
Many many thanks!!!
Worf
call a UDF from another server <> Authentication
Hello
I'm trying to call a UDF from another server with the following command:
select i from openquery([10.0.10.240], '[survey].[dbo].[ufnGetAxis_Ana] as i')
error:
[OLE/DB provider returned message: Invalid authorization specification]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
Msg 7399, Level 16, State 1, Line 3
OLE DB provider 'SQLOLEDB' reported an error. Authentication failed.
Can someone give me some advice on how to pass username and password to access an object on another server?
Many thanks!
hi, first thing is it the SQL Server or other one?
if it is SQL Server, then it quit easy, you need to create a Linked Server(Direct IP Address will not work) using
sp_addlinkedserver [ @.server= ] 'server' [ , [ @.srvproduct= ] 'product_name' ][ , [ @.provider= ] 'provider_name' ]
[ , [ @.datasrc= ] 'data_source' ]
[ , [ @.location= ] 'location' ]
[ , [ @.provstr= ] 'provider_string' ]
[ , [ @.catalog= ] 'catalog' ] then use the openquery asOPENQUERY ( linked_server ,'query' )
- linked_server
Is an identifier representing the name of the linked server.
- 'query'
Is the query string executed in the linked server. The maximum length of the string is 8 KB
now try
select i from openquery(<linked server name>, '[survey].[dbo].[ufnGetAxis_Ana] as i')
Regards,
Thanks.
Gurpreet S. Gill
|||Many thanks Gurpreet
Are you sure about the syntax?
I get a syntax error when executing select i from openquery(myserver,'survey.dbo.ufnGetAxis_Ana(2,7) as i')
Many thanks!
Worf
|||hi try this
select i FROM openquery(ha9,'select pubs.dbo.myFunction() as i')
this is the command, where 'ha9' is linked server , at the remote end 'pubs' is the database name, 'dbo' is owner and 'myFunction' is the name of the function, in your case it should be
select i from openquery(myserver,'select survey.dbo.ufnGetAxis_Ana(2,7) as i')
NOTE: if your Remote Serever is SQL Server, then , name of the server is the Linked server name, also need to set the login & password.
Regards,
Thanks.
Gurpreet S. Gill
|||
It works Gurpreet!!
Many many thanks!!!
Worf
CalendarTransform
when trying to install the calendarTransform component i receive following error:
the command "....\..\regcomponent calendarTransform" exited with code 1
any idea what it means?
It would help to know what the calendar transform component is.|||http://www.microsoft.com/downloads/details.aspx?familyid=e603bde7-44bb-409a-890f-ed94a20b6710&displaylang=en|||There should be more to it than that message. If using the build event scroll up in the output window for a full message. Alternatively run the command by hand in a DOS window and see what comes out. My guess is that you have not assigned a strong name key file, see the Signing tab of the project properties. You cannot GAC a component unless it is signed.|||KirkHaselden wrote:
It would help to know what the calendar transform component is.
darren:
after running the command in DOS by hand it works !!
thanks for your help.
peter
Calendar Report Layout Help
Hello
I need to create something like the following table:
MON TUE WED THU FRI SAT SUN
01/01/07 02/01/07 03/01/07 04/01/07 05/01/07 06/01/07 07/01/07
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
08/01/07 09/01/07 10/01/07 11/01/07 12/01/07 13/01/07 14/01/07
Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field Blank Field
The user would enter the start date, in this case the 1st Jan 07 and then this would populate a table. This seems like it should be so simple but I can't work it out, can anyone help please?
Cheers
I sorted it using a cross tab query.Friday, February 24, 2012
Calculations: Division calculation is returning incorrect results
Hi everyone,
second (longer) MDX query is just to show how I managed to get correct results.
I created the following simple MDX query containing a Division calculation (listed below):
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE MEMBER [Adventure Works].[Employee].[Employee].[All Employees].[BDA13F64-E5D5-418B-A4,A6,1F,C5,B8,44,2,54] AS ( [Employee].[Employee].[All Employees] ) / ( [Employee].[Employee].&[290] ) , SOLVE_ORDER = 1
GO
SELECT
[Delivery Date].[Date].MEMBERS DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS ,
{[Employee].[Employee].[All Employees].[BDA13F64-E5D5-418B-A4,A6,1F,C5,B8,44,2,54],[Employee].[Employee].MEMBERS} DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
GO
DROP MEMBER [Adventure Works].[Employee].[Employee].[All Employees].[BDA13F64-E5D5-418B-A4,A6,1F,C5,B8,44,2,54]
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
When executing the query, I noticed that some of the values of the calculation were incorrect (which I verified by hand calculating the expected result). I noticed that the value becomes wrong starting at the "July 13, 2002" column until the end of the row. Oddly enough, though, when I add the "NON EMPTY" keywords to remove nulls from both axes and explicitly specify each hierarchy's default members for the slice, the calculation then returns the correct value:
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
CREATE MEMBER [Adventure Works].[Employee].[Employee].[All Employees].[BDA13F64-E5D5-418B-A4,A6,1F,C5,B8,44,2,54] AS ( [Employee].[Employee].[All Employees] ) / ( [Employee].[Employee].&[290] ) , SOLVE_ORDER = 1
GO
SELECT
NON EMPTY [Delivery Date].[Date].MEMBERS DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON COLUMNS ,
NON EMPTY {[Employee].[Employee].[All Employees].[BDA13F64-E5D5-418B-A4,A6,1F,C5,B8,44,2,54],[Employee].[Employee].MEMBERS} DIMENSION PROPERTIES MEMBER_NAME, MEMBER_TYPE, DESCRIPTION, PARENT_UNIQUE_NAME, HIERARCHY_UNIQUE_NAME ON ROWS
FROM [Adventure Works]
WHERE
( [Account].[Account].defaultmember, [Account].[Account Number].defaultmember, [Account].[Account Type].defaultmember, [Account].[Accounts].defaultmember, [Customer].[Address].defaultmember, [Customer].[City].defaultmember, [Customer].[Commute Distance].defaultmember, [Customer].[Country].defaultmember, [Customer].[Customer].defaultmember, [Customer].[Customer Geography].defaultmember, [Customer].[Education].defaultmember, [Customer].[Email Address].defaultmember, [Customer].[Gender].defaultmember, [Customer].[Home Owner].defaultmember, [Customer].[Marital Status].defaultmember, [Customer].[Number of Cars Owned].defaultmember, [Customer].[Number of Children At Home].defaultmember, [Customer].[Occupation].defaultmember, [Customer].[Phone].defaultmember, [Customer].[Postal Code].defaultmember, [Customer].[State-Province].defaultmember, [Customer].[Total Children].defaultmember, [Customer].[Yearly Income].defaultmember, [Date].[Calendar].defaultmember, [Date].[Calendar Quarter of Year].defaultmember, [Date].[Calendar Semester of Year].defaultmember, [Date].[Calendar Year].defaultmember, [Date].[Date].defaultmember, [Date].[Day Name].defaultmember, [Date].[Day of Month].defaultmember, [Date].[Day of Week].defaultmember, [Date].[Day of Year].defaultmember, [Date].[Fiscal].defaultmember, [Date].[Fiscal Quarter of Year].defaultmember, [Date].[Fiscal Semester of Year].defaultmember, [Date].[Fiscal Year].defaultmember, [Date].[Month of Year].defaultmember, [Date].[Week of Year].defaultmember, [Delivery Date].[Calendar].defaultmember, [Delivery Date].[Calendar Quarter of Year].defaultmember, [Delivery Date].[Calendar Semester of Year].defaultmember, [Delivery Date].[Calendar Year].defaultmember, [Delivery Date].[Day Name].defaultmember, [Delivery Date].[Day of Month].defaultmember, [Delivery Date].[Day of Week].defaultmember, [Delivery Date].[Day of Year].defaultmember, [Delivery Date].[Fiscal].defaultmember, [Delivery Date].[Fiscal Quarter of Year].defaultmember, [Delivery Date].[Fiscal Semester of Year].defaultmember, [Delivery Date].[Fiscal Year].defaultmember, [Delivery Date].[Month of Year].defaultmember, [Delivery Date].[Week of Year].defaultmember, [Department].[Departments].defaultmember, [Destination Currency].[Destination Currency].defaultmember, [Destination Currency].[Destination Currency Code].defaultmember, [Employee].[Department Name].defaultmember, [Employee].[Email Address].defaultmember, [Employee].[Emergency Contact Name].defaultmember, [Employee].[Emergency Contact Phone].defaultmember, [Employee].[Employee Department].defaultmember, [Employee].[Employees].defaultmember, [Employee].[End Date].defaultmember, [Employee].[Gender].defaultmember, [Employee].[Hire Date].defaultmember, [Employee].[Hire Year].defaultmember, [Employee].[Marital Status].defaultmember, [Employee].[Pay Frequency].defaultmember, [Employee].[Phone].defaultmember, [Employee].[Salaried Flag].defaultmember, [Employee].[Sales Person Flag].defaultmember, [Employee].[Sick Leave Hours].defaultmember, [Employee].[Start Date].defaultmember, [Employee].[Status].defaultmember, [Employee].[Title].defaultmember, [Employee].[Vacation Hours].defaultmember, [Geography].[City].defaultmember, [Geography].[Country].defaultmember, [Geography].[Geography].defaultmember, [Geography].[Postal Code].defaultmember, [Geography].[State-Province].defaultmember, [Internet Sales Order Details].[Carrier Tracking Number].defaultmember, [Internet Sales Order Details].[Customer PO Number].defaultmember, [Internet Sales Order Details].[Internet Sales Orders].defaultmember, [Internet Sales Order Details].[Sales Order Line].defaultmember, [Internet Sales Order Details].[Sales Order Number].defaultmember, [Measures].defaultmember, [Organization].[Currency Code].defaultmember, [Organization].[Organizations].defaultmember, [Product].[Category].defaultmember, [Product].[Class].defaultmember, [Product].[Color].defaultmember, [Product].[Days to Manufacture].defaultmember, [Product].[Dealer Price].defaultmember, [Product].[End Date].defaultmember, [Product].[Large Photo].defaultmember, [Product].[List Price].defaultmember, [Product].[Manufacture Time].defaultmember, [Product].[Model Name].defaultmember, [Product].[Product].defaultmember, [Product].[Product Categories].defaultmember, [Product].[Product Key].defaultmember, [Product].[Product Line].defaultmember, [Product].[Product Model Categories].defaultmember, [Product].[Product Model Lines].defaultmember, [Product].[Reorder Point].defaultmember, [Product].[Safety Stock Level].defaultmember, [Product].[Size].defaultmember, [Product].[Size Range].defaultmember, [Product].[Standard Cost].defaultmember, [Product].[Start Date].defaultmember, [Product].[Status].defaultmember, [Product].[Stock Level].defaultmember, [Product].[Style].defaultmember, [Product].[Subcategory].defaultmember, [Product].[Weight].defaultmember, [Promotion].[Discount Percent].defaultmember, [Promotion].[End Date].defaultmember, [Promotion].[Max Quantity].defaultmember, [Promotion].[Min Quantity].defaultmember, [Promotion].[Promotion].defaultmember, [Promotion].[Promotion Category].defaultmember, [Promotion].[Promotion Type].defaultmember, [Promotion].[Promotions].defaultmember, [Promotion].[Start Date].defaultmember, [Reseller].[Address].defaultmember, [Reseller].[Annual Revenue].defaultmember, [Reseller].[Annual Sales].defaultmember, [Reseller].[Bank Name].defaultmember, [Reseller].[Business Type].defaultmember, [Reseller].[First Order Year].defaultmember, [Reseller].[Last Order Year].defaultmember, [Reseller].[Min Payment Amount].defaultmember, [Reseller].[Min Payment Type].defaultmember, [Reseller].[Number of Employees].defaultmember, [Reseller].[Order Frequency].defaultmember, [Reseller].[Order Month].defaultmember, [Reseller].[Phone].defaultmember, [Reseller].[Product Line].defaultmember, [Reseller].[Reseller].defaultmember, [Reseller].[Reseller Bank].defaultmember, [Reseller].[Reseller Order Frequency].defaultmember, [Reseller].[Reseller Order Month].defaultmember, [Reseller].[Reseller Type].defaultmember, [Reseller].[Year Opened].defaultmember, [Reseller Sales Order Details].[Carrier Tracking Number].defaultmember, [Reseller Sales Order Details].[Customer PO Number].defaultmember, [Reseller Sales Order Details].[Reseller Sales Orders].defaultmember, [Reseller Sales Order Details].[Sales Order Line].defaultmember, [Reseller Sales Order Details].[Sales Order Number].defaultmember, [Sales Channel].[Sales Channel].defaultmember, [Sales Reason].[Sales Reason].defaultmember, [Sales Reason].[Sales Reason Type].defaultmember, [Sales Reason].[Sales Reasons].defaultmember, [Sales Summary Order Details].[Carrier Tracking Number].defaultmember, [Sales Summary Order Details].[Customer PO Number].defaultmember, [Sales Summary Order Details].[Sales Order Line].defaultmember, [Sales Summary Order Details].[Sales Order Number].defaultmember, [Sales Summary Order Details].[Sales Orders].defaultmember, [Sales Territory].[Sales Territory].defaultmember, [Sales Territory].[Sales Territory Country].defaultmember, [Sales Territory].[Sales Territory Group].defaultmember, [Sales Territory].[Sales Territory Region].defaultmember, [Scenario].[Scenario].defaultmember, [Ship Date].[Calendar].defaultmember, [Ship Date].[Calendar Quarter of Year].defaultmember, [Ship Date].[Calendar Semester of Year].defaultmember, [Ship Date].[Calendar Year].defaultmember, [Ship Date].[Date].defaultmember, [Ship Date].[Day Name].defaultmember, [Ship Date].[Day of Month].defaultmember, [Ship Date].[Day of Week].defaultmember, [Ship Date].[Day of Year].defaultmember, [Ship Date].[Fiscal].defaultmember, [Ship Date].[Fiscal Quarter of Year].defaultmember, [Ship Date].[Fiscal Semester of Year].defaultmember, [Ship Date].[Fiscal Year].defaultmember, [Ship Date].[Month of Year].defaultmember, [Ship Date].[Week of Year].defaultmember, [Source Currency].[Source Currency].defaultmember, [Source Currency].[Source Currency Code].defaultmember )
GO
DROP MEMBER [Adventure Works].[Employee].[Employee].[All Employees].[BDA13F64-E5D5-418B-A4,A6,1F,C5,B8,44,2,54]
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Any insight into this problem would be much appreciated.
Jon
Do you think that we dont have nothing to do?!
...
|||Sorry, just trying to not leave out any details that would be helpful Thesecond (longer) MDX query is just to show how I managed to get correct results.
Jon
Calculations on members and aggregation
I'm designing a Profit And Loss report dimension that has the following attributes: Report Line, Cost Center and Account. Attribute relationships are defined between the attributes so that Report Line->Cost Center->Account.
A Report line is either A) A combination ofcost centers and accounts like:
Report Line A
- Cost Center 1
--Account 3000
--Account 3001
-Cost Center B
.. And so on
Or B) A calculation
I'm trying to handle calculations through mdx scripts by overwriting the value for those report lines that are calculations like:
scope (Report Line B);
this = Report Line A - Report Line C;
end scope
The problem is that the calculation report lines mess up the aggregation of the cube. I guess what I am really asking is wether its possible to turn off the members that are calculations in the aggregation of the cube. I realize i could use calculated measures for this but this doesnt fit my need for two reasons: Ease of use of the dimension and inability to drill down in reporting services on drillable members when calculated members are included.
Anyone have any ideas on this?
You can use the Freeze MDX statement to prevent changes to "Report Line B" from effecting the totals. To do this add simply apply Freeze to the all member of the hierarchy that contains Report Line B prior to updating Report Line B.
|||Thanks!
Works like a charm, and as a bonus it helped me understand freeze
calculations in a self-join (was "Query")
Hi all,
If I have a table like the following: (Table Name is A)
ID Date Time DeviceNumber Sum
1 1/12/2004 06:00:00 1 200
2 1/12/2004 08:00:00 1 600
3 1/12/2004 06:00:00 2 300
4 1/12/2004 08:00:00 2 800
If I write the following SQL statment:
"Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.ID=Six.ID
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");
How SQL interpret this statment. I make the same table innered joined be ID field.
By purpose is to calculate the Difference between the Sum of the different hourse of the two devices.
I mean,
'06:00:00 - 08:00:00' 1 400 --> (600-200)
'06:00:00 - 08:00:00' 2 500 --> (800-300)
Please help me ti under stand and solce the problem.Looks look you shoudl be joining on DeviceNumber and not on ID.|||Hi,
I don't understand right now, How SQL pull out data according to the where criteria.
This will help me to design the query.
Here I thought to make two copies of the same table, and substract the Sum of 06:00:00 o'clock from 08:00: o'clock.
Can you help me to correct the statment.
thanks...|||What I meant was:
Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.DeviceNumber=Six.DeviceNumber
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");
If I have a table like the following: (Table Name is A)
ID Date Time DeviceNumber Sum
1 1/12/2004 06:00:00 1 200
2 1/12/2004 08:00:00 1 600
3 1/12/2004 06:00:00 2 300
4 1/12/2004 08:00:00 2 800
If I write the following SQL statment:
"Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.ID=Six.ID
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");
How SQL interpret this statment. I make the same table innered joined be ID field.
By purpose is to calculate the Difference between the Sum of the different hourse of the two devices.
I mean,
'06:00:00 - 08:00:00' 1 400 --> (600-200)
'06:00:00 - 08:00:00' 2 500 --> (800-300)
Please help me ti under stand and solce the problem.Looks look you shoudl be joining on DeviceNumber and not on ID.|||Hi,
I don't understand right now, How SQL pull out data according to the where criteria.
This will help me to design the query.
Here I thought to make two copies of the same table, and substract the Sum of 06:00:00 o'clock from 08:00: o'clock.
Can you help me to correct the statment.
thanks...|||What I meant was:
Select Eight.Time,Eight.DeviceNumber,Eight.Sum - Six.Sum As Dif
From A as Eight Inner Join A as six On Eight.DeviceNumber=Six.DeviceNumber
Where (Eight.Time = "08:00:00") And (Six.Time="06:00:00");
Sunday, February 19, 2012
Calculationg times
Hi !
Following problem:
Table Times
USERID varchar
IN datetime
OUT datetime
THRA 18.05.05 18:01 18.05.05 22:00
I calculate the Hours, Minutes from IN to OUT with following function:
[dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT))
CREATE FUNCTION [dbo].GetHoursFromMinutes(@.pminutes INT)
RETURNS DECIMAL(18,2)
BEGIN
DECLARE @.hours INT,@.minutes INT
SET @.hours =@.pMinuten/60
SET @.minutes =@.pminutes%60
RETURN @.hours + (@.minutes *0.01)
END
This works. My challenge now. From 19:00 in the evening to 06:00 in the
morning they get an extra charge for nightwork. So I need an way to find out
if and how many minutes are in this timespan.
Nice would be a SQL formulat to calculate this.
Thanks for help.
ThomasCheck out this function I found on internet
CREATE FUNCTION dbo.presentDiffInHHMMSS
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
Usage:
DECLARE @.dt DATETIME
SET @.dt = '2001-04-30 17:04:32'
PRINT dbo.presentDiffInHHMMSS(@.dt, GETDATE())
DROP FUNCTION dbo.presentDiffInHHMMSS
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:5A6A2008-7CE9-4EFE-9E96-F12E64649C03@.microsoft.com...
> Hi !
> Following problem:
> Table Times
> USERID varchar
> IN datetime
> OUT datetime
> THRA 18.05.05 18:01 18.05.05 22:00
> I calculate the Hours, Minutes from IN to OUT with following function:
> [dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT))
> CREATE FUNCTION [dbo].GetHoursFromMinutes(@.pminutes INT)
> RETURNS DECIMAL(18,2)
> BEGIN
> DECLARE @.hours INT,@.minutes INT
> SET @.hours =@.pMinuten/60
> SET @.minutes =@.pminutes%60
> RETURN @.hours + (@.minutes *0.01)
> END
> This works. My challenge now. From 19:00 in the evening to 06:00 in the
> morning they get an extra charge for nightwork. So I need an way to find
out
> if and how many minutes are in this timespan.
> Nice would be a SQL formulat to calculate this.
> Thanks for help.
> Thomas|||Yes with this I can calculate the timespan between two times but I need the
minutes
of a timespan within another timespan (19:00 til 06:00)
Thomas
"Uri Dimant" wrote:
> Check out this function I found on internet
> CREATE FUNCTION dbo.presentDiffInHHMMSS
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
> Usage:
> DECLARE @.dt DATETIME
> SET @.dt = '2001-04-30 17:04:32'
> PRINT dbo.presentDiffInHHMMSS(@.dt, GETDATE())
> DROP FUNCTION dbo.presentDiffInHHMMSS
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:5A6A2008-7CE9-4EFE-9E96-F12E64649C03@.microsoft.com...
> out
>
>|||Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
Begin
Declare @.TotalHours Int
Declare @.TotalMinutes Int
Declare @.WholeDays Int
--Handle scenario where Start and EndDate are on the same day
If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
If DatePart(hh, @.StartDate) < 6 Begin
Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
Else If DatePart(hh, @.StartDate) < 19 Begin
Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Float))
As DateTime))
Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
Set @.TotalHours = @.TotalMinutes / 60
Set @.TotalMinutes = @.TotalMinutes % 60
End
If DatePart(hh, @.EndDate) > 19 Begin
Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
Else Begin
--Determine the number of whole days between the two date
Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.EndDate))
--If there are no days, then start at zero
If @.WholeDays < 0
Set @.WholeDays = 0
--We know that in each full day, there are 6 hours in the morning
--and 5 hours in the evening
Set @.TotalHours = @.WholeDays * 11
Set @.TotalMinutes = 0
--Determine the number of "magic" hours in the start date
If DatePart(hh, @.StartDate) < 19
Set @.TotalHours = @.TotalHours + 5
Else Begin
Set @.TotalHours = @.TotalHours + (24 - DatePart(hh, @.StartDate))
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
--determine the number of "magic" hours on the end date
If DatePart(hh, @.EndDate) >= 6
Set @.TotalHours = @.TotalHours + 6
Else Begin
Set @.TotalHours = @.TotalHours + DatePart(hh, @.EndDate)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
--Adjust the counts if @.TotalMinutes is greater than sixty
If @.TotalMinutes / 60 >= 1 Begin
Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
Set @.TotalMinutes = @.TotalMinutes % 60
End
Return @.TotalHours + (@.TotalMinutes * .01)
End
HTH
Thomas|||You could also refactor this solution a bit with some recursion:
Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
Begin
Declare @.Result Decimal(18,2)
Declare @.TotalHours Int
Declare @.TotalMinutes Int
Declare @.WholeDays Int
Set @.Result = 0
Set @.TotalHours = 0
Set @.TotalMinutes = 0
--Handle scenario where Start and EndDate are on the same day
If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
If DatePart(hh, @.StartDate) < 6 Begin
Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
Else If DatePart(hh, @.StartDate) < 19 Begin
Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Float))
As DateTime))
Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
Set @.TotalHours = @.TotalMinutes / 60
Set @.TotalMinutes = @.TotalMinutes % 60
End
If DatePart(hh, @.EndDate) > 19 Begin
Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
Else Begin
--Determine the number of whole days between the two date
Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.EndDate))
--If there are no days, then start at zero
If @.WholeDays < 0
Set @.WholeDays = 0
--We know that in each full day, there are 6 hours in the morning
--and 5 hours in the evening
Set @.TotalHours = @.WholeDays * 11
Set @.TotalMinutes = 0
--Get total hours from StartDate to the end of the StartDate's day
Set @.Result = GetCountSpecialHours(@.StartDate
, DateAdd(ss, -1, DateAdd(d, 1,
Cast(Floor(Cast(@.StartDate As Float))))))
--Get total hours from the beginning of the EndDate's day to the EndDate
Set @.Result = @.Result + GetCountSpecialHours(Cast(Floor(Cast(@.En
dDateAs
Float)))
, @.EndDate)
End
--Adjust the counts if @.TotalMinutes is greater than sixty
If @.TotalMinutes / 60 >= 1 Begin
Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
Set @.TotalMinutes = @.TotalMinutes % 60
End
Return @.Result + @.TotalHours + (@.TotalMinutes * .01)
End
HTH
Thomas|||I can't compile, few errors:
returns
as
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23K4Wgf$TFHA.548@.tk2msftngp13.phx.gbl...
> You could also refactor this solution a bit with some recursion:
> Create Function GetCountSpecialHours(
> @.StartDate DateTime
> , @.EndDate DateTime
> )
> Return Decimal(18,2)
> Begin
> Declare @.Result Decimal(18,2)
> Declare @.TotalHours Int
> Declare @.TotalMinutes Int
> Declare @.WholeDays Int
> Set @.Result = 0
> Set @.TotalHours = 0
> Set @.TotalMinutes = 0
> --Handle scenario where Start and EndDate are on the same day
> If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
> If DatePart(hh, @.StartDate) < 6 Begin
> Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> Else If DatePart(hh, @.StartDate) < 19 Begin
> Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As
> Float))
> As DateTime))
> Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
> Set @.TotalHours = @.TotalMinutes / 60
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> If DatePart(hh, @.EndDate) > 19 Begin
> Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> Else Begin
> --Determine the number of whole days between the two date
> Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1,
> @.EndDate))
> --If there are no days, then start at zero
> If @.WholeDays < 0
> Set @.WholeDays = 0
> --We know that in each full day, there are 6 hours in the morning
> --and 5 hours in the evening
> Set @.TotalHours = @.WholeDays * 11
> Set @.TotalMinutes = 0
> --Get total hours from StartDate to the end of the StartDate's day
> Set @.Result = GetCountSpecialHours(@.StartDate
> , DateAdd(ss, -1, DateAdd(d, 1,
> Cast(Floor(Cast(@.StartDate As Float))))))
> --Get total hours from the beginning of the EndDate's day to the
> EndDate
> Set @.Result = @.Result + GetCountSpecialHours(Cast(Floor(Cast(@.En
dDateAs
> Float)))
> , @.EndDate)
> End
> --Adjust the counts if @.TotalMinutes is greater than sixty
> If @.TotalMinutes / 60 >= 1 Begin
> Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> Return @.Result + @.TotalHours + (@.TotalMinutes * .01)
> End
>
> HTH
>
> Thomas
>
>|||Simple problem...This section:
Is missing the word "As" after it...it should read:
Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
As
Begin
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23RApBr$TFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I can't compile, few errors:
> returns
> as
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23K4Wgf$TFHA.548@.tk2msftngp13.phx.gbl...
>|||Thanks a lot. I thought that there is not a really 'easy' solution.
Its sometimes really hard for me to find a function for things which are
absolutly easy doing it by 'brain' and paper ;) !
Do you use this function in an application ?
The next step for me is now to integrate the holydays which are also 100%
(read them from a (calendartable) and to get the start and end points from
variables cause they change from business to business. Going to be a monster
function for such an 'easy' thing.
Thomas
"Thomas Coleman" wrote:
> Create Function GetCountSpecialHours(
> @.StartDate DateTime
> , @.EndDate DateTime
> )
> Return Decimal(18,2)
> Begin
> Declare @.TotalHours Int
> Declare @.TotalMinutes Int
> Declare @.WholeDays Int
> --Handle scenario where Start and EndDate are on the same day
> If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
> If DatePart(hh, @.StartDate) < 6 Begin
> Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> Else If DatePart(hh, @.StartDate) < 19 Begin
> Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Flo
at))
> As DateTime))
> Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
> Set @.TotalHours = @.TotalMinutes / 60
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> If DatePart(hh, @.EndDate) > 19 Begin
> Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> Else Begin
> --Determine the number of whole days between the two date
> Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.End
Date))
> --If there are no days, then start at zero
> If @.WholeDays < 0
> Set @.WholeDays = 0
> --We know that in each full day, there are 6 hours in the morning
> --and 5 hours in the evening
> Set @.TotalHours = @.WholeDays * 11
> Set @.TotalMinutes = 0
> --Determine the number of "magic" hours in the start date
> If DatePart(hh, @.StartDate) < 19
> Set @.TotalHours = @.TotalHours + 5
> Else Begin
> Set @.TotalHours = @.TotalHours + (24 - DatePart(hh, @.StartDate))
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> --determine the number of "magic" hours on the end date
> If DatePart(hh, @.EndDate) >= 6
> Set @.TotalHours = @.TotalHours + 6
> Else Begin
> Set @.TotalHours = @.TotalHours + DatePart(hh, @.EndDate)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> --Adjust the counts if @.TotalMinutes is greater than sixty
> If @.TotalMinutes / 60 >= 1 Begin
> Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> Return @.TotalHours + (@.TotalMinutes * .01)
> End
>
> HTH
>
> Thomas
>
>|||In regards to using that function, I just whipped up that function to solve
your
particular problem. Holidays should be a snap actually. One simple solution
is
to subtract the number of holidays between the two dates from @.WholeDays.
Thomas
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:B1A0FD1D-5AF8-4A61-98D9-47F6AF2D2B87@.microsoft.com...
> Thanks a lot. I thought that there is not a really 'easy' solution.
> Its sometimes really hard for me to find a function for things which are
> absolutly easy doing it by 'brain' and paper ;) !
> Do you use this function in an application ?
> The next step for me is now to integrate the holydays which are also 100%
> (read them from a (calendartable) and to get the start and end points from
> variables cause they change from business to business. Going to be a monst
er
> function for such an 'easy' thing.
> Thomas
Following problem:
Table Times
USERID varchar
IN datetime
OUT datetime
THRA 18.05.05 18:01 18.05.05 22:00
I calculate the Hours, Minutes from IN to OUT with following function:
[dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT))
CREATE FUNCTION [dbo].GetHoursFromMinutes(@.pminutes INT)
RETURNS DECIMAL(18,2)
BEGIN
DECLARE @.hours INT,@.minutes INT
SET @.hours =@.pMinuten/60
SET @.minutes =@.pminutes%60
RETURN @.hours + (@.minutes *0.01)
END
This works. My challenge now. From 19:00 in the evening to 06:00 in the
morning they get an extra charge for nightwork. So I need an way to find out
if and how many minutes are in this timespan.
Nice would be a SQL formulat to calculate this.
Thanks for help.
ThomasCheck out this function I found on internet
CREATE FUNCTION dbo.presentDiffInHHMMSS
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
Usage:
DECLARE @.dt DATETIME
SET @.dt = '2001-04-30 17:04:32'
PRINT dbo.presentDiffInHHMMSS(@.dt, GETDATE())
DROP FUNCTION dbo.presentDiffInHHMMSS
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:5A6A2008-7CE9-4EFE-9E96-F12E64649C03@.microsoft.com...
> Hi !
> Following problem:
> Table Times
> USERID varchar
> IN datetime
> OUT datetime
> THRA 18.05.05 18:01 18.05.05 22:00
> I calculate the Hours, Minutes from IN to OUT with following function:
> [dbo].GetHoursFromMinutes(DATEDIFF(n,IN,OUT))
> CREATE FUNCTION [dbo].GetHoursFromMinutes(@.pminutes INT)
> RETURNS DECIMAL(18,2)
> BEGIN
> DECLARE @.hours INT,@.minutes INT
> SET @.hours =@.pMinuten/60
> SET @.minutes =@.pminutes%60
> RETURN @.hours + (@.minutes *0.01)
> END
> This works. My challenge now. From 19:00 in the evening to 06:00 in the
> morning they get an extra charge for nightwork. So I need an way to find
out
> if and how many minutes are in this timespan.
> Nice would be a SQL formulat to calculate this.
> Thanks for help.
> Thomas|||Yes with this I can calculate the timespan between two times but I need the
minutes
of a timespan within another timespan (19:00 til 06:00)
Thomas
"Uri Dimant" wrote:
> Check out this function I found on internet
> CREATE FUNCTION dbo.presentDiffInHHMMSS
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
> Usage:
> DECLARE @.dt DATETIME
> SET @.dt = '2001-04-30 17:04:32'
> PRINT dbo.presentDiffInHHMMSS(@.dt, GETDATE())
> DROP FUNCTION dbo.presentDiffInHHMMSS
> "Thomas" <Thomas@.discussions.microsoft.com> wrote in message
> news:5A6A2008-7CE9-4EFE-9E96-F12E64649C03@.microsoft.com...
> out
>
>|||Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
Begin
Declare @.TotalHours Int
Declare @.TotalMinutes Int
Declare @.WholeDays Int
--Handle scenario where Start and EndDate are on the same day
If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
If DatePart(hh, @.StartDate) < 6 Begin
Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
Else If DatePart(hh, @.StartDate) < 19 Begin
Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Float))
As DateTime))
Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
Set @.TotalHours = @.TotalMinutes / 60
Set @.TotalMinutes = @.TotalMinutes % 60
End
If DatePart(hh, @.EndDate) > 19 Begin
Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
Else Begin
--Determine the number of whole days between the two date
Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.EndDate))
--If there are no days, then start at zero
If @.WholeDays < 0
Set @.WholeDays = 0
--We know that in each full day, there are 6 hours in the morning
--and 5 hours in the evening
Set @.TotalHours = @.WholeDays * 11
Set @.TotalMinutes = 0
--Determine the number of "magic" hours in the start date
If DatePart(hh, @.StartDate) < 19
Set @.TotalHours = @.TotalHours + 5
Else Begin
Set @.TotalHours = @.TotalHours + (24 - DatePart(hh, @.StartDate))
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
--determine the number of "magic" hours on the end date
If DatePart(hh, @.EndDate) >= 6
Set @.TotalHours = @.TotalHours + 6
Else Begin
Set @.TotalHours = @.TotalHours + DatePart(hh, @.EndDate)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
--Adjust the counts if @.TotalMinutes is greater than sixty
If @.TotalMinutes / 60 >= 1 Begin
Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
Set @.TotalMinutes = @.TotalMinutes % 60
End
Return @.TotalHours + (@.TotalMinutes * .01)
End
HTH
Thomas|||You could also refactor this solution a bit with some recursion:
Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
Begin
Declare @.Result Decimal(18,2)
Declare @.TotalHours Int
Declare @.TotalMinutes Int
Declare @.WholeDays Int
Set @.Result = 0
Set @.TotalHours = 0
Set @.TotalMinutes = 0
--Handle scenario where Start and EndDate are on the same day
If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
If DatePart(hh, @.StartDate) < 6 Begin
Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
Set @.TotalMinutes = DatePart(n, @.StartDate)
End
Else If DatePart(hh, @.StartDate) < 19 Begin
Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Float))
As DateTime))
Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
Set @.TotalHours = @.TotalMinutes / 60
Set @.TotalMinutes = @.TotalMinutes % 60
End
If DatePart(hh, @.EndDate) > 19 Begin
Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
End
End
Else Begin
--Determine the number of whole days between the two date
Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.EndDate))
--If there are no days, then start at zero
If @.WholeDays < 0
Set @.WholeDays = 0
--We know that in each full day, there are 6 hours in the morning
--and 5 hours in the evening
Set @.TotalHours = @.WholeDays * 11
Set @.TotalMinutes = 0
--Get total hours from StartDate to the end of the StartDate's day
Set @.Result = GetCountSpecialHours(@.StartDate
, DateAdd(ss, -1, DateAdd(d, 1,
Cast(Floor(Cast(@.StartDate As Float))))))
--Get total hours from the beginning of the EndDate's day to the EndDate
Set @.Result = @.Result + GetCountSpecialHours(Cast(Floor(Cast(@.En
dDateAs
Float)))
, @.EndDate)
End
--Adjust the counts if @.TotalMinutes is greater than sixty
If @.TotalMinutes / 60 >= 1 Begin
Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
Set @.TotalMinutes = @.TotalMinutes % 60
End
Return @.Result + @.TotalHours + (@.TotalMinutes * .01)
End
HTH
Thomas|||I can't compile, few errors:
returns
as
"Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
news:%23K4Wgf$TFHA.548@.tk2msftngp13.phx.gbl...
> You could also refactor this solution a bit with some recursion:
> Create Function GetCountSpecialHours(
> @.StartDate DateTime
> , @.EndDate DateTime
> )
> Return Decimal(18,2)
> Begin
> Declare @.Result Decimal(18,2)
> Declare @.TotalHours Int
> Declare @.TotalMinutes Int
> Declare @.WholeDays Int
> Set @.Result = 0
> Set @.TotalHours = 0
> Set @.TotalMinutes = 0
> --Handle scenario where Start and EndDate are on the same day
> If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
> If DatePart(hh, @.StartDate) < 6 Begin
> Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> Else If DatePart(hh, @.StartDate) < 19 Begin
> Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As
> Float))
> As DateTime))
> Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
> Set @.TotalHours = @.TotalMinutes / 60
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> If DatePart(hh, @.EndDate) > 19 Begin
> Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> Else Begin
> --Determine the number of whole days between the two date
> Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1,
> @.EndDate))
> --If there are no days, then start at zero
> If @.WholeDays < 0
> Set @.WholeDays = 0
> --We know that in each full day, there are 6 hours in the morning
> --and 5 hours in the evening
> Set @.TotalHours = @.WholeDays * 11
> Set @.TotalMinutes = 0
> --Get total hours from StartDate to the end of the StartDate's day
> Set @.Result = GetCountSpecialHours(@.StartDate
> , DateAdd(ss, -1, DateAdd(d, 1,
> Cast(Floor(Cast(@.StartDate As Float))))))
> --Get total hours from the beginning of the EndDate's day to the
> EndDate
> Set @.Result = @.Result + GetCountSpecialHours(Cast(Floor(Cast(@.En
dDateAs
> Float)))
> , @.EndDate)
> End
> --Adjust the counts if @.TotalMinutes is greater than sixty
> If @.TotalMinutes / 60 >= 1 Begin
> Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> Return @.Result + @.TotalHours + (@.TotalMinutes * .01)
> End
>
> HTH
>
> Thomas
>
>|||Simple problem...This section:
Is missing the word "As" after it...it should read:
Create Function GetCountSpecialHours(
@.StartDate DateTime
, @.EndDate DateTime
)
Return Decimal(18,2)
As
Begin
Thomas
"js" <js@.someone@.hotmail.com> wrote in message
news:%23RApBr$TFHA.2096@.TK2MSFTNGP14.phx.gbl...
>I can't compile, few errors:
> returns
> as
> "Thomas Coleman" <replyingroup@.anywhere.com> wrote in message
> news:%23K4Wgf$TFHA.548@.tk2msftngp13.phx.gbl...
>|||Thanks a lot. I thought that there is not a really 'easy' solution.
Its sometimes really hard for me to find a function for things which are
absolutly easy doing it by 'brain' and paper ;) !
Do you use this function in an application ?
The next step for me is now to integrate the holydays which are also 100%
(read them from a (calendartable) and to get the start and end points from
variables cause they change from business to business. Going to be a monster
function for such an 'easy' thing.
Thomas
"Thomas Coleman" wrote:
> Create Function GetCountSpecialHours(
> @.StartDate DateTime
> , @.EndDate DateTime
> )
> Return Decimal(18,2)
> Begin
> Declare @.TotalHours Int
> Declare @.TotalMinutes Int
> Declare @.WholeDays Int
> --Handle scenario where Start and EndDate are on the same day
> If DateDiff(d, @.StartDate, @.EndDate) < 1 Begin
> If DatePart(hh, @.StartDate) < 6 Begin
> Set @.TotalHours = 6 - DatePart(hh, @.StartDate)
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> Else If DatePart(hh, @.StartDate) < 19 Begin
> Set @.StartDate = DateAdd(hh, 19, Cast(Floor(Cast(@.StartDate As Flo
at))
> As DateTime))
> Set @.TotalMinutes = DateDiff(n, @.StartDate, @.EndDate)
> Set @.TotalHours = @.TotalMinutes / 60
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> If DatePart(hh, @.EndDate) > 19 Begin
> Set @.TotalHours = @.TotalHours + (DatePart(hh, @.EndDate) - 19)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> Else Begin
> --Determine the number of whole days between the two date
> Set @.WholeDays = DateDiff(d,DateAdd(d,1,@.StartDate),DateA
dd(d,-1, @.End
Date))
> --If there are no days, then start at zero
> If @.WholeDays < 0
> Set @.WholeDays = 0
> --We know that in each full day, there are 6 hours in the morning
> --and 5 hours in the evening
> Set @.TotalHours = @.WholeDays * 11
> Set @.TotalMinutes = 0
> --Determine the number of "magic" hours in the start date
> If DatePart(hh, @.StartDate) < 19
> Set @.TotalHours = @.TotalHours + 5
> Else Begin
> Set @.TotalHours = @.TotalHours + (24 - DatePart(hh, @.StartDate))
> Set @.TotalMinutes = DatePart(n, @.StartDate)
> End
> --determine the number of "magic" hours on the end date
> If DatePart(hh, @.EndDate) >= 6
> Set @.TotalHours = @.TotalHours + 6
> Else Begin
> Set @.TotalHours = @.TotalHours + DatePart(hh, @.EndDate)
> Set @.TotalMinutes = @.TotalMinutes + DatePart(n, @.EndDate)
> End
> End
> --Adjust the counts if @.TotalMinutes is greater than sixty
> If @.TotalMinutes / 60 >= 1 Begin
> Set @.TotalHours = @.TotalHours + (@.TotalMinutes / 60)
> Set @.TotalMinutes = @.TotalMinutes % 60
> End
> Return @.TotalHours + (@.TotalMinutes * .01)
> End
>
> HTH
>
> Thomas
>
>|||In regards to using that function, I just whipped up that function to solve
your
particular problem. Holidays should be a snap actually. One simple solution
is
to subtract the number of holidays between the two dates from @.WholeDays.
Thomas
"Thomas" <Thomas@.discussions.microsoft.com> wrote in message
news:B1A0FD1D-5AF8-4A61-98D9-47F6AF2D2B87@.microsoft.com...
> Thanks a lot. I thought that there is not a really 'easy' solution.
> Its sometimes really hard for me to find a function for things which are
> absolutly easy doing it by 'brain' and paper ;) !
> Do you use this function in an application ?
> The next step for me is now to integrate the holydays which are also 100%
> (read them from a (calendartable) and to get the start and end points from
> variables cause they change from business to business. Going to be a monst
er
> function for such an 'easy' thing.
> Thomas
calculation query
Hi all,
I need your help of building the following query.
I have the table "CountersTbl".
The table's fields are.
Date\Time Counter1 Counter2
The tables holds counters in different dates ant time.e.g:
19/11/04 06:00 am 10 20
19/11/04 15:00 pm 15 25
19/11/04 11:00 pm 35 90
...
In the above table we have the counters in three shifts in one day. Each day I hae the same shifts readings.
My task is:
I want to build a query that calculate the difference between the shifts counters in a givven day.
So, the output of the query of the 19/11/04 day is:
From 06:00 am - 15:00 pm 5 5
From 15:00 pm - 11:00 pm 20 65
I you please help me to build the code of this query.
Best regards...select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
, three.Counter1
-six.Counter1 as Counter1Diff
, three.Counter2
-six.Counter2 as Counter2Diff
from CountersTbl as three
inner
join CountersTbl as six
on date(three.datetimecol)
= date(six.datetimecol)
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
union all
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
, eleven.Counter1
-three.Counter1 as Counter1Diff
, eleven.Counter2
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
inner
join CountersTbl as three
on date(eleven.datetimecol)
= date(three.datetimecol)
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
order
by ShiftDate
, Shifthere datetimecol is the name of your "Date\Time" column
also, please note, date and time represent whatever functions are available in your particular database system for extracting the date only and time only portions of the datetime values
i was going to write them using the standard sql EXTRACT function but the "standard sql" book which i own is pretty crappy and does not give enough decent examples for me to know how to extract dates and times
and anyway, most common database systems don't support EXTRACT, they have their own proprietary date functions
I need your help of building the following query.
I have the table "CountersTbl".
The table's fields are.
Date\Time Counter1 Counter2
The tables holds counters in different dates ant time.e.g:
19/11/04 06:00 am 10 20
19/11/04 15:00 pm 15 25
19/11/04 11:00 pm 35 90
...
In the above table we have the counters in three shifts in one day. Each day I hae the same shifts readings.
My task is:
I want to build a query that calculate the difference between the shifts counters in a givven day.
So, the output of the query of the 19/11/04 day is:
From 06:00 am - 15:00 pm 5 5
From 15:00 pm - 11:00 pm 20 65
I you please help me to build the code of this query.
Best regards...select date(three.datetimecol) as ShiftDate
, 'From 06:00 - 15:00' as Shift
, three.Counter1
-six.Counter1 as Counter1Diff
, three.Counter2
-six.Counter2 as Counter2Diff
from CountersTbl as three
inner
join CountersTbl as six
on date(three.datetimecol)
= date(six.datetimecol)
where time(three.datetimecol) = '15:00'
and time(six.datetimecol) = '06:00'
union all
select date(eleven.datetimecol) as ShiftDate
, 'From 15:00 - 23:00' as Shift
, eleven.Counter1
-three.Counter1 as Counter1Diff
, eleven.Counter2
-three.Counter2 as Counter2Diff
from CountersTbl as eleven
inner
join CountersTbl as three
on date(eleven.datetimecol)
= date(three.datetimecol)
where time(eleven.datetimecol) = '23:00'
and date(three.datetimecol) = '15:00'
order
by ShiftDate
, Shifthere datetimecol is the name of your "Date\Time" column
also, please note, date and time represent whatever functions are available in your particular database system for extracting the date only and time only portions of the datetime values
i was going to write them using the standard sql EXTRACT function but the "standard sql" book which i own is pretty crappy and does not give enough decent examples for me to know how to extract dates and times
and anyway, most common database systems don't support EXTRACT, they have their own proprietary date functions
calculation problem
In the following calculation I am receiving my output as 000000.9000. I need it to look like 00000000.90. I cant seem to loose the 2 zeros at the end. Can anyone help me with this? Keep in mind that my output has to be varchar.
right('00000000000' + cast((st.rate_dlr * .01) * 10 as varchar),11)If you only need positive values, you can use:SELECT Replace(Str(st.rate_dlr * 0.01, 11, 2), ' ', '0')Allowing negative numbers gets a bit more complicated, but until you decide how you want them formatted I could only guess anyway.
-PatP|||Thanks for your reply...I finally figured it out. Here is basically what I did.........
select Cost_cast = right('000000.00' + convert(varchar,convert(decimal(6,2),round(4444.22 22,2))),9)|||Before you get too frisky, I'd checkselect Cost_cast = right('000000.00'
+ convert(varchar,convert(decimal(6,2)
, round(44.2222,2))),9)I think you might prefer my solution!
-PatP
right('00000000000' + cast((st.rate_dlr * .01) * 10 as varchar),11)If you only need positive values, you can use:SELECT Replace(Str(st.rate_dlr * 0.01, 11, 2), ' ', '0')Allowing negative numbers gets a bit more complicated, but until you decide how you want them formatted I could only guess anyway.
-PatP|||Thanks for your reply...I finally figured it out. Here is basically what I did.........
select Cost_cast = right('000000.00' + convert(varchar,convert(decimal(6,2),round(4444.22 22,2))),9)|||Before you get too frisky, I'd checkselect Cost_cast = right('000000.00'
+ convert(varchar,convert(decimal(6,2)
, round(44.2222,2))),9)I think you might prefer my solution!
-PatP
Calculation in Matrix
My Matrix has the following structure
Item Jan Feb
--
AAA 200 40
How would I add another column to show the diff. between the two months
Item Jan Feb Diff
--
AAA 200 40 160
Thanks,
JRI don't think you can accomplish this currently with a matrix. As you know,
with a table it is easy but that fixes the number of displayed columns unlike
a matrix. Note that your question is similar to the one posted yesterday
about adding a partial subtotal to a matrix. There were no answers to that
question.
"John" wrote:
> My Matrix has the following structure
> Item Jan Feb
> --
> AAA 200 40
>
> How would I add another column to show the diff. between the two months
> Item Jan Feb Diff
> --
> AAA 200 40 160
>
> Thanks,
> JR|||Substraction is negative addition. Display Feb as 20 but treat it as -20 and
calculate the total.
CD
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:714B0E8B-B6C4-488B-9DE5-9DA23AD83E39@.microsoft.com...
>I don't think you can accomplish this currently with a matrix. As you know,
> with a table it is easy but that fixes the number of displayed columns
> unlike
> a matrix. Note that your question is similar to the one posted yesterday
> about adding a partial subtotal to a matrix. There were no answers to that
> question.
> "John" wrote:
>> My Matrix has the following structure
>> Item Jan Feb
>> --
>> AAA 200 40
>>
>> How would I add another column to show the diff. between the two months
>> Item Jan Feb Diff
>> --
>> AAA 200 40 160
>>
>> Thanks,
>> JR
Item Jan Feb
--
AAA 200 40
How would I add another column to show the diff. between the two months
Item Jan Feb Diff
--
AAA 200 40 160
Thanks,
JRI don't think you can accomplish this currently with a matrix. As you know,
with a table it is easy but that fixes the number of displayed columns unlike
a matrix. Note that your question is similar to the one posted yesterday
about adding a partial subtotal to a matrix. There were no answers to that
question.
"John" wrote:
> My Matrix has the following structure
> Item Jan Feb
> --
> AAA 200 40
>
> How would I add another column to show the diff. between the two months
> Item Jan Feb Diff
> --
> AAA 200 40 160
>
> Thanks,
> JR|||Substraction is negative addition. Display Feb as 20 but treat it as -20 and
calculate the total.
CD
"B. Mark McKinney" <BMarkMcKinney@.discussions.microsoft.com> wrote in
message news:714B0E8B-B6C4-488B-9DE5-9DA23AD83E39@.microsoft.com...
>I don't think you can accomplish this currently with a matrix. As you know,
> with a table it is easy but that fixes the number of displayed columns
> unlike
> a matrix. Note that your question is similar to the one posted yesterday
> about adding a partial subtotal to a matrix. There were no answers to that
> question.
> "John" wrote:
>> My Matrix has the following structure
>> Item Jan Feb
>> --
>> AAA 200 40
>>
>> How would I add another column to show the diff. between the two months
>> Item Jan Feb Diff
>> --
>> AAA 200 40 160
>>
>> Thanks,
>> JR
Tuesday, February 14, 2012
calculating percentage of group1 vs group2
Hi,
I've got a matrix report set up that gives the following:
column Group1 Group2
Row1 x y
I need to calculate x/y and cannot get the scope straight.
Any help would be much appreciated.im having the same problem, did you find a way of solving it?
thank you very much
Eli
"devinjc" wrote:
> Hi,
> I've got a matrix report set up that gives the following:
> column Group1 Group2
> Row1 x y
> I need to calculate x/y and cannot get the scope straight.
> Any help would be much appreciated.
>|||Did you ever discover a solution? I'm trying to do the same but with no
success.
"devinjc" wrote:
> Hi,
> I've got a matrix report set up that gives the following:
> column Group1 Group2
> Row1 x y
> I need to calculate x/y and cannot get the scope straight.
> Any help would be much appreciated.
>|||me too
"devinjc" wrote:
> Hi,
> I've got a matrix report set up that gives the following:
> column Group1 Group2
> Row1 x y
> I need to calculate x/y and cannot get the scope straight.
> Any help would be much appreciated.
>|||I solved this problem by calculating the percentage in a different query and
the adding an extra matrix next to the existing one.
To deal with different levels off aggregation, I dident calculate the
percantge but the difference and the base value.
The expression for the percentage then becomes:
=iif( sum(Fields!Base.Value,"Compare") = 0, 0,
sum(Fields!Difference.Value,"Compare") /sum(Fields!Base.Value,"Compare") *100)
It's a bit heavy on the use of queries but it works and I've been looking
everywhere for a different sollution.
"Tango" wrote:
> me too
> "devinjc" wrote:
> > Hi,
> >
> > I've got a matrix report set up that gives the following:
> >
> > column Group1 Group2
> >
> > Row1 x y
> >
> > I need to calculate x/y and cannot get the scope straight.
> >
> > Any help would be much appreciated.
> >
I've got a matrix report set up that gives the following:
column Group1 Group2
Row1 x y
I need to calculate x/y and cannot get the scope straight.
Any help would be much appreciated.im having the same problem, did you find a way of solving it?
thank you very much
Eli
"devinjc" wrote:
> Hi,
> I've got a matrix report set up that gives the following:
> column Group1 Group2
> Row1 x y
> I need to calculate x/y and cannot get the scope straight.
> Any help would be much appreciated.
>|||Did you ever discover a solution? I'm trying to do the same but with no
success.
"devinjc" wrote:
> Hi,
> I've got a matrix report set up that gives the following:
> column Group1 Group2
> Row1 x y
> I need to calculate x/y and cannot get the scope straight.
> Any help would be much appreciated.
>|||me too
"devinjc" wrote:
> Hi,
> I've got a matrix report set up that gives the following:
> column Group1 Group2
> Row1 x y
> I need to calculate x/y and cannot get the scope straight.
> Any help would be much appreciated.
>|||I solved this problem by calculating the percentage in a different query and
the adding an extra matrix next to the existing one.
To deal with different levels off aggregation, I dident calculate the
percantge but the difference and the base value.
The expression for the percentage then becomes:
=iif( sum(Fields!Base.Value,"Compare") = 0, 0,
sum(Fields!Difference.Value,"Compare") /sum(Fields!Base.Value,"Compare") *100)
It's a bit heavy on the use of queries but it works and I've been looking
everywhere for a different sollution.
"Tango" wrote:
> me too
> "devinjc" wrote:
> > Hi,
> >
> > I've got a matrix report set up that gives the following:
> >
> > column Group1 Group2
> >
> > Row1 x y
> >
> > I need to calculate x/y and cannot get the scope straight.
> >
> > Any help would be much appreciated.
> >
Subscribe to:
Posts (Atom)