How Do I Add Time with integer value in MSSQL ?

Hi, Expert

How Do I Add Time with integer value in MSSQL ??

For Example: 07:00:50 + 20 = 07:01:10

Note: 20 is in second

Thanks in advance

Check out the DATEADD function in BOL (documentation).|||

Hi,

You can play with DATEADD function for your question, for example:

SELECT dateadd(s, 20, getdate())

this one will add 20 seconds to now.

You can look up this function from Books Online to find more information. By the way, in SQL Server there is datatime data type, but not time or date only data type.

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttid
Look at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one select
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttid|||Oh, I see. I have to use Having and it's after the Group. It works great.
Thank you very much and have a great day!
"Geoff N. Hiten" wrote:
> Look at the "Having" clause of the select statement. "Having" works on
> "Group By" much like the 'Where" works on "From". In your case, you would
> add something like:
> HAVING Sum(Amount) > 0
> after your Group By section.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> > Hi
> > I'm builing this string dynamically in C# program. I need to add one more
> > filtering condition of "Sum(Amount) >0" to the Select statement. I added
> > it
> > but I got an error message. Can someone help on how to do this in one
> > select
> > statment?
> >
> > Thanks, Alpha
> >
> > select exttid,sum(amount) as FeeBalance from tblLedger
> > where exttid in
> > (select distinct(exttid) from tblBilling b
> > where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> > and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> > and void = 0
> > group by exttid
>
>

How do I add condition for a sum() value in the select statement?

Hi
I'm builing this string dynamically in C# program. I need to add one more
filtering condition of "Sum(Amount) >0" to the Select statement. I added it
but I got an error message. Can someone help on how to do this in one selec
t
statment?
Thanks, Alpha
select exttid,sum(amount) as FeeBalance from tblLedger
where exttid in
(select distinct(exttid) from tblBilling b
where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
and void = 0
group by exttidLook at the "Having" clause of the select statement. "Having" works on
"Group By" much like the 'Where" works on "From". In your case, you would
add something like:
HAVING Sum(Amount) > 0
after your Group By section.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:F6DA3969-24F9-4895-B7CB-93ECC06EB852@.microsoft.com...
> Hi
> I'm builing this string dynamically in C# program. I need to add one more
> filtering condition of "Sum(Amount) >0" to the Select statement. I added
> it
> but I got an error message. Can someone help on how to do this in one
> select
> statment?
> Thanks, Alpha
> select exttid,sum(amount) as FeeBalance from tblLedger
> where exttid in
> (select distinct(exttid) from tblBilling b
> where datediff(day,cast('5/17/2005' as datetime),b.formdate) >= 0
> and datediff(day,cast('8/17/2005' as datetime),b.formdate) <= 0)
> and void = 0
> group by exttidsql

How do I access the value of a stored proc return param in c# using executeNonQuery?

I've got a stored proc to insert a record and return the id of the record inserted in an output param.
How do I access this value in my code after the proc is executed?

param = comm.CreateParameter();
param.ParameterName ="@.MemberID";
param.Direction =ParameterDirection.Output;
param.DbType =DbType.Int32;
comm.Parameters.Add(param);

try
{
rowsAffected =GenericDataAccess.ExecuteNonQuery(comm);
}
catch
{
rowsAffected = -1;
}

you should simply be able to access the parameters Value property.

param.Value

How could I obtain the domain name from SQL?

Dear fellows,
I would need from T-SQL job or through any friendly-user function (VB
front-end app) the aforementioned value.
With EXEC xp_cmdshell 'IPCONFIG /ALL' I get values such as HOST NAME or
IP Adress. Problem is the following: if oneself launch IPCONFIG /ALL from a
DOS session it is obtained DNS suffixes list with values hoped: domain,
Active Directory and so on. But launched from Query Analyzer due to it
returns more than a row appear as NULL value.
Does anyone know how/where it is available?
Best regards,> But launched from Query Analyzer due to it
> returns more than a row appear as NULL value.
Is this the only problem?
CREATE TABLE #foo(cmd VARCHAR(1024))
INSERT #foo EXEC master..xp_cmdshell 'IPCONFIG /ALL'
SELECT * FROM #foo WHERE cmd IS NOT NULL
DROP TABLE #foo|||Dear Aaron,
Is this the only problem?
No!
Your solution not works because of appers the line empty.
Thanks a lot for your support,
"Aaron Bertrand [SQL Server MVP]" wrote:

> Is this the only problem?
>
> CREATE TABLE #foo(cmd VARCHAR(1024))
> INSERT #foo EXEC master..xp_cmdshell 'IPCONFIG /ALL'
> SELECT * FROM #foo WHERE cmd IS NOT NULL
> DROP TABLE #foo
>
>|||> Your solution not works because of appers the line empty.
Okay, so is this really that difficult?
SELECT * FROM #foo WHERE cmd > ''

How Change field value triggered by date?

In a table i had field called Status and it is of type integer and another two field From and To ... they are of type date .....
what i want to do is to change the value of status field when the current date become equal to the From field and also make another change when the current date become equal to the To field ...

So How can i do This on SQL server 2005?

You need to run a query to update the rows, something like this

UPDATE mytable
SET Status = CASE
WHEN datediff(d, From, getdate()) = 0 THEN 1
WHEN datediff(d, To, getdate()) = 0 THEN 2
END

You could create a calculated column based on the same expression if you want status to always be up to date without running the query first. Otherwise schedule a job to run that query every night so that the status column is always correct.

|||

Thank You for your reply and it is helpful,

But what if the From and To fields in a child table to the original table that have the field status?

e.x. Master Table have the following fields:

PersonID, Name, Status

Details Table has the following fields:

ID,PersonID (as foreign key to the master table), From, To

How the query will look like?!

|||

UPDATE mastertable
SET Status = CASE
WHEN datediff(d, detailtable.From, getdate()) = 0 THEN 1
WHEN datediff(d, detailtable.To, getdate()) = 0 THEN 2
END
FROM mastertable
INNER JOIN detailtable ON mastertable.PersonID = detailtable.PersonID

How can we RETAIN the Tooltip value shown on report into in Excel Sheet

Is there any way in which i can carry or retain the tooltip value which is shown on my report into Excel sheet ...

i mean when i export the report into Excel sheet, tooltip value is gone?? how can i retain the tooltip value when i export into excel ??

is it possible if so ...any hints? thanks a lot in advance

any thoughts on this...at least can anyone suggest me how to control the export feature of report?? i mean can i write custom rendering events etc?

i really appreciate...if some reply to this thread

How can we RETAIN the Tooltip value shown on report into in Excel

Is there any way in which i can carry or retain the tooltip value which is
shown on my report into Excel sheet ...
i mean when i export the report into Excel sheet, tooltip value is gone'
how can i retain the tooltip value when i export into excel '
thanks a lot in advanceOn May 9, 1:23 pm, Dia <D...@.discussions.microsoft.com> wrote:
> Is there any way in which i can carry or retain the tooltip value which is
> shown on my report into Excel sheet ...
> i mean when i export the report into Excel sheet, tooltip value is gone'
> how can i retain the tooltip value when i export into excel '
> thanks a lot in advance
As far as I know, there is not a way to do this. I believe the only
export format that supports tooltips is Web Archive. Sorry that I
could not be of further assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

How can we replace any null cells with a particular value instead?

Hi, all experts here,

Thank you very much for your kind attention.

I am wondering if there is any way to replace all null cells when browsing dimensions dynamically? As when we browse cube data with whatever dimensions hierarchies etc. on rows and columns we always get some null cells which have not intersections between those selected dimension hierarchies on columns and rows at all as we never be able to know where null cells are. Therefore in order to make things better (looking better) like in client tool ProClarity, I want to find a way to replace those null cells with a particular value to make the reports look better?

Is it possible to achieve this in SQL Server 2005 Analysis Services and which can be inherited in its clients as well (e.g. ProClarity)? I am looking forward to hearing from you shortly and thanks again for your help.

With best regards,

Yours sincerely,

Hello Helen! In ProClarity you have two buttons in the dimension tool that will remove empty rows and columns in a table.

Be aware that the NON EMPTY buttons will only remove rows and columns where all the cells a null.

If you enter other values, with the MDX IIF-statement in a calculated member, or format-options(in the Calculations tab in the cube editor) you can hurt query performance.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thank you very much for that.

The question to me is, some null cells on some rows where not the whole rows are empty. E.g some cells of those rows are with values, some are not. Therefore it does not look nice. So I want to replace them. But then as you kindly suggested replacing them with other values you have to add calculated members which to some extent hurt performance query. Therefore I think it is better to leave them as they are to the end users.

Thank you very much again for your kind help.

With best regards,

Yours sincerely,

How can we get the round value of a number by MDX?

Hi, all,

How can I get the round value of a number (e.g 4.95, I want to get its closest value of 5), how can I achieve this by MDX?

Thanks a lot and I am looking forward to hearing from you.

With best regards,

Yours sincerely,

go to menu report -> report properties and select code. then paste following code there.

Public Function Round(d as double) AS Double
Dim roundValue AS Double
roundValue =System.Math.Round(d)

If (System.Math.Abs(d - roundValue) - 0.5>0) Then
If (roundValue Mod 2) = 0 Then
roundValue = roundValue + 1
End If
End If

RETURN roundValue
End Function

then in ur designer

Code.Round(Field!Amount.value)

think this wil b ok
|||

Hi, thanks for your help. But i dont understand as what i am trying to do is for the KPI in cube designer, i want to get a round value for the KPI.

Thanks.

With best regards,

Yours sincerely,

|||

Helen,

In MDX you can achieve Rounding by using VBA!Round Function as following

WITH MEMBER [Measures].[My Rounded Measure]

AS VBA!ROUND([Measures].[My Measure],0)

SELECT

{

[Measures].[My Rounded Measure]

} ON 0,

NON EMPTY{

[Customer].[Customer].[Customer]

} ON 1

FROM [My Cube]

Bhudev

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can we format the percentage value format?

Hi, experts,

Thanks for your kind attention.

I wanna know how can we format percentage values with 2 decimals (e.g. 98.88%)?

I am looking forward to hearing from you shortly and thanks a lot in advance.

With kindest regards,

Yours sincerely,

If it is a measure try the format property in the Properties tab. Or do you need something else?

Cheers

|||

Hi,

Thank you for your reply.

Yes, I cant find the format for defining the decimal places for percentage values in the format property tab?

I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Actually there isn't one :-) . The building option for percentage always uses 2 decimal places. Else I think that you would have to specify your own format if you need percentage with more than 2 decimal places.

cheers

|||

Hi,

Thanks for that.

Kind regards,

|||

Hello Helen! Try "###.##%" or "000.00%" for the calculated member!

You will figure out how they work!

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your advices.

With kindest regards,

Yours sincerely,

How can remove a row from my report if the results is empty or a n

How can remove a row from my report if the results is empty or a null?
If i have a null value , then i get a white space row in report - this is
not looking good !
Can somebody help me, please !?
Thank you!victoras wrote:
> How can remove a row from my report if the results is empty or a null?
> If i have a null value , then i get a white space row in report - this is
> not looking good !
> Can somebody help me, please !?
You need to write your SQL query to exclude that row, something like:
SELECT col1, col2 ... FROM mytable WHERE col1 IS NOT NULL
Luke|||or just use an iif statement on the row which toggles the visibility.
click the row, go to properties and visibility. choose expression
type
=iif(fields!afield.value is system.dbnull.value or fields!afield.value = "",
true, false)
basically says if your field is null or an empty string then do not show
else do show. True means don't show and false means do show.
"Luke Plant" <luke@.mailinator.com> wrote in message
news:do8hgo$9vq$1$8302bc10@.news.demon.co.uk...
> victoras wrote:
>> How can remove a row from my report if the results is empty or a null?
>> If i have a null value , then i get a white space row in report - this is
>> not looking good !
>> Can somebody help me, please !?
> You need to write your SQL query to exclude that row, something like:
> SELECT col1, col2 ... FROM mytable WHERE col1 IS NOT NULL
> Luke

How can I write this SQL statement when the string value is a variable

Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, MattJust declare @.StockSymbol as nchar or nvarchar and you can simply use

SET @.AlertSymbol = @.StockSymbol

The N in N'foo' tells SQL Server that 'foo' is in unicode. The same applies to all n<bar> datatypes.|||Originally posted by mt404
Hi. I was wondering how I might be able to write the following SQL statement | SET @.AlertSymbol = N'MSFT' | when I want to replace the MSFT with the variable @.StockSymbol?

I'd like to do something like | SET @.AlertSymbol = N'@.StockSymbol' | but that doesn't seem to work as SQL isn't evaluating @.StockSymbol but rather treating it as a string.

All of the following return errors:

SET @.AlertSymbol = N@.StockSymbol
SET @.AlertSymbol = N+@.StockSymbol
SET @.AlertSymbol = N&@.StockSymbol

Thanks, Matt

Nchar/varchar has higher precedence than char/varchar. Hence, an implicit conversion should take care of this for you.

e.g.

declare @.AlertSymbol nvarchar(10),
@.StockSymbol varchar(10)

set @.StockSymbol='MSFT'

set @.AlertSymbol=@.StockSymbol

--sql2k
select sql_variant_property(@.AlertSymbol,'BaseType'), @.AlertSymbol|||Thaks to both of you for helping me out and teaching me what the N'foo' actually meant.

How can i view the value of a variable during debug?

Thats the question... i can see the value of columns adding viewers, but how can i see variables?

Thanks!

Add a break point to one of your control flow tasks (by right clicking on it) and then when debug stops at that break point, you can go up to the Debug menu and select the Locals window to be displayed.|||

Thanks Phil,

I do that, but my variable doesnt appear in the window...

|||

Which window are you looking at? It should be in the Locals window; you have to expand the variables node...

|||

Sorry for my delay rafael, i was on holidays ;-)

I am looking at "Debug/windows/local variables", but nothing appears in this window.

Edit: I finally found the solution, the problem was taht i didnt insert the break point on control flow. Regards.

How can i view the value of a variable during debug?

Thats the question... i can see the value of columns adding viewers, but how can i see variables?

Thanks!

Add a break point to one of your control flow tasks (by right clicking on it) and then when debug stops at that break point, you can go up to the Debug menu and select the Locals window to be displayed.|||

Thanks Phil,

I do that, but my variable doesnt appear in the window...

|||

Which window are you looking at? It should be in the Locals window; you have to expand the variables node...

|||

Sorry for my delay rafael, i was on holidays ;-)

I am looking at "Debug/windows/local variables", but nothing appears in this window.

Edit: I finally found the solution, the problem was taht i didnt insert the break point on control flow. Regards.

How can I Use a User Defined function as a default for a column value?

Hi, I have an int column for which I want to set to a certain code value, e.g.: In my table "Biometric" I have a column called "BiometricStatusCode" which is a foreign key to a Table/Column "REF_PERSON_BiometricStatusCodes:BiometricStatusID"

In that REF_PERSON_BiometricStatusCodes table, I have a row of data that returns a value of "All Others", the PK id for this row happens to be 3.

I can enter 3 as my default for the column in my original "Biometric" table...that will work fine and return "All Others" when joined...as long as the PK for "All Others" is 3. However, I cannot guarantee that in customer installations, hence I want to use a function that returns the Int/PK code for the row of data that contains "All Others" in my REF_PERSON_BiometricStatusCodes table.

So..........I created this function:

Create FUNCTION [dbo].[ufn_SelectDefaultBiometricStatusCode]()

RETURNS int

AS

BEGIN

DECLARE @.ret int;

SELECT @.ret = [BiometricStatusID]

FROM [AFR].[dbo].[REF_PERSON_BiometricStatusCodes] where

Upper([BiometricStatusDescr]) = 'ALL OTHERS';

IF (@.ret IS NULL)

SET @.ret = 0

RETURN @.ret

END;

The function works fine, it's not full proof...but is ok.

When I enter the function name, no quotes in the "Default Value or Binding" property in the table designer in sql 2005, it returns an error: "error validating the default for column".

The column is an int, the function returns an int... I can't figure it out..

Anyone have any ideas?

thanks.

You can use a computed column.

If you use SQL Server Management Studio to modify the table - use the column properties tab - Computed Column Specification - Formula.

how can i use "order by" with empty values

hi all.
how can i use "order by" with empty values
In SQL server, if record have empty value wills display firts, i need display empty value at last
please help me, thanks

use following query...

Select Value1, Value2,Value3 From table

Order By Case When Value1 is Null or Value1 = '' Then 1 Else 0 End, Value1