Showing posts with label example. Show all posts
Showing posts with label example. Show all posts

Thursday, March 29, 2012

convert a negative number to 0

Is it possible to convert a negative number to 0?

for example -4 need to be 0 and 3 needs to be 3.

try this

SELET (CASE WHEN MyField <= 0 THEN 0 ELSE MyField END) AS MyNonNegativeField
FROM MyTable|||That will work, except that I have a very lengthy expression for "MyField". I don't want to have to duplicate that expression.|||

If you really don't want to repeat the expression then you could turn your query into a derived table and then SELECT from the derived table, incorporating the CASE logic into that SELECT list.

SELECT t.Column1,
CASE WHEN t.Column2 < 0 THEN 0 ELSE t.Column2 END

FROM (SELECT <My Complex Expression1> AS Column1,

<My Complex Expression2> AS Column2

FROM etc...) t

Chris

|||

Another very simple way is UDF,

Create Function dbo.NegativeToZero(@.Value as int)

returns Int

as

Begin

Return Case When @.Value < 0 Then 0 else @.Value End;

End

Go

Select {Your Columns}, ....., dbo.NegativeToZero(Your Lengthy Expression) as Data

From

YourTable

|||

Just be aware that introducing a UDF into a query often has a negative impact on performance. Better performance is usually obtained by incorporating a UDF's code into the body of the query (although I appreciate that this isn't always possible).

Chris

Tuesday, March 20, 2012

Conversation Timers, Alive or Dead?

What is actually happening when a conversation timer is being used in terms of the dialog_timer column in sys.conversationendpoints? For example, I was using the following query to tell me which conversation timers are currently running:

SELECT * FROM sys.conversation_endpoints

WHERE dialog_timer > '1900-01-01 00:00:00.000'

However, I have noticed that periodically the dialog_timer value goes back to '1900-01-01 00:00:00.000' and the query fails, starting up another timer. Then, the original timer magically appears again, working just fine. So, I changed to this query:

SELECT * FROM sys.conversation_endpoints

WHERE far_service

IN

(

'TimerService1',

'TimerService2',

'TimerService3',

)

But this seems like the long way around and doesn't really indicate that the timer is running or not, just that its present in the sys.conversationendpoints catalog view. What is the proper way to see if timers are running? If one dies for some reason, I need to restart it.

What is going on with this? The timers keep getting set back to

'1900-01-01 00:00:00.000' and the jobs don't get executed. What does this mean? How to correct it - its seems that the conversation_handle is still there and fine, but the timer isn't working.

|||

The dialog_timer is set whenever the BEGIN CONVERSATION TIMER is executed. It's value is the time (UTC) at which the timer should 'fire' (send the Timer message). The dialog_timer is reset back to '1900-01-01 00:00:00.000' whenever the timer message is enqueued into the service queue. If you see the dialog_timer value reset, it means the timer has fired and a timer message is waiting in the service queue.

Conversation group id question

HI

I have an example ( see below ).

I expect to have all messages sent using this code to have the same group id but they are all different. what I am doing wrong?

Leonid.

DECLARE @.conversationHandle uniqueidentifier

DECLARE @.usergroup uniqueidentifier

select @.usergroup = uid from bvuser where userid = 1

select @.usergroup

Begin Transaction

BEGIN DIALOG @.conversationHandle

FROM SERVICE [BvMainResponseService]

TO SERVICE 'BvMainService'

ON CONTRACT [BvMainContract]

WITH RELATED_CONVERSATION_GROUP = @.usergroup;

-- Send a message on the dialog

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [BvTaskMsg]

(N'Test')

commit

As far as i understand it you expand a conversation group by adding additional dialogs related to the first one:

For example:

DECLARE @.conversationHandle uniqueidentifier

Begin Transaction

BEGIN DIALOG @.conversationHandle

FROM SERVICE [BvMainResponseService]

TO SERVICE 'BvMainService'

ON CONTRACT [BvMainContract]

WITH RELATED_CONVERSATION_GROUP = @.conversationHandle;

-- Send a message on the dialog

SEND ON CONVERSATION @.conversationHandle

MESSAGE TYPE [BvTaskMsg]

(N'Test')

commit

You keep using the conversation handle from the begin dialog to keep the same conversation, i could be mistaken as i have not really tried it , but i think that is the theory anyway.

Thanx

|||

this is from BOL

If related_conversation_group_id does not reference an existing conversation group, the service broker creates a new conversation group with the specified related_conversation_group_id and relates the new dialog to that conversation group.

so as I understand this - new conversation group id is created when BEGIN DIALOG is used for the first time with specified ID, and then ... here is BOL again

Specifies the existing conversation group that the new dialog is added to. When this clause is present, the new dialog will be added to the conversation group specified by related_conversation_group_id.

But obviously I am doing something wrong here becuase it doesn't work as I expect it.

Leonid.

|||

In the test you've shown the conversation should have the same conversation group id. How are you looking up the conversations?

Here is a test script that shows that the related_conversation_group creates conversation in the same group, and the first BEGIN CONVERSATION creates the group itself, just as you expect:

use [tempdb];

go

create queue [testQueue];

create service [testService] on queue [testQueue];

go

create queue [targetQueue];

create service [targetService] on queue [targetQueue] ([DEFAULT]);

go

declare @.cg uniqueidentifier;

declare @.h uniqueidentifier;

select @.cg = newid();

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

begin dialog conversation @.h

from service [testService]

to service N'targetService', N'current database'

with related_conversation_group = @.cg,

encryption = off;

send on conversation @.h;

select * from sys.conversation_endpoints where conversation_group_id = @.cg;

HTH,
~ Remus

Converion for VARCHAR to FLOAT

Hi-

I am trying the following example.

create table mytest (a float, b float(8))

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b

PRINT @.a

The result is 0.40000000000000002.

Can some one tell me what am I doing wrong? Appreciate your time.

- SaratOriginally posted by sbaru
Hi-

I am trying the following example.

create table mytest (a float, b float(8))

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b

PRINT @.a

The result is 0.40000000000000002.

Can some one tell me what am I doing wrong? Appreciate your time.

- Sarat
why do u need the table? what r u trying to do?

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b
PRINT @.a

it prints '0.4' for me|||Originally posted by sbaru
Hi-

I am trying the following example.

create table mytest (a float, b float(8))

declare @.a FLOAT
declare @.b varchar(10)

set @.b = '0.4'

set @.a = @.b

PRINT @.a

The result is 0.40000000000000002.

Can some one tell me what am I doing wrong? Appreciate your time.

- Sarat

Yes, this is a common problem with all computers. When you declare float, the computer represents the numbers internally as approximations. The approximation is really really close to your number but often not exact.

To avoid this when working with monetary values, for example, I frequently will declare the field to be integer and just think of the value as pennies (for U.S. currency) and multiply the result by 100 to get dollars.

Monday, March 19, 2012

Controlling the cursor position

Let's say I run a query that returns a 100k records. Is there a way to
control where the cursor is in the result set. For example, can I get it
to jump to row 50k and return the next 500 rows?Taken from Transact SQL help:
-- Fetch the row that is two rows prior to the current row.
FETCH RELATIVE -2 FROM authors_cursor
Hope that helps
"preston" wrote:

> Let's say I run a query that returns a 100k records. Is there a way to
> control where the cursor is in the result set. For example, can I get it
> to jump to row 50k and return the next 500 rows?
>|||Hi, preston
See: http://www.aspfaq.com/show.asp?id=2120
Razvan

Sunday, March 11, 2012

control the percentage of processor used by SQL server

exists a form to control the percentage of processor used by SQL
server? for example, not use more than 60% percent.
I don't think you can do that.You can tell the number of cpu's to use but but
not by
percent
"hongo32" wrote:

> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegro ups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/dow...srvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David
|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsserv...m/default.mspx
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegro ups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>

control the percentage of processor used by SQL server

exists a form to control the percentage of processor used by SQL
server? for example, not use more than 60% percent.I don't think you can do that.You can tell the number of cpu's to use but bu
t
not by
percent
"hongo32" wrote:

> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/do...nsrvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsser...lt.ms
px
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>

control the percentage of processor used by SQL server

exists a form to control the percentage of processor used by SQL
server? for example, not use more than 60% percent.I don't think you can do that.You can tell the number of cpu's to use but but
not by
percent
"hongo32" wrote:
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>|||"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>
First off the basic best practice is to use dedicated SQL Server boxes.
What else do you think needs CPU.
But yes, there are two tools for allocating CPU resources to SQL Server
instances: SQL Server processor affinity and Windows System Resource
Manager.
CPU affinity is configured inside SQL Server and basically allows you to
prevent a SQL Server instance from using one or more of your processors.
Windows System Resource Manager is more sophisticated and allows realtime
allocation of CPU resources to processes based on policies and server load.
So SQL Server could be allowed to use 100% of a CPU when other processes
aren't using it, but restricted to 60% when there is contention.
http://www.microsoft.com/technet/downloads/winsrvr/wsrm.mspx
On a multi-processor system you can set the processor affinity for SQL
Server to prevent it from using some of your CPU's.
David|||If you're on Win2003 have a look at WSRM
http://www.microsoft.com/windowsserver2003/technologies/management/wsrm/default.mspx
For Win2k you can use Aurema ArmTech
http://www.aurema.com/products/winsql.php
We've used both and they have worked well in our environment
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"hongo32" <hongo32es@.yahoo.com> wrote in message
news:1131739796.126120.60050@.g44g2000cwa.googlegroups.com...
> exists a form to control the percentage of processor used by SQL
> server? for example, not use more than 60% percent.
>

Thursday, March 8, 2012

Control number of report copies within Crystal

Hi all,

I need some way to control the number of report copies within Crystal. For example, is there any way i can set a formular so that CR will produce two copies of the report with the first copy will have a text field "Office copy" and the second copy will have a text field "Factory Copy" ??

Thanks and CheersYes it is possible (by passing your text as a parameter, for example).
You didn't mention if you are running report from CR or from a custom application, but in either case, you will have to run the report twice.

Saturday, February 25, 2012

Contains(@v1, @v2) Is this legal?

I am attempting to perform a contains of one variable string in another.
here is a simple example of what I am attempting to do, this should return
true, but I am not sure if this is a limitation of sql server, that it will
now allow a contains on two datatypes. Any ideas?
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if (Contains(@.t2, @.t1))
print 'true'
else
print 'false'
Thanks.Hi, kapsolas
You probably want to use the CHARINDEX function:
IF CHARINDEX(@.t1,@.t2)<>0 ...
For more informations, see:
http://msdn2.microsoft.com/en-us/library/ms186323.aspx
Razvan|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
>I am attempting to perform a contains of one variable string in another.
> here is a simple example of what I am attempting to do, this should return
> true, but I am not sure if this is a limitation of sql server, that it
> will
> now allow a contains on two datatypes. Any ideas?
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if (Contains(@.t2, @.t1))
> print 'true'
> else
> print 'false'
> Thanks.
Another solution:
declare @.t1 varchar(30),
@.t2 varchar(30)
set @.t1 = 'Te'
set @.t2 = 'Test'
if @.t2 like '%' + @.t1 + '%'
print 'true'
else
print 'false'|||Raymond,
that is the solution I have implemented. Using the LIKE. I wanted to clean
it up a bit to make it more readable by using the Contains.
I'll play with the char index as recommended in the other post as well.
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F05CD410-BFDD-4627-8308-5B774805A2AA@.microsoft.com...
> Another solution:
> declare @.t1 varchar(30),
> @.t2 varchar(30)
> set @.t1 = 'Te'
> set @.t2 = 'Test'
> if @.t2 like '%' + @.t1 + '%'
> print 'true'
> else
> print 'false'
>
>|||"kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> Raymond,
> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
> I'll play with the char index as recommended in the other post as well.
I have no experience with Contains.
This is the information I got in BOL:
...You can use the CONTAINS predicate to search a database for a specific
phrase. Of course, such a query can be written using the LIKE predicate.
However, many forms of CONTAINS provide far more text query capabilities
than can be obtained with LIKE. Additionally, unlike using the LIKE
predicate, a CONTAINS search is always case insensitive...
So, if you are not using the extra "query capabilities" of Contains, I
suggest you use one of the other solutions that you got for this post.
Of course, the best would be to test all solutions with your database and
data to find the one that performs the best.|||Thanks for that piece Raymond.
For now I'll use the LIKE and as soon as I have a bit more time i'll
investigate the contains a bit more.
Thanks for your help
"Raymond D'Anjou" wrote:

> "kapsolas" <kapsolas@.discussions.microsoft.com> wrote in message
> news:F5F39D6F-0723-4EE5-B032-E425A6942D33@.microsoft.com...
> I have no experience with Contains.
> This is the information I got in BOL:
> ...You can use the CONTAINS predicate to search a database for a specific
> phrase. Of course, such a query can be written using the LIKE predicate.
> However, many forms of CONTAINS provide far more text query capabilities
> than can be obtained with LIKE. Additionally, unlike using the LIKE
> predicate, a CONTAINS search is always case insensitive...
> So, if you are not using the extra "query capabilities" of Contains, I
> suggest you use one of the other solutions that you got for this post.
> Of course, the best would be to test all solutions with your database and
> data to find the one that performs the best.
>
>|||> that is the solution I have implemented. Using the LIKE. I wanted to clean
> it up a bit to make it more readable by using the Contains.
I don't know why you think that's cleaner or more readable. I guess for
someone who has never used T-SQL and only used FTS, but I think that'd be a
pretty rare bird.
A

Tuesday, February 14, 2012

Construct Variable Name?

Can you contruct a variable name from another variable? For example, I want the following to PRINT 10

DECLARE @.var1 INT
DECLARE @.var2 INT

SET @.var1 = 10
SET @.var2 = 20

PRINT '@.var' + '1'

This prints the variable name, not the contents of the var. I tried to parse it with square brackets, but no luck.

Thanks,
CarlYou can do this using dynamic SQL, though I'm not sure it would work for the specific PRINT statement in your example.|||Ya, I need a varName in a var. A datatype of VARNAME. in some langs you just parse it correctly and it works, some it won't.

if you substitute EXEC for PRINT, it says you must declare variable 'var1', as you can see it obviously is being declared and interpretted the way it was typed.

Carl|||Actually, dynamic SQL will not work for this example, because the scope of the variables is limited to the procedure in which they are created.

Please explain what you are trying to do, and maybe we can come up with a satisfactory solution.|||I am just trying to simplify the code.

In stead of:

IF @.var1 < @.var2
SET @.var3 = DATEADD(n,GETDATE(), @.var1)
ELSE
SET @.var3 = DATEADD(n,GETDATE(), @.var2)

I was hoping to simply:

SET @.var3 = DATEADD(n,GETDATE(), @.var + @.CurrentVarIndex)

I already know which one I want, @.var1 or @.var2, it's just that I have to IF and have 2 lines to do the work of 1.|||... it's just that I have to IF and have 2 lines to do the work of 1.I've seen worse. Using dynamic SQL or some other hack work around to do this is going to end up making your code more complicated, not simpler.|||I know what you mean regarding the dynamic sql and "the sea of red". can make reviewing your code quite miserable.
Carl

Constraints in SQL server

I wanna know how to define constraints in sql server.
everytime i try something it says "error validating constraint'

does someone have an example how a constraint have to look like?

PS: i want to the constraint that check's this:
i have an order.. an order haves orderlines
and we have a 'contract' .. and contract have 'contractlines'

.. an order can became a 'contract' .. and some of the orderlines will be 'contractlines'

so a contract have a FK:orderID.. and a contractline have FK:contractID&orderlineID

i want to check if the orderlineID in contractline exists in the correspondending order..

>>>something like:
orderlineID exists in ( select orderlineID from orderline left join
order on order.orderID = orderLine.orderID left join contract on
contract.orderID = order.orderID left join contract on contractLine.contractID = contract.contractIDIt sounds like you want foreign key constraints:


create table Orders
(
OrderID int not null,
primary key (OrderID),
)
create table OrderLines
(
OrderID int not null,
OrderLineID int not null,
primary key (OrderID, OrderLineID),
)
create table Contracts
(
ContractID int not null,
OrderID int null,
primary key (ContractID),
foreign key (OrderID) references Orders(OrderID),
)
create table ContractLines
(
ContractID int not null,
ContractLineID int not null,
OrderID int null,
OrderLineID int null,
primary key (ContractID, ContractLineID),
foreign key (OrderID, OrderLineID) references OrderLines(OrderID, OrderLineID),
)

I don't think this is a very good design for several reasons, including the following:
1) I used nulls
2) I have no idea if the various IDs are natural or surrogate keys
3) I assumed a lot of things about how the four concepts orders, contracts, order lines and contract lines are related

constraint?

Hi,
I was wondering how i could set up a constraint if i want to prevent
duplicates.
For example, in a table where i have 4 columns:
-id (identity and primary)
-contactid
-type
-period
One can have values like:
id | contactid | type | period
1 c123 1 11
2 c355 | 2 | 11
Now what i would like to do is if someone inserts a record like id 1 and
the period is 12, then the constraint should apply. If someone inserts a
record like id 2 and the period is 12 then it's ok.
Is this possible?Somethinmg like this for example:
ALTER TABLE your_table
ADD CONSTRAINT ak_constraint_name
UNIQUE (contactid, period) ;
David Portas
SQL Server MVP
--|||"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>
I'm still not quite sure what you are after here. You can create a UNIQUE
constraint/index across multiple columns in a table.
For your first example, a UNIQUE constraint across (id, contactid, type)
would do the trick.
What is the difference with record id 2? Is it because the type column has
pipe symbols in it? I'm not following.
If it is more complex than the simple UNIQUE constraint, then you can either
apply your rules in a stored procedure, or with a trigger.
Rick Sawtell
MCT, MCSD, MCDBA|||Create a unique index, duplicate inserts will fail.
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Jason
What is about type column? What is the purpose of this column?
"Jason" <jasonlewis@.hotrmail.com> wrote in message
news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I was wondering how i could set up a constraint if i want to prevent
> duplicates.
> For example, in a table where i have 4 columns:
> -id (identity and primary)
> -contactid
> -type
> -period
> One can have values like:
> id | contactid | type | period
> 1 c123 1 11
> 2 c355 | 2 | 11
> Now what i would like to do is if someone inserts a record like id 1 and
> the period is 12, then the constraint should apply. If someone inserts a
> record like id 2 and the period is 12 then it's ok.
> Is this possible?
>|||Rick Sawtell wrote:
> "Jason" <jasonlewis@.hotrmail.com> wrote in message
> news:%23%239xqBv2FHA.3876@.TK2MSFTNGP09.phx.gbl...
>
>
> I'm still not quite sure what you are after here. You can create a UNIQU
E
> constraint/index across multiple columns in a table.
> For your first example, a UNIQUE constraint across (id, contactid, type)
> would do the trick.
> What is the difference with record id 2? Is it because the type column h
as
> pipe symbols in it? I'm not following.
> If it is more complex than the simple UNIQUE constraint, then you can eith
er
> apply your rules in a stored procedure, or with a trigger.
> Rick Sawtell
> MCT, MCSD, MCDBA
>
>
Hi Rick,
The pipes are nothing, it's a seperator.
The difference is, that a contact with type 1 may be inserted only once.
Type 2 can have several inserts.
So i only want to constrain contacts which has type 1.|||create table semi_unique(id int primary key, stype int, uq int, u1 as
case when stype=1 then null else id end)
-- uq must be unique for type 1 only
create unique index u1 on semi_unique(uq, u1)
insert into semi_unique(id, stype, uq) values(1,1,1)
-- should fail
insert into semi_unique(id, stype, uq) values(2,1,1)
-- should succeed
insert into semi_unique(id, stype, uq) values(3,2,1)
insert into semi_unique(id, stype, uq) values(4,2,1)
select * from semi_unique
drop table semi_unique|||Alexander Kuznetsov wrote:
> create table semi_unique(id int primary key, stype int, uq int, u1 as
> case when stype=1 then null else id end)
> -- uq must be unique for type 1 only
> create unique index u1 on semi_unique(uq, u1)
> insert into semi_unique(id, stype, uq) values(1,1,1)
> -- should fail
> insert into semi_unique(id, stype, uq) values(2,1,1)
> -- should succeed
> insert into semi_unique(id, stype, uq) values(3,2,1)
> insert into semi_unique(id, stype, uq) values(4,2,1)
> select * from semi_unique
> drop table semi_unique
>
Hi it worked, but what if i add another column to it.

Friday, February 10, 2012

consolidating member

Is it possible to add an extra member that consolidates all other members. For example, I'm looking at totals by department and account. I need an extra department that sums by account across all other departments. Like this:

Dept A

Account 1 $10

Account 2 $12

Dept B

Account 1 $15

Account 2 $11

Consolidated(A + B)

Account 1 $25

Account 2 $23

Assuming that Account and Department are separate dimensions, doesn't the "All" member, at the root of the Department hierarchy, fulfill this requirement - if not, could you explain why in some more detail?|||Yes, the "all" member does contain the data I need. My problem is in generating the final printed report. I guess I need to think about solving the problem with the reporting tool. I'm using reporting services. When I get to the end of a department, I need to display data from the "all" member which consolidates all sub departments. Then, when I get to the end of all departments, I need to display consolidating data for all departments.|||

Reporting Services does indeed present problems when dealing with an "All" member. This MSDN paper - though based on SQL Server 2000 - provides some guidance :

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql2k/html/olapasandrs.asp?frame=true

>>

Integrating Analysis Services with Reporting Services

Sean Boon
Microsoft Corporation

June 2004

Applies to:
Microsoft SQL Server 2000

Summary: Create a compelling solution for your customer that defines and manages great-looking Analysis Services reports, and quickly answers analytical questions to improve traditional reporting scenarios.

...

Another behavior of the flattening algorithm is that the "[(ALL)]" level is not included in the dataset. This has a couple of implications. The first is that if you want to include data from the "All" member of a dimension, you'll need to create a calculated member to represent this member. This can be accomplished in a couple of different ways. The first method would be to create a calculated member on the Measures dimension and for the definition of the calculated member refer to the current member's name or unique name. There are several examples of this method represented in later sections of this whitepaper.

Note The "All" level of a dimension is not included in the field set that is returned to Reporting Services.

The second implication of the "All" level not being represented in the dataset is that calculated members, usually defined without a parent member, will need to change so that they do have a parent member. This only applies in cases where the calculated member does not belong to the Measures dimension. In many cases, when calculated members are defined on a non-Measures dimension, the parent member property is left blank. This can be changed in the calculated member dialog box as shown below

...

>>