is there a way to have conditional counts or conditional running values?
such as counting the number of active customers verses the total number of
customers...
IIF(Fields!CustomerStatus.Value = "Active", Count(Fields!CustomerID.Value),
Nothing)
OR
IIF(Fields!CustomerStatus.Value = "Active",
RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
thanks,
--jimmyI haven't tried this but maybe
Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
Otherwise create a computed column with the IIF,
then SUm( the computed column)
(You might also be able to use Excel's sumif function directly>)
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"jimmy" <jimmy@.discussions.microsoft.com> wrote in message
news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> is there a way to have conditional counts or conditional running values?
> such as counting the number of active customers verses the total number
of
> customers...
> IIF(Fields!CustomerStatus.Value = "Active",
Count(Fields!CustomerID.Value),
> Nothing)
> OR
> IIF(Fields!CustomerStatus.Value = "Active",
> RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
>
> thanks,
> --jimmy|||worked perfectly, thanks!
"Wayne Snyder" wrote:
> I haven't tried this but maybe
> Sum(iif(Fields!CustomerStatus.Value="Active",1,0))
> Otherwise create a computed column with the IIF,
> then SUm( the computed column)
> (You might also be able to use Excel's sumif function directly>)
> --
> Wayne Snyder MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> (Please respond only to the newsgroup.)
> I support the Professional Association for SQL Server ( PASS) and it's
> community of SQL Professionals.
> "jimmy" <jimmy@.discussions.microsoft.com> wrote in message
> news:4E42351E-2196-42D8-AB8E-B44B34DDC1E3@.microsoft.com...
> >
> > is there a way to have conditional counts or conditional running values?
> > such as counting the number of active customers verses the total number
> of
> > customers...
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> Count(Fields!CustomerID.Value),
> > Nothing)
> >
> > OR
> >
> > IIF(Fields!CustomerStatus.Value = "Active",
> > RunningValue(Fields!CustomerID.Value, Count, Nothing), Nothing)
> >
> >
> > thanks,
> > --jimmy
>
>
Showing posts with label customers. Show all posts
Showing posts with label customers. Show all posts
Thursday, March 22, 2012
Thursday, March 8, 2012
Conditional Insert in SQL Server 2000?
I need to do a conditional insert, and I am afraid that if I do it in 2 steps it will create bugs...
I have a table called customers and a table called locks
customers has 3 fields CustomerID, FirstName, and LastName
locks has fields LockID, CustomerID, UserID, SessionID, and TimeStamp
I need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.
This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.
I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.
Any ideas or comments?This code from myPreventing Duplicate Record Insertion on Page Refresh article should do what you need (note the use of BEGIN TRANSACTION, EXISTS, and WITH (UPDLOCK
I have a table called customers and a table called locks
customers has 3 fields CustomerID, FirstName, and LastName
locks has fields LockID, CustomerID, UserID, SessionID, and TimeStamp
I need to check if there are any records in Locks for a CustomerID and if there are none, I need to insert One.
This is going to tell my application that this Customer record is locked so no one else can edit it, until the Lock record is deleted.
I just don't want to make 2 trips, One to check if the Lock exists, then One to insert the Lock, since this could allow 2 locks to be created if the timing is correct.
Any ideas or comments?This code from myPreventing Duplicate Record Insertion on Page Refresh article should do what you need (note the use of BEGIN TRANSACTION, EXISTS, and WITH (UPDLOCK
Listing 4 – Stored Procedure spAddEmployee_UsingExists
CREATEPROCEDURE spAddEmployee_UsingExists
(
@.FirstNamevarchar(50),
@.LastNamevarchar(50)
)
AS
DECLARE @.Resultint
BEGINTRANSACTION
IFEXISTS
(
SELECT
NULL
FROM
EmployeesWITH (UPDLOCK)
WHERE
FirstName = @.FirstNameAND
LastName = @.LastName
)
BEGIN
SELECT @.Result = -1
END
ELSE
BEGIN
INSERTINTO
Employees
(
FirstName,
LastName
)
VALUES
(
@.FirstName,
@.LastName
)
SELECT @.Result =@.@.ERROR
END
IF @.Result <> 0
BEGIN
ROLLBACK
END
ELSE
BEGIN
COMMIT
END
RETURN @.Result
Subscribe to:
Posts (Atom)