Running Value with Subtotals and Grand Total

I have a Division parameter (select one or many) and I am grouping my Sales
Goal row by Division with columns for months. I can show the running year to
date total across the columns ok, but I can't figure out how to reset the
running value when I get to a new Division grouping. It is adding the second
division to the results from the first grouping which is not what I want.
How do I this?
I also want a grand total below all the groupings
TIA
DeanI have been looking for help with doing the subtotals and the grand totals of
a report and I came acrross the Running value aggregate and I am no expert
but it seems to me you need to specify the scope for your runningvalue and
that will reset it if the scope changes.
"Dean" wrote:
> I have a Division parameter (select one or many) and I am grouping my Sales
> Goal row by Division with columns for months. I can show the running year to
> date total across the columns ok, but I can't figure out how to reset the
> running value when I get to a new Division grouping. It is adding the second
> division to the results from the first grouping which is not what I want.
> How do I this?
> I also want a grand total below all the groupings
> TIA
> Dean
>
>

Running T-SQL query against MS Access database

Is it possible to run a T-SQL query against an Access Database?
Such as a query that is not syntactically correct in Access...

select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anSQLServerColumn1] = [anAccessColumn1],
[anSQLServerColumn2] = SUBSTRING([anAccessColumn2],1,1)
from [anAccessDatabaseTable]')

I have a program with large amounts of SQL code such as the above, and I do not want to have to rewrite it all in Access compliant syntax.

I can make it work only if I rewrite the above as...

select * from
OPENQUERY( anAccessLinkedServer,
'Select
[anAccessColumn1] AS [anSQLServerColumn1],
Left$([anAccessColumn2],1,1) AS [anSQLServerColumn2]
from [anAccessDatabaseTable]')

Kind Regards,
Laughton JacksonIf you are using OfficeXP (not sure about 2000 or ealier versions) you can open your database and go to the menu and click on Tools then options. Click on the Table/Queries tab and in the lower righ-hand corner check the box below the phrase "SQL Server Compatible Syntax (ANSI 92)" checkbox is marked "This database." Check the box and click OK.

This should help keep you from rewriting at least most of it.|||This option is not available in 2000.
Also, This Access dataase is being used by other legacy programs and this may affect these program's use of the database.

I was hoping this was possible by using the sp_addlinkedserver, sp_serveroption procedures.

Any other suggestions?

Thanks
Laughton Jackson|||Wish I did. Will keep you in mind and if anything comes up will post again.

Happy Hunting

Running Totals

Hi,
I have question regarding running totals, here is my SQL code so far:
SELECT Month(DateUsed) as Months,
COUNT(DateUsed) as NumUsed
FROM TempTable
GROUP BY DateUsed
What I also want in there is another column giving me the running total of
'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
obviously this wont work. I have tried using ROLLUP but that does not give me
what I want.
Here is an example of the data I want back.
Months NumUsed RunningTotal
1 14 14
7 10 24
10 44 68
Is there another way to do this?
Thanks in advance.
Kind regards,
JPlease post your DDL and sample data.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"JenC" <JenC@.discussions.microsoft.com> wrote in message
news:5B340CE7-5536-402A-8E30-6B2C14EB759C@.microsoft.com...
Hi,
I have question regarding running totals, here is my SQL code so far:
SELECT Month(DateUsed) as Months,
COUNT(DateUsed) as NumUsed
FROM TempTable
GROUP BY DateUsed
What I also want in there is another column giving me the running total of
'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
obviously this wont work. I have tried using ROLLUP but that does not give
me
what I want.
Here is an example of the data I want back.
Months NumUsed RunningTotal
1 14 14
7 10 24
10 44 68
Is there another way to do this?
Thanks in advance.
Kind regards,
J|||Something like :
SELECT Month(DateUsed) as Months,
COUNT(DateUsed) as NumUsed,
(SELECT COUNT(DateUsed)
FROM TempTable T2
WHERE T2.Month(DateUsed) <= T1.Month(DateUsed)) AS RunningTotal
FROM TempTable T1
GROUP BY DateUsed
A +
JenC a écrit :
> Hi,
> I have question regarding running totals, here is my SQL code so far:
> SELECT Month(DateUsed) as Months,
> COUNT(DateUsed) as NumUsed
> FROM TempTable
> GROUP BY DateUsed
> What I also want in there is another column giving me the running total of
> 'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
> obviously this wont work. I have tried using ROLLUP but that does not give me
> what I want.
> Here is an example of the data I want back.
> Months NumUsed RunningTotal
> 1 14 14
> 7 10 24
> 10 44 68
>
> Is there another way to do this?
> Thanks in advance.
> Kind regards,
> J
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||Hi Tom,
Just in the process of posting the required info when i read the post by
SQLPro which gets the results I want.
Thanks for your time. J
"Tom Moreau" wrote:
> Please post your DDL and sample data.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "JenC" <JenC@.discussions.microsoft.com> wrote in message
> news:5B340CE7-5536-402A-8E30-6B2C14EB759C@.microsoft.com...
> Hi,
> I have question regarding running totals, here is my SQL code so far:
> SELECT Month(DateUsed) as Months,
> COUNT(DateUsed) as NumUsed
> FROM TempTable
> GROUP BY DateUsed
> What I also want in there is another column giving me the running total of
> 'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
> obviously this wont work. I have tried using ROLLUP but that does not give
> me
> what I want.
> Here is an example of the data I want back.
> Months NumUsed RunningTotal
> 1 14 14
> 7 10 24
> 10 44 68
>
> Is there another way to do this?
> Thanks in advance.
> Kind regards,
> J
>|||Hi,
Thanks for this, works a treat. J
"SQLpro [MVP]" wrote:
> Something like :
> SELECT Month(DateUsed) as Months,
> COUNT(DateUsed) as NumUsed,
> (SELECT COUNT(DateUsed)
> FROM TempTable T2
> WHERE T2.Month(DateUsed) <= T1.Month(DateUsed)) AS RunningTotal
> FROM TempTable T1
> GROUP BY DateUsed
> A +
> JenC a écrit :
> > Hi,
> >
> > I have question regarding running totals, here is my SQL code so far:
> >
> > SELECT Month(DateUsed) as Months,
> > COUNT(DateUsed) as NumUsed
> > FROM TempTable
> > GROUP BY DateUsed
> >
> > What I also want in there is another column giving me the running total of
> > 'Count(DateUsed)'. I have tried Sum(Count(DateUsed) ) as RunningTotal,
> > obviously this wont work. I have tried using ROLLUP but that does not give me
> > what I want.
> >
> > Here is an example of the data I want back.
> >
> > Months NumUsed RunningTotal
> > 1 14 14
> > 7 10 24
> > 10 44 68
> >
> >
> > Is there another way to do this?
> >
> > Thanks in advance.
> >
> > Kind regards,
> > J
>
> --
> Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
> Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
> Audit, conseil, expertise, formation, modélisation, tuning, optimisation
> ********************* http://www.datasapiens.com ***********************
>

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 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