RunningValue In Matrix

Hello, I was wonder if it is possible to have a RunningValue per row on a
matrix? I want to Sum a number onto itself and show in each columnfor the
first row and reset for the second row and start all over again. I am going
to use conditional statements to control what value is being summed based on
the rowgroup I am at, but that number will have to sum onto itself for every
column in each row.
Ie. 38, 76, 114, 152, 190 in first row
second row: 38, 76, 114, 152, 190
These are based on the variable Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value
Thanks,
BJYes reporting services literally provides a runningvalue function
called runningvalue. In your case runningvalue(Sum(Fields!
Wc_TOTALS_DIVIDEND_ROUNDED)). Put it in the data field of the matrix.
Make sure to use the appropriate column group.

RunningValue ?

I am having problems with the following -

this represents the dataset Date Customer Voucher Number Document Amount Check Amount 7/15/2006 Company G 101 15.00 45.00 7/15/2006 Compnay G 101 25.00 45.00 7/15/2006 Compnay G 101 5.00 45.00 7/18/2006 Company A 102 35.00 35.00 7/25/2006 Company M 103 45.00 50.00 7/25/2006 Company M 103 15.00 50.00

This represents the final report this is what I am getting Date Customer Voucher Number Document Amount Check Amount Check Amount (Hidden) Group 1 Header = Voucher Number (Hidden) Detail 7/15/2006 Company G 101 15.00 45.00 45.00 (Hidden) Detail 7/15/2006 Company G 101 25.00 45.00 45.00 (Hidden) Detail 7/15/2006 Company G 101 5.00 45.00 45.00 Group 1Footer 7/16/2006 Company G 45.00 45.00 135.00 (Hidden) Group1Header = Voucher Number (Hidden) Detail 7/18/2006 Company A 102 35.00 35.00 35.00 Group 1 Footer 7/19/2006 Company A 35.00 35.00 35.00 (Hidden) Group 1 Header = Voucher Number (Hidden) Detail 7/25/2006 Company M 103 45.00 50.00 50.00 (Hidden) Detail 7/25/2006 Company M 103 15.00 50.00 50.00 Group 1 Footer 7/25/2006 Company M 103 60.00 50.00 100.00 Report Footer Grand Total: 140.00 130.00 270.00

(I am using a table)

I can get the correct total for the Group 1 Footer Check Amount by dividing the Total by a count of the Voucher Numbers, but I have not been able to add the Group 1 totals of the Check Amount for the Grand Total in the footer. I have made various attempts using RunningValue with the Group 1 Check Amount Total field, but always getting a error - mostly that the scope was not correct.

Any suggestions will be appreciated.

Ok, so I solved my own problem. Maybe this will help you. I summed the Group 1 Footer cells with the correct Check Amount totals (45, 35,50) in a textbox in the Report Footer =Sum(ReportItems!CHEKTOTL_1.value). Voila! I was trying to make this way too complicated!|||

I am having the exact same problem and have tried about four different approaches without success.

Where are my CRW running totals?!

I my case it is cash receipts and applications (whereas your case appears to be payables).

How did you get your group 1 footer to get just the check amount. I have tried summing a iif(...) where only the first row of a check shows the value - and this did not work (cannot sum a calculation!). You indicate that you divided the total by the number of rows - would you mind showing the expression.

How then did you get this expression in the group 1 footer to be summed - I have all but givin up on runningvalue due to scope issues - this also killed my attempt at VBA code.

Thanks

Philip Neufeld

|||

Here is my solution. I hope my explanation makes sense.

I did some aggregating in the the SQL statement - summing the DocAmount and grouping by Voucher Number, Voucher Date, Check Total and Vendor.

My report is for multiple companies, so in the design portion I used a list data region sorted by company. Then I added a table, grouped by Company.

I put the Date, Voucher Number, Vendor, Voucher Amount and Check Amount in the detail rows of the table and summed the Voucher Amount and Check Total in the Table Summary row.

On the General tab of the table properties, I checked Insert a page break after this table and Fit table on one page if possible. When the report is exported to Excel, each company appears on a separate worksheet and the summaries also appear. That was another problem I was having with another solution I had devised - the totals appeared on my screen in the Reporting Manager, but apparently the formulas that I used to get the totals weren't exporting into Excel.

Hope this helps!

sjm

sql

Running Totals

We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date

Week to Date Month to Date Year to Date

Item Number

I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?

Nancy

Nancy,

Here's an example of running values with a IIF statement. I believe you can accomplish what you're trying to do the same way.

RunningValue(IIf(Fields!elca_employer_number.Value<0,Fields!elca_employer_number.Value,nothing),count,nothing)

Ham

|||

I have the Week to Date and Year to Date working but the period (or month to date) is still causing problems.

The data comes in at the weekly level, so I was able to use:

=iif(Fields!WEEK.Value=Sum(Fields!CurrentWeek.Value, "Current"),Sum(Fields!Ordered.Value),0)

The Year to Date is was able to do a simply sum, but for the Period to Date totals I'm still stuck.

When I try:

=RunningValue(iif(Fields!Period.Value=Sum(Fields!CurrentPeriod.Value, "Current"),Sum(Fields!Ordered.Value),0),sum, Group3)

I get an error - The value expression for the textbox contains an agrregate funtion in the argument to another aggregate function.

I'm trying to evaulate if the Period in my main dataset is equal to the Period in the Current dataset. That part is easy. Then I need to sum that up and display the data on one line

WTD PTD YTD

Item 5 50 500

In Crystal Reports I would have created a field called Period_Ordered that evaluates the if Period=Current Period, then I would have summed the Period_Ordered field in my group footer. Visual Studio doesn't seem to allow this. Very frustrating.

|||

NCL,

Okay we have a partial answer,

On the this statement: RunningValue(iif(Fields!Period.Value=Sum(Fields!CurrentPeriod.Value, "Current"),Sum(Fields!Ordered.Value),0),sum, Group3) you have to remove the "SUM" values because you cannot aggregate inside of an aggregate function.

I do not think you need runningvalues because you are looking at your final totals. I believe this is all you need now. IIF(SUM(Fields!Period.Value,"Current")=Sum(Fields!CurrentPeriod.Value, "Current"),Sum(Fields!Ordered.Value),0)

Ham

|||

On the first one: the sum(fields!CurrentPeriod.Value,"Current") is coming from the dataset. The dataset only contains one line of data. It takes the current date and pulls back the valid Fiscal Week, Period and Year. Not sure why only the sum is available to add to my expression. Maybe that is a different issue.

I tried the second option but it's not calculating the data correctly. It's not summing all of the weeks in current period.

If Current dataset is pulling back today - 1/23/2007 Current Week =30 Current Period = 7

Report pulls:

Item Week Period Year Qty Current Week Current Peroid to date Year to Date

Details:

99999 1 1 2007 5 0 0 5

99999 6 2 2007 10 0 0 15

99999 29 7 2007 10 0 10 25

99999 30 7 2007 5 5 15 30

What I need the report to show in the item group footer is:

99999 5 15 30

Any ideas?

|||

N,

On your question for

What I need the report to show in the item group footer is:

99999 5 15 30

I going to go on the assume that you are return a field name period and a second field name currentPeriod

Fields!Item.value

Fields!CurrentWeek.value

Sum(IIF(Fields!Period.value = Fields!CurrentPeriod.value, Fields!Qty.value,0))

Sum(Fields!Qty.value)

I hope this helps.

Ham

sql

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jéjé wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jéjé wrote:
>> Hi,
>> I have to create a query which evalaute, for each year, the employees who
>> reached a particular number of absences and when this value is reached.
>> I have 1 000 000 records in my absence table.
>> my table is like this:
>> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
>> I want a list of 1 date by year by employee and by activity when the
>> runin absenceduration total reached 5 days.
>> Also, I'll evaluate the same formula but by quarter instead-of year and
>> the target value is 3 days.
>> AbsenceDurationInDays is a floating value like:
>> 0.5 = half a day (or 4hours of work)
>> any guide?
>> any sample query anywhere?
>> how to setup my indexes?
>> I'll store the result in a table using DTS, and I'll evaluate this query
>> 1 time a week.
>> thanks for your help.
>> Jerome.

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
select datepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and th
e
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote:sql

running total from stored proc

i need to return the number of rows from a query as something
eg select ID,column from table where column = something
then return ID,column,expr1 (which is the total of records returned)
is this possible ?
my query looks like
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')mark
You can look at an OUTPUT parameters in stored procedure (see BOL)
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
DECLARE @.ret INT
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
GROUP BY DateLeadReceived ,LeadLoggedBy,Type
RETURN @.ret
"mark" <mark@.remove.com> wrote in message
news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
> i need to return the number of rows from a query as something
> eg select ID,column from table where column = something
> then return ID,column,expr1 (which is the total of records returned)
> is this possible ?
> my query looks like
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
>
>
>|||Uri
What's the difference between using return @.var or using output paramter ?
"Uri Dimant" wrote:

> mark
> You can look at an OUTPUT parameters in stored procedure (see BOL)
>
> CREATE PROCEDURE CSR_Performance
> @.csr varchar(75),
> @.From varchar(50),
> @.To Varchar(50)
> AS
> DECLARE @.ret INT
> DECLARE @.fromdate datetime
> select @.fromdate=convert(datetime, @.from)
> DECLARE @.todate datetime
> select @.todate=convert(datetime, @.to)
> SELECT @.ret=COUNT(*), DateLeadReceived , LeadLoggedBy,Type
> FROM Lead
> WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
> (LeadLoggedBy = @.csr) AND (Type = 'Telephone')
> GROUP BY DateLeadReceived ,LeadLoggedBy,Type
> RETURN @.ret
>
> "mark" <mark@.remove.com> wrote in message
> news:3t-dnSGix75OI_HfRVn-pQ@.giganews.com...
>
>|||Well , a difference as you said RETURN (unlike OUTPUT) statement terminates
the batch and none of the statements within SP are executed. RETURN
statement is usually used to reterun an error number or @.@.idenitity value.
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||One is a return value and the other is output parameters. For a return value
, it can only be int and
most of us only use this to communicate success/error. You can have several
output parameters and
they can be of almost any datatype.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mal .mullerjannie@.hotmail.com>" <<removethis> wrote in message
news:F94557D7-2AAA-4B8A-8942-49821BA120E9@.microsoft.com...
> Uri
> What's the difference between using return @.var or using output paramter ?
>
> "Uri Dimant" wrote:
>|||that code didnt seem to work, but ive solved the problem like this
thanks
mark
CREATE PROCEDURE CSR_Performance
@.csr varchar(75),
@.From varchar(50),
@.To Varchar(50)
AS
declare @.total int
DECLARE @.fromdate datetime
select @.fromdate=convert(datetime, @.from)
DECLARE @.todate datetime
select @.todate=convert(datetime, @.to)
SELECT DateLeadReceived , LeadLoggedBy,Type
FROM Lead
WHERE (DateLeadReceived BETWEEN @.fromdate AND @.todate) AND
(LeadLoggedBy = @.csr) AND (Type = 'Telephone')
select @.total=@.@.rowcount
select @.total as totalcolumn
GO

Running total count in stored procedure

in my procedure, I want to count the number of rows that have errored
during an insert statement - each row is evaluated using a cursor, so
I am processing one row at a time for the insert. My total count to
be displayed is inside the cursor, but after the last fetch is called.
Wouldn't this display the last count? The problem is that the count is
always 1. Can anyone help?

here is my code,

... cursor fetch
begin ... cursor
if error then:
begin

INSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,
CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,
STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,
PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,
USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,
CUSTINDICATOR, DT_LOADED)
VALUES(@.ERRORNUM, @.ERRORDESC,
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY,
@.STATE, @.POSTALCODE, @.CONTACT, @.PHONE, @.SALESREPCODE,
@.PRICELEVEL, @.TERMSCODE, @.DISCPERCENT, @.TAXCODE,
@.USERCOMMENT, @.CURRENCY, @.EMAILADDRESS, @.CUSTOMERGROUP,
@.CUSTINDICATOR, @.DTLOADED)

SET @.ERRORCNT = @.ERRORCNT + 1

END --error

--
FETCH NEXT FROM CERNO_US INTO
@.CUSTOMERNUMBER, @.CUSTOMERNAME, @.ADDRESS1, @.ADDRESS2, @.CITY, @.STATE,
@.POSTALCODE, @.CONTACT,@.PHONE,@.SALESREPCODE, @.PRICELEVEL,@.TERMSCODE,
@.DISCPERCENT, @.TAXCODE, @.USERCOMMENT, @.CURRENCY,@.EMAILADDRESS,
@.CUSTOMERGROUP, @.CUSTINDICATOR, @.DTLOADED
--
IF @.ERRORCNT > 0
INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,
STATUS)
VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @.ERRORCNT, 'FAILED
INSERT/UPDATE')

END -- cursor
CLOSE CERNO_US
DEALLOCATE CERNO_USTracey (tracey.lemer@.itsservices.com) writes:
> in my procedure, I want to count the number of rows that have errored
> during an insert statement - each row is evaluated using a cursor, so
> I am processing one row at a time for the insert. My total count to
> be displayed is inside the cursor, but after the last fetch is called.
> Wouldn't this display the last count? The problem is that the count is
> always 1. Can anyone help?

As I read your code, you insert a row into PROCEDURE_RESULTS as soon as
@.ERRORCNT is > 0, that is 1.

I don't know what you mean with "be displayed is inside the cursor, but
after the last fetch is called" - that makes no sense to me. You don't
really display the data what I can see, and you should just as well
insert into PROCEDURE_RESULTS after you have dealloacated the cursor.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Running SQLServer and SQLServer Agent as Power User

We're trying to limit the number of user accounts with Admin level permissio
n
on our Win2K servers, especially SQL servers. We have created a domain level
account to run SQLServer and SQLAgent. We'd like to limit it to Power User
status instead of Admin status on the servers, but we cannot seem to start
and stop the services from SEM with only Power User status. We've checked
registry key permissions and everything seems to be configured properly. Is
this configuration even possible? Or does this account NEED to be local admi
n
on the server? Help would be appreciated. Thanks.The account that starts the services needs to have the "log on as a service"
right. Without this MSSQLServer and MS SQL Server Agent will not start.
It would also be more secure to use a domain account for this rather than a
local account as SQL then benefits from the integrated security of Windows
2000.
Also why would you want end users to have admin rights on the server at all?
This defeats the object of system security and resource accessibility. It
is best that they are Doman Users only then assign access rights to shares
on the servers.
HTH
Regards
Dazza
"gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
> We're trying to limit the number of user accounts with Admin level
> permission
> on our Win2K servers, especially SQL servers. We have created a domain
> level
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly.
> Is
> this configuration even possible? Or does this account NEED to be local
> admin
> on the server? Help would be appreciated. Thanks.|||We've following the instructions in MS article 283811 and ensured that the
account has all necessary extended user rights, such as act as part of
operating system, logon as batch job, logon as service. The fundamental
question is whether the account can run as Power User or does it need to be
Administrator? If it does not NEED to be Administrator, what other
configuration is necessary to let us use that account to stop and start the
SQLServer service, since Power User does not seem to have the rights. Thanks
.
"Dazza" wrote:

> The account that starts the services needs to have the "log on as a servic
e"
> right. Without this MSSQLServer and MS SQL Server Agent will not start.
> It would also be more secure to use a domain account for this rather than
a
> local account as SQL then benefits from the integrated security of Windows
> 2000.
> Also why would you want end users to have admin rights on the server at al
l?
> This defeats the object of system security and resource accessibility. It
> is best that they are Doman Users only then assign access rights to shares
> on the servers.
> HTH
> Regards
> Dazza
>
> "gbledsoe" <gbledsoe@.discussions.microsoft.com> wrote in message
> news:72CDD311-3C73-480E-9734-3E6F0E76DB09@.microsoft.com...
>
>|||gbledsoe wrote:
> We're trying to limit the number of user accounts with Admin level permiss
ion
> on our Win2K servers, especially SQL servers. We have created a domain lev
el
> account to run SQLServer and SQLAgent. We'd like to limit it to Power User
> status instead of Admin status on the servers, but we cannot seem to start
> and stop the services from SEM with only Power User status. We've checked
> registry key permissions and everything seems to be configured properly. I
s
> this configuration even possible? Or does this account NEED to be local ad
min
> on the server? Help would be appreciated. Thanks.
It should be, although not all sql feature are available. I am running
multiple instances with different plain domain user accounts. When you are
not sure about registry, user and ntfs permission change the account using
the enterprise manager.
When you need the proxy account to run scheduled dts packages create a
separate account for the sql agent service and make it local admin, unless
someone here can explain how to accomplice this without local admin rights.
have a look at this one:
http://support.microsoft.com/defaul...;283811&sd=tech
Hans

Running SQL Scripts against SQL 2005

Our developers generate a large number of SQL scripts that get applied with each application update. Is there an easy way to select one script after the other and run them against a particular database?

Right now it appears I have to open them one by one (since the window selector is useless for actually picking a script) by scanning through the directory for the next script, confirming the connection (thankfully I'm using Integrated Security), Changing the current database (since it always defaults to master) and finally run the script. And I'm ending up doing this for every script. Hoping there's a better way to do this.

Thanks,

Larry

Management studio accepts a subset of sqlcmd statements. The one you want to use is :r. This reads the contents of the file and puts it in line with the query you have.

i.e.

:r c:\sql\myfirstscript.sql

:r c:\sql\mysecondscript.sql

go

:r c:\sql\mythirdscript.sql

in this example the first 2 scripts are combined and run. If they contain GOs in then each batch will be executed as normal.

The point to note is that the :r does execute the file but reads the file into the query. So make sure each file ends in GO or you seperate your :r statements by go.

TO use sqlcmd mode click on the icon on the toolbar with a window AND a red exclamation mark. Or go to Query and select SQLCMD mode

Running SQL query in HUGE database

hi All, I am currently dealing with a number of tables each with over 40,000 records. I have these tables in Access and i'm planning to do some SQL query on them.

The problem is that due to then large table size, the query is running extremely slow and I'm not sure if using Access SQL is the most viable option.

I have something like:
there are three tables, T1 , T2 and T3.

T1: T2 T3

ID Value ID Value ID Value
1 100 2 5 3 1
2 200 3 5 4 1
3 300 4 5
4 400

My job is to add up all the corresponding values in the three tables and come out with something like this :

Results
ID Value
1 100
2 205
3 306
4 406

So you see the three tables have different number of records and If a record in T1 is not found in T2 or T3, I still want to keep the original value in T1. And for each table there are some 10,000 records!!!

Any advice on how to go about doing this? Some other alternatives I cuold think of is to copy the three tables to Excel and use formula, but in reality I have large number of such files so doing it manually is very time consuming.

Thanks!Your query would be:

select id, sum(value)
from
( select id, value from t1
union all
select id, value from t2
union all
select id, value from t3
)
group by id
order by id;

Whether this is too much data for Access to handle, I don't know. It is certainly a pretty small amount of data for a DBMS such as SQL Server or Oracle.|||thanks I just tried it and it works very well.

however I forgot to add a point that T2 and T3 might contain records that do not exist in T1 (say Id=5)
but I ONLY want records that exist in T1.

What should I do?
Thanks!|||In that case, perhaps an outer join is more appropriate?

select t1.id, t1.value+coalesce(t2.value,0)+coalesce(t3.value,0)
from t1
left outer join t2 on t2.id = t1.id
left outer join t3 on t3.id = t1.id;