Adding two group sums

Hi guys,

I'm very new to Crystal reports and I've scoured the internet looking how to add two 'sums together in a field.

In group header 3 I'm trying to add the following two sums together

Sum ({WMLocnHandlingMediaConf.QtyStor}

and

Sum ({WMLocnHandlingMediaConf.QtyIn}

And I really don't have a clue on how to do, I am a novice on Crystal and this is probably the most complicated thing I've attempted to do.

If anyone can help it would be very much apperciated.

Many Thanks
NickTry inserting and configuring a Running Total Field|||Make sure the the two sums you have already created are physically on the report. (If you do not wanrt to show them just format/suppress).

Create a new formula. When looking for the two fields to sum in the formula, look into report fields. You should see the tweo previously created sums.

Adding times together

Hi guys,

I have a field in my DB called EventDate as a DateTime field,
therefore it holds both the date and time together like this:
'2004-10-14 08:42:57.000'.

I need to add together all the times in this column for a particular
date range (BETWEEN).

Any suggestions will be great.

Thanks
Sunny:)Sunny K (sunstarwu@.yahoo.com) writes:
> I have a field in my DB called EventDate as a DateTime field,
> therefore it holds both the date and time together like this:
> '2004-10-14 08:42:57.000'.
> I need to add together all the times in this column for a particular
> date range (BETWEEN).

If I take you by the word, it sounds like the answer is:

SELECT SUM(datefiff(ss, convert(char(8), EventDate, 112), EventDate)
FROM tbl
WHERE EventDate BETWEEN ... AND ...

But it looks a little funny.

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||You certainly can use BETWEEN with the DATETIME datatype but if you are
querying values with times other than midnight it's often more convenient to
use use >= and < instead of BETWEEN. For example

This:

SELECT *
FROM YourTable
WHERE eventdate >= '20041014'
AND eventdate < '20041015'

Is equivalent to this:

SELECT *
FROM YourTable
WHERE eventdate
BETWEEN '2004-10-14T00:00:00.000'
AND '2004-10-14T23:59:59.997'

Hope that answers your question.

--
David Portas
SQL Server MVP
--|||> I need to add together all the times in this column

I missed that bit from my first post - maybe because I've no idea what it
means! Just what would you expect to be the result of, for example
'2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
you want to add up a DATETIME?

--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message news:<xoudnd6FteLz-BTcRVn-1A@.giganews.com>...
> > I need to add together all the times in this column
> I missed that bit from my first post - maybe because I've no idea what it
> means! Just what would you expect to be the result of, for example
> '2004-10-14 08:42:57.000' + '2004-12-31 00:00:00.000'? Could you explain how
> you want to add up a DATETIME?

Hi Dave

Thats for the reply, and admitly i was very vague in what i meant to
say. From your above example the time result of the two times would
give me 08:42:57.000, as the time added was 00:00:00.000.

Maybe this will help explain what i mean a bit better. Here is a few
typical lines from my table:

Name EventDate EventID
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
CTWIGG-MOBL 2004-11-03 15:46:11.000 6005

My 'exact' requirements are to SUM all the 6005 EventID times together
and SUM all the 6006 EventID times together then find the difference
between the two times. The dates in the column are of no use.

Ive been banging my head over how to do this for a few days now. Any
suggestions?|||Sunny K (sunstarwu@.yahoo.com) writes:
> Thats for the reply, and admitly i was very vague in what i meant to
> say. From your above example the time result of the two times would
> give me 08:42:57.000, as the time added was 00:00:00.000.
> Maybe this will help explain what i mean a bit better. Here is a few
> typical lines from my table:
> Name EventDate EventID
> _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>
> My 'exact' requirements are to SUM all the 6005 EventID times together
> and SUM all the 6006 EventID times together then find the difference
> between the two times. The dates in the column are of no use.
> Ive been banging my head over how to do this for a few days now. Any
> suggestions?

I repeat from my previous post:

A common advice for this type of query is that you post

o CREATE TABLE statement for your table.
o INSERT statements with sample data.
o The desired result, given the sample data.

This make it easy to cut and paste and compose a tested solution.

In this case, the part with the desired result is very important,
because I am not sure what result you are looking for, and I don't
feel like guessing.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <esquel@.sommarskog.se> wrote in message news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
> Sunny K (sunstarwu@.yahoo.com) writes:
> > Thats for the reply, and admitly i was very vague in what i meant to
> > say. From your above example the time result of the two times would
> > give me 08:42:57.000, as the time added was 00:00:00.000.
> > Maybe this will help explain what i mean a bit better. Here is a few
> > typical lines from my table:
> > Name EventDate EventID
> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
> > My 'exact' requirements are to SUM all the 6005 EventID times together
> > and SUM all the 6006 EventID times together then find the difference
> > between the two times. The dates in the column are of no use.
> > Ive been banging my head over how to do this for a few days now. Any
> > suggestions?
> I repeat from my previous post:
> A common advice for this type of query is that you post
> o CREATE TABLE statement for your table.
> o INSERT statements with sample data.
> o The desired result, given the sample data.
> This make it easy to cut and paste and compose a tested solution.
> In this case, the part with the desired result is very important,
> because I am not sure what result you are looking for, and I don't
> feel like guessing.

Hi,

Here is the script to create the table with some sample data:

CREATE TABLE [dbo].[tbltemp23] (
[Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[EventDate] [datetime] NOT NULL ,
[EventID] [int] NOT NULL
) ON [PRIMARY]
GO

INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)

Now I need to work out the the total time of all the 6006 EventIDs
(the date is here is not needed) which should equal: 93:32:16 then the
the total time of all the 6005 EventIDs which equals: 93:42:44. Then
finally find the difference between the two times, which should equal:
00:10:28 in this case.

I hope this is enough information.

Thanks
Sunny|||Thanks for the DDL and data.

SQL Server doesn't have a timespan data type. The query below uses
1900-01-01 as the base date from which durations are calculated, ignoring
the date component of the table data. You can format the returned values
according to your reporting requirements.

SELECT
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006) AS EventId6006Duration,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005) AS EventId6005Duration,
DATEADD(s,
DATEDIFF(s,
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6006),
(SELECT
DATEADD(s,
SUM(DATEDIFF(s,
'19000101', CAST(CONVERT(varchar(12), EventDate, 114) AS datetime))),
'19000101')
FROM tbltemp23
WHERE EventId = 6005)),
'19000101'
) AS EventDurationDifference

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Sunny K" <sunstarwu@.yahoo.com> wrote in message
news:1ecdad8f.0411080132.4d6627fe@.posting.google.c om...
> Erland Sommarskog <esquel@.sommarskog.se> wrote in message
> news:<Xns9597F073739C9Yazorman@.127.0.0.1>...
>> Sunny K (sunstarwu@.yahoo.com) writes:
>> > Thats for the reply, and admitly i was very vague in what i meant to
>> > say. From your above example the time result of the two times would
>> > give me 08:42:57.000, as the time added was 00:00:00.000.
>>> > Maybe this will help explain what i mean a bit better. Here is a few
>> > typical lines from my table:
>>> > Name EventDate EventID
>> > _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
>> > CTWIGG-MOBL 2004-11-03 09:13:21.000 6006
>> > CTWIGG-MOBL 2004-11-03 09:14:42.000 6005
>> > CTWIGG-MOBL 2004-11-03 15:44:55.000 6006
>> > CTWIGG-MOBL 2004-11-03 15:46:11.000 6005
>>>> > My 'exact' requirements are to SUM all the 6005 EventID times together
>> > and SUM all the 6006 EventID times together then find the difference
>> > between the two times. The dates in the column are of no use.
>>> > Ive been banging my head over how to do this for a few days now. Any
>> > suggestions?
>>
>> I repeat from my previous post:
>>
>> A common advice for this type of query is that you post
>>
>> o CREATE TABLE statement for your table.
>> o INSERT statements with sample data.
>> o The desired result, given the sample data.
>>
>> This make it easy to cut and paste and compose a tested solution.
>>
>> In this case, the part with the desired result is very important,
>> because I am not sure what result you are looking for, and I don't
>> feel like guessing.
>
> Hi,
> Here is the script to create the table with some sample data:
>
> CREATE TABLE [dbo].[tbltemp23] (
> [Machine_Name] [char] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [EventDate] [datetime] NOT NULL ,
> [EventID] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:10:54.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-11 09:12:13.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:41:42.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-14 08:42:57.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:16:45.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-10-18 16:19:21.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:32:56.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-02 16:34:17.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:13:21.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 09:14:42.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:44:55.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-03 15:46:11.000',6005)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:51:43.000',6006)
> INSERT INTO tbltemp23 VALUES ('MOBL','2004-11-04 17:53:03.000',6005)
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.
> I hope this is enough information.
> Thanks
> Sunny|||Sunny K (sunstarwu@.yahoo.com) writes:
> Now I need to work out the the total time of all the 6006 EventIDs
> (the date is here is not needed) which should equal: 93:32:16 then the
> the total time of all the 6005 EventIDs which equals: 93:42:44. Then
> finally find the difference between the two times, which should equal:
> 00:10:28 in this case.

To be honest, this still seem very strange to me. Sure, there is enough
information to write a solution, but somehow I wonder what is the real
problem.

Looking at your data, it seems that event 6006 means start and 6005
means end, and what you really are computing is the total duration of
all start-stop sequences. Given that, I wrote this query:

SELECT convert(char(8), dateadd(ss, SUM(diff), '19000101'), 108)
FROM (select diff = datediff(ss, a.EventDate,
(SELECT MIN(EventDate)
FROM tbltemp23 b
WHERE b.EventDate > a.EventDate
AND b.EventID = 6005))
FROM tbltemp23 a
WHERE a.EventID = 6006) AS c

Of course, this query breaks down if the 6006 and 6005 can come in
any order, but in that case I have no clue of what might be going on.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What can I say you guys, you really know your stuff. Thanks for all
the help, its given me the exact results I've needed.

I will consider you guys when I face another problem:-P

Sunny

adding subreports to master report at runtime

Hi guys,

Is it possible at runtime to decide what subreports you want in your master report. Is this possible in RS?. Many Thanks in advance.

It is possible to add or remove remove reports from reporting services at runtime. Though you can have all of them in your main report and controls its visibility using expressions.

Shyam

|||Many thanks for your response, but is there a way of dynamically adding multiple subreports to a master report without placing them all in a master and controlling visibility?|||

There was a typo in my earlier message. I actually meant that it is NOT possible to add or remove subreports from a main report at runtime. I'm afraid I may have to repeat it now.

Shyam

|||

Ok thanks, setting the visibilty does work, and is a current solution to the problem for now. Maybe the next version can deal with this. Many thanks for your response.

Kenny

|||Can you please mark this post as answered? Just click on Mark as Answer buttonsql

adding sql server CAL on cluster

Hi Guys,
i am using sql server 2000 on Windows 2000 cluster with
150 concurrent license . now i want to add 50 more licence.
do i have to add this for both nodes?
what is the procedure to do this ? is it using licensing
manager in control panel?
PLS advice meReplied in the other thread.
David Portas
SQL Server MVP
--

adding sql server CAL on cluster

Hi Guys,
i am using sql server 2000 on windows 2000 cluster with
150 concurrent license . now i want to add 50 more licence.
do i have to add this for both nodes?
what is the procedure to do this ? is it using licensing
manager in control panel?
PLS advice me
Replied in the other thread.
David Portas
SQL Server MVP

Adding records

Hello guys,

I hope someone can help me!

I have a (probably simple) problem with compact edition, I can't seem to add records using VB 6.0. Here's some sample code I've been testing with:

Dim pConn As ADODB.Connection
Dim pRS As ADODB.Recordset

Set pConn = New ADODB.Connection
Set pRS = New ADODB.Recordset

pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\test.sdf"
'pConn.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb"
pConn.Open

pConn.Execute "INSERT INTO tblActions ([Branch ID], [Computer ID], [User ID], [Program ID], [Type ID], ID, [Extra 1], [Extra 2]) " & _
"VALUES (1, 1, 1, 1, 1, 1, 'some text', 'more text')"

' Open the recordset
pRS.Open "tblActions", pConn, adOpenForwardOnly, adLockOptimistic

pRS.AddNew
pRS![Branch ID] = 1
pRS![Computer ID] = 1
pRS![User ID] = 1
pRS![Program ID] = 1
pRS![Type ID] = 1
pRS![ID] = 1
pRS![Extra 1] = 1
pRS![Extra 2] = 1
pRS.Update

pRS.Close
pConn.Close

Set pRS = Nothing
Set pConn = Nothing

I can add the records using an SQL statement, but when I try to open the recordset and add a record using the AddNew method it fails with the message:

The command contained one or more errors. [,,,,,]

But, if I connect to an Access database (which uses exactly the same tables, etc.) using the commented out connection string in the above sample, the code works fine. What am I doing wrong?

Many thanks in advance!

I think you will have to rewrite your code to use a SqlCeResultSet instead of a ADODB.RecordSet if you want to use funtionality similar to .AddNew. For a example using using SqlCeResultSet.CreateRecord and using the SqlCeUpdateableRecord, see http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceresultset.createrecord.aspx

|||

Thanks for the response, but the code you've linked to shows VB.net code, and I need to use VB 6.0. Surely there must be some way to create records in a similar way to using the .AddNew method and an SQL CE database.

My problem is that the code needs to be able to link to either an SQL CE database or an Access database, and must be written in VB 6.0.

|||Sorry, didn't think about the VB 6 issue. I think the only way forward will be to use pConn.Execute with an INSERT statement. Where does the error occur - when calling .AddNew or .Update ?|||

Neither unfortunately, it fails when opening the recordset.

I can open it fine for reading, but as soon as I add the adLockOptimistic parameter, it fails, I've tried all lock types too just in case.

|||

I think the only way forward will be to use pConn.Execute with an INSERT statement.

|||Thanks again Erik, I guess I'll have to struggle through with that for now.

Adding 'Other' segment/slice in a pie chart

Hi guys,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality in the report layout level with out writing a complex MDX query? If this is not possible, can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be easily implemented using a third pary software such as 'Dundas chart for Reporting Service'. However, my client don't want to buy this third party software.

Please let me know if anybody has came accross with similar scenario?

Sincerely,

--Amde

Please help!!!!!!!!!!

|||

Do you have to use MDX or can you use T-SQL? I once used a derived table to get this type of data. Not pretty and not the quickest thing if you have huge datasets, but it works.

SELECT grouper, sum(total_charge), sum(pct)
FROM (

select
dx1_num "Diag", -- Item to list in pie slice
sum(charge_amount) "total_charge", --
(sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) "pct", -- Percent of Everything
case
when (sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) < .05 then 'Misc' -- Interim Group
else CAST(dx1_num AS VARCHAR(15))
end "grouper" -- what kind of name do you want for free?
from ar_billtrans_charge
group by dx1_num

) Y GROUP BY grouper;

I am sure some T-SQL gods out there can do much better.

R

|||

Hi,

Appreciate your response. Basically, I am using MDX query. Do you have any idea how to do the same thing using MDX?

Thank you for your cooperation.

--Amde

|||

Please read my response with a sample report in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

Adding 'Other' segment/slice in a pie chart

Hi guys,

I am creating a pie chart report from a cube. This report may contain unknown number of segments. Here is the thing; if more than 1 data slice is generated with a value less than 5% of the total, then a segment labelled 'other' will be generated, and data from all slices with value < 5% will be added to this 'Other' segment.

Is it possible to implement this functionality in the report layout level with out writing a complex MDX query? If this is not possible, can anybody give me a sample MDX query which implements similar issue(i.e. 'Other-ing' rule.)

For your information, this feature can be easily implemented using a third pary software such as 'Dundas chart for Reporting Service'. However, my client don't want to buy this third party software.

Please let me know if anybody has came accross with similar scenario?

Sincerely,

--Amde

Please help!!!!!!!!!!

|||

Do you have to use MDX or can you use T-SQL? I once used a derived table to get this type of data. Not pretty and not the quickest thing if you have huge datasets, but it works.

SELECT grouper, sum(total_charge), sum(pct)
FROM (

select
dx1_num "Diag", -- Item to list in pie slice
sum(charge_amount) "total_charge", --
(sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) "pct", -- Percent of Everything
case
when (sum(charge_amount)/(SELECT SUM(charge_amount) FROM ar_billtrans_charge)) < .05 then 'Misc' -- Interim Group
else CAST(dx1_num AS VARCHAR(15))
end "grouper" -- what kind of name do you want for free?
from ar_billtrans_charge
group by dx1_num

) Y GROUP BY grouper;

I am sure some T-SQL gods out there can do much better.

R

|||

Hi,

Appreciate your response. Basically, I am using MDX query. Do you have any idea how to do the same thing using MDX?

Thank you for your cooperation.

--Amde

|||

Please read my response with a sample report in this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=638700&SiteID=1&mode=1

-- Robert

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!
When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
__________________________________________________ ________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>
|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________ ________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TE
ST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=
TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:

> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> ________________________________________
__________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
>
>

Adding Linked server

Hi guys,
My linked server (from one SQL Server 2000 to another SQL Server 2000) is
not working. My settings are as follows:
Checked other data source
Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
Under Security tab:
Be made using the logins current security context is checked.
Please help!When you say it's not working , do you mean "insufficient permissions!? or
are you getting some othe rmessage?
--
Jack Vamvas
__________________________________________________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"David" <David@.discussions.microsoft.com> wrote in message
news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> Hi guys,
> My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> not working. My settings are as follows:
> Checked other data source
> Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> Under Security tab:
> Be made using the logins current security context is checked.
>
> Please help!
>|||It says, SQL Server does not exist or Access denied. Thanks
"Jack Vamvas" wrote:
> When you say it's not working , do you mean "insufficient permissions!? or
> are you getting some othe rmessage?
> --
> Jack Vamvas
> __________________________________________________________________
> Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
> SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
> New article by Jack Vamvas - SQL and Markov Chains -
> www.ciquery.com/articles/art_04.asp
> "David" <David@.discussions.microsoft.com> wrote in message
> news:20B29608-CAEF-483A-ADF4-A5DA13B87D5D@.microsoft.com...
> > Hi guys,
> >
> > My linked server (from one SQL Server 2000 to another SQL Server 2000) is
> > not working. My settings are as follows:
> >
> > Checked other data source
> > Provider string: DRIVER={SQL Server};SERVER=SERVER_NAME;UID=TEST;PWD=TEST;
> >
> > Under Security tab:
> > Be made using the logins current security context is checked.
> >
> >
> > Please help!
> >
>
>

adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:

> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
index
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very
good idea of what's happening, especially which processes are involved.
I think you will find that you are getting deadlocks on only one table. Ther
e are a couple of causes of this that I've had to deal with.
1. You have a trigger on the table. The trigger joins the INSERTED or DELETE
D tables onto itself. This trigger query can result in a Share Table lock. S
o you have two process, both aquire a row level eXclusive lock. They then de
adlock each other attemptin
g to get the Share Table lock. This is an escalation deadlock. This type of
problem foxed my company for 3 years until SQL Server 7.0 can in with the De
adlock and Deadlock Chain events to track it down. It then took 2 hours to f
ind and fix.
2. Two query are both using the same table, and locking a lot of rows. But o
nly one is using an index, or they are using different indexes. The rows are
locked in the order given by the indexes. The rows are then locked out of s
equence. In your case, it s
ounds as if the purge is still scanning the table, so locking all rows.
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ

adding index NETS "Deadlock"

Can you guys help me understand the logic here.
have a very small jobQueue table
JobId
StartTime
EndTime
JobTypeId
ParentJobId
a few other trivial columns
table gets smacked all day long (thousands and thousands of times)
one stored procedure (spGetNextJob) is doing table scans based on no index
placed on StartTime. Taking 1,000+ ms to execute which is clearly
unacceptable.
I add a nonclustered index on StartTime and get Deadlocks in Production.
My confusion is that Indexes dont cause deadlocks, accessing objects in
inconsistent order causes deadlocks....
HOW, can adding an index on the table create a deadlock scenario.
(Table is constantly truncated, so never really has more than a couple
hundred records)
Greg Jackson
PDX, OregonJaxon wrote:
> My confusion is that Indexes dont cause deadlocks, accessing objects
> in inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
Try enabling trace flag T1204. When this flag is enabled, the server will
print out detailed information about any deadlocks.
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Add a RowID BigINT Identity(1,1) to the beginning of the table.
Clustered UNIQUE index on RowID.
Noncluster index on Startime
Write stored procedures to do inserts, updates, and deletes.
Make developers use those procedures ONLY for accessing the table.
Watch the system run very fast.
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> Can you guys help me understand the logic here.
> have a very small jobQueue table
> JobId
> StartTime
> EndTime
> JobTypeId
> ParentJobId
> a few other trivial columns
> table gets smacked all day long (thousands and thousands of times)
> one stored procedure (spGetNextJob) is doing table scans based on no index
> placed on StartTime. Taking 1,000+ ms to execute which is clearly
> unacceptable.
> I add a nonclustered index on StartTime and get Deadlocks in Production.
> My confusion is that Indexes dont cause deadlocks, accessing objects in
> inconsistent order causes deadlocks....
> HOW, can adding an index on the table create a deadlock scenario.
> (Table is constantly truncated, so never really has more than a couple
> hundred records)
>
> Greg Jackson
> PDX, Oregon
>|||From Geoff's post, If you will not have that many rows, you might get away
with using an INT data type rather than a bigint
--
Ray Higdon MCSE, MCDBA, CCNA
--
"Geoff N. Hiten" <SRDBA@.Careerbuilder.com> wrote in message
news:%23z$JyxuAEHA.2480@.TK2MSFTNGP11.phx.gbl...
> Add a RowID BigINT Identity(1,1) to the beginning of the table.
> Clustered UNIQUE index on RowID.
> Noncluster index on Startime
> Write stored procedures to do inserts, updates, and deletes.
> Make developers use those procedures ONLY for accessing the table.
> Watch the system run very fast.
> --
> Geoff N. Hiten
> Microsoft SQL Server MVP
> Senior Database Administrator
> Careerbuilder.com
> I support the Professional Association for SQL Server
> www.sqlpass.org
> "Jaxon" <GregoryAJacksonN0SPAM@.hotmail.com> wrote in message
> news:uDBxDftAEHA.712@.tk2msftngp13.phx.gbl...
> > Can you guys help me understand the logic here.
> >
> > have a very small jobQueue table
> >
> > JobId
> > StartTime
> > EndTime
> > JobTypeId
> > ParentJobId
> > a few other trivial columns
> >
> > table gets smacked all day long (thousands and thousands of times)
> >
> > one stored procedure (spGetNextJob) is doing table scans based on no
index
> > placed on StartTime. Taking 1,000+ ms to execute which is clearly
> > unacceptable.
> >
> > I add a nonclustered index on StartTime and get Deadlocks in Production.
> >
> > My confusion is that Indexes dont cause deadlocks, accessing objects in
> > inconsistent order causes deadlocks....
> >
> > HOW, can adding an index on the table create a deadlock scenario.
> >
> > (Table is constantly truncated, so never really has more than a couple
> > hundred records)
> >
> >
> > Greg Jackson
> > PDX, Oregon
> >
> >
>|||SQL Profiler has Deadlock and Deadlock Chain events.
Couple this with looking at the statements being issued, you can get a very good idea of what's happening, especially which processes are involved
I think you will find that you are getting deadlocks on only one table. There are a couple of causes of this that I've had to deal with
1. You have a trigger on the table. The trigger joins the INSERTED or DELETED tables onto itself. This trigger query can result in a Share Table lock. So you have two process, both aquire a row level eXclusive lock. They then deadlock each other attempting to get the Share Table lock. This is an escalation deadlock. This type of problem foxed my company for 3 years until SQL Server 7.0 can in with the Deadlock and Deadlock Chain events to track it down. It then took 2 hours to find and fix
2. Two query are both using the same table, and locking a lot of rows. But only one is using an index, or they are using different indexes. The rows are locked in the order given by the indexes. The rows are then locked out of sequence. In your case, it sounds as if the purge is still scanning the table, so locking all rows
I hope this helps|||currently, the PKEY and Clustered Index are on JobId Which is a FREAKING
GUID (not my choice).
Currently there is no index on StartTime but when I ADD a nonclustered
there, I get deadlocks.
I've already analyzed the crap out of the deadlock situation.
deadlock is on the job table and is ocurring due to the two sprocs listed
above.
the sprocs SUCK (use cursors, etc etc etc) I'll fix them later.
I just dont understand how adding a nonclustered to the timeStart Column
results in Deadlocks and removing the index alleviates them.
Sure, I will fix the sprocs later (Like a 3 week task including QA, etc)
Could it just be that with the index added, it makes inserts that much
slower such that some transaction is being heald open a little longer
resulting in deadlock ?
In other words, is the index just the straw that is breaking my camels back
?
this entire job queue crap is an architectural nightmare.....the rewrite is
coming in the near future, believe me.
GAJ|||sorry, I didnt mention the sprocs...
the culprits are "spCheckJob" and "spGetNextJob"
they are both quite ugly you dont want me to send the definitions for these
believe me.
GAJ

Adding Grand Total to a Column Group in a Matrix. Please Help!

Hello Guys,

I am working on a matrix report which has several row groups and 1 column group. After execution, the column group wil end up with several columns containg numeric counts. I would like to have the grand total for each "column group" column as a last row on this report.

For row groups you can just right click "Subtotal", but that is not possible for column group. Could someone please help me to find a clever way of accomplishing this, please. Thank you so much for your help!

For column groups, you can also just right click "Subtotal". Maybe you are clicking the wrong box or it isn't truly a column group or you just aren't seeing it.

Look around a bit more.

http://i55.photobucket.com/albums/g121/Farsight38/untitled-1.jpg

|||

You're right, however that will just give me totals on the right side of the matrix (row totals). What I would like to have is a grand total column(s) at the bottom of the matrix (basically a grand total for all values in each of the "column grouping" columns). Is there a way to accomplish that? Thank you for your expertise.

|||

I'm not sure I understand what you're asking for.

In order to get row totals on the right side of the matrix, you right click a column and select subtotal.

In order to get column totals at the bottom of the matrix, you right click a row and select subtotal.

|||

You should be able to achieve this is you add a subtotal on the outermost (leftmost) row group.

The reason you sometimes cannot add a subtotal is to do with static groups which don't support subtotals. These are created when you drag more than one column (measure) from your dataset to the details portion of the matrix (rows or columns).

It would help if you put together some sample data and what you would like to achieve as output. Do this in Excel and copy paste it into a post. That way we'll be able to help more affectively.

|||

Adam, you're the greatest!

Adding a subtotal to the leftmost row group did the trick. Thank you so much.

??€?§Q? , matrix wouldn't let me add the subtotal to the bottom right cell (Data), where I needed the totals to show. Adam's suggestion worked. I thought doing that would just add the total for the values of that rowgroup - but now I know better

Thank you for your help, guys.

Adding Domain Accounts to Databases

Hi guys
I have a user that is in the process of being migrated from one domain to
another. Therefore I have been asked to add his new domain account to the SQL
Server 2000.
However when I try to Add the user Domain2\user1 it fails saying 'user1'
already exist. Which is true as Domain1\User1.
Question is: Is there a way to add user1 from Domain2 without removing the
Domain1 user?
Thanks.
Regards
JonasJonas
Lookup sp_change_users_login in the BOL.
"Jonas Larsen" <JonasLarsen@.discussions.microsoft.com> wrote in message
news:A913CF93-A288-45E0-8653-D085F2FB68B9@.microsoft.com...
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the
> SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas|||Jonas Larsen wrote:
> Hi guys
> I have a user that is in the process of being migrated from one domain to
> another. Therefore I have been asked to add his new domain account to the SQL
> Server 2000.
> However when I try to Add the user Domain2\user1 it fails saying 'user1'
> already exist. Which is true as Domain1\User1.
> Question is: Is there a way to add user1 from Domain2 without removing the
> Domain1 user?
> Thanks.
> Regards
> Jonas
Hi Jonas
You could also create a group in the new domain and then give this group
the required access to the database. You can then put the new user
account into this group. That should give you what you want.
Regards
Steen

adding disk to existing clustered sql 2000 instance

hi guys
i had 2 sql instances in win2003. I uninstalled one instance. How do i add
the disks to the existing/active instance?
TIAHi
Move the disks to the SQL Server group you want to add them to. Take the
group offline, create a dependency for SQL Server on the new disks and bring
the group online again.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"rupart" wrote:
> hi guys
> i had 2 sql instances in win2003. I uninstalled one instance. How do i add
> the disks to the existing/active instance?
> TIA|||hi Mike,
i tried to create the resource(physical disk) but couldnt see the disks
under the list...
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Move the disks to the SQL Server group you want to add them to. Take the
> group offline, create a dependency for SQL Server on the new disks and bring
> the group online again.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "rupart" wrote:
> > hi guys
> > i had 2 sql instances in win2003. I uninstalled one instance. How do i add
> > the disks to the existing/active instance?
> >
> > TIA

adding column vlaues with one another

Hi Guys,
I have a table like this
Groupid Salary
B01 1000
B01 2000
B01 3000
B02 2000
B02 2000
B02 1000
I wish to show like this
Groupid Salary Sum1
B01 1000 1000
B01 2000 3000
B01 3000 5000
Sum
6000
B02 2000 2000
B02 2000 4000
B02 1000 5000
Sum
5000
Plz Help me ASAPHi,
Create Table #TestTable
(
ID INT,
Value INT
)
INSERt INTO #TestTable
VALUEs (1,100)
INSERt INTO #TestTable
VALUEs (1,100)
INSERt INTO #TestTable
VALUEs (3,100)
INSERt INTO #TestTable
VALUEs (2,100)
INSERt INTO #TestTable
VALUEs (2,100)
INSERt INTO #TestTable
VALUEs (2,100)
Select * from #TestTable
order by [ID]
Compute SUM(Value) by [ID]
Drop TABLE #TestTable
HTH, jens Suessmeyer.|||Manish Sukhija wrote:

> I have a table like this
> Groupid Salary
> B01 1000
> B01 2000
> B01 3000
> B02 2000
> B02 2000
> B02 1000
> I wish to show like this
> Groupid Salary Sum1
> B01 1000 1000
> B01 2000 3000
> B01 3000 5000
> Sum
> 6000
> B02 2000 2000
> B02 2000 4000
> B02 1000 5000
> Sum
> 5000
> Plz Help me ASAP
This is something you need to do in your application, not really
something for the database. Most report generators support this.
HTH,
Stijn Verrept.|||Manish
Why not doing such reports on the client side?
CREATE TABLE #Test
(
PK INT NOT NULL PRIMARY KEY,
Groupid CHAR(3) NOT NULL,
Salary INT NOT NULL
)
INSERT INTO #Test VALUES (1,'B01',1000)
INSERT INTO #Test VALUES (2,'B01',2000)
INSERT INTO #Test VALUES (3,'B01',3000)
INSERT INTO #Test VALUES (4,'B02',2000)
INSERT INTO #Test VALUES (5,'B02',2000)
INSERT INTO #Test VALUES (6,'B02',1000)
SELECT Groupid,Salary,(SELECT SUM(Salary) FROM #Test T WHERE T.PK<=#Test.PK
AND T.Groupid='B01') Summary
FROM #Test WHERE Groupid='B01'
COMPUTE SUM(Salary)
SELECT Groupid,Salary,(SELECT SUM(Salary) FROM #Test T WHERE T.PK<=#Test.PK
AND T.Groupid='B02') Summary
FROM #Test WHERE Groupid='B02'
COMPUTE SUM(Salary)
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:B0840D6C-F7E8-4C88-8822-167AC0A83C8B@.microsoft.com...
> Hi Guys,
> I have a table like this
> Groupid Salary
> B01 1000
> B01 2000
> B01 3000
> B02 2000
> B02 2000
> B02 1000
> I wish to show like this
> Groupid Salary Sum1
> B01 1000 1000
> B01 2000 3000
> B01 3000 5000
> Sum
> 6000
> B02 2000 2000
> B02 2000 4000
> B02 1000 5000
> Sum
> 5000
> Plz Help me ASAP
>|||Manish Sukhija wrote:
> Hi Guys,
> I have a table like this
> Groupid Salary
> B01 1000
> B01 2000
> B01 3000
> B02 2000
> B02 2000
> B02 1000
> I wish to show like this
> Groupid Salary Sum1
> B01 1000 1000
> B01 2000 3000
> B01 3000 5000
> Sum
> 6000
> B02 2000 2000
> B02 2000 4000
> B02 1000 5000
> Sum
> 5000
> Plz Help me ASAP
Judging by your required output, Sum1 is supposed to be a rolling
total. What determines the order in which the rows are to be added?
What are the key(s) your table? Your sketch of your table apparently
doesn't have a key at all! Please include DDL so that we don't have to
guess.
Let's assume you have a table that looks like this:
CREATE TABLE your_table (groupid CHAR(3) NOT NULL, dt DATETIME NOT
NULL, salary NUMERIC(10,2) NOT NULL, PRIMARY KEY (groupid, dt)) ;
Note the extra column and the key. Now try:
SELECT groupid, dt,
(SELECT SUM(salary)
FROM your_table
WHERE groupid = T.groupid
AND dt <= T.dt)
FROM your_table AS T
ORDER BY groupid, dt ;
You also have some extra totals in your output. You can try the
following, or take a look at the ROLLUP feature in Books Online.
SELECT groupid, dt,
(SELECT SUM(salary)
FROM your_table
WHERE groupid = T.groupid
AND dt <= T.dt)
FROM your_table AS T
UNION
SELECT groupid, '9999-12-31', SUM(salary)
FROM your_table
GROUP BY groupid
ORDER BY groupid, dt ;
David Portas
SQL Server MVP
--

adding CAL for sql server on cluster

Hi Guys,
i am using sql server 2000 on windows 2000 cluster with
150 concurrent license . now i want to add 50 more licence.
do i have to add this for both nodes?
what is the procedure to do this ? is it using licensing
manager in control panel?
PLS advice me
> 150 concurrent license . now i want to add 50 more licence.
SQL CALs are not "concurrent" licences. You need a CAL per device or user
regardless of how many connections are made concurrently. Under the
Server/CAL licensing model you only need one licence per device or user even
if they connect to multiple servers, whether clustered or otherwise.
Refer to:
http://www.microsoft.com/sql/howtobuy/default.asp
David Portas
SQL Server MVP
|||Hi,
so even if i have 10 connections from one work stations it
will consider as only one CAL.?
but it in sql 7.0 it was concurrent license
[vbcol=seagreen]
>--Original Message--
licence.
>SQL CALs are not "concurrent" licences. You need a CAL
per device or user
>regardless of how many connections are made concurrently.
Under the
>Server/CAL licensing model you only need one licence per
device or user even
>if they connect to multiple servers, whether clustered or
otherwise.
>Refer to:
>http://www.microsoft.com/sql/howtobuy/default.asp
>--
>David Portas
>SQL Server MVP
>--
>
>.
>
|||> so even if i have 10 connections from one work stations it
> will consider as only one CAL.?
Yes. Conversely if you have 100 workstations you need 100 device CALs even
if no more than 50 are ever connected concurrently.

> but it in sql 7.0 it was concurrent license
From the Licensing FAQ:
Q. Do you still offer per-server (concurrency) CALs?
A. No. SQL Server 2000 is only available by means of a Server plus device
CAL, Server plus user CAL, or a Processor license.
David Portas
SQL Server MVP

adding CAL for sql server on cluster

Hi Guys,
i am using sql server 2000 on Windows 2000 cluster with
150 concurrent license . now i want to add 50 more licence.
do i have to add this for both nodes?
what is the procedure to do this ? is it using licensing
manager in control panel?
PLS advice me> 150 concurrent license . now i want to add 50 more licence.
SQL CALs are not "concurrent" licences. You need a CAL per device or user
regardless of how many connections are made concurrently. Under the
Server/CAL licensing model you only need one licence per device or user even
if they connect to multiple servers, whether clustered or otherwise.
Refer to:
http://www.microsoft.com/sql/howtobuy/default.asp
David Portas
SQL Server MVP
--|||Hi,
so even if i have 10 connections from one work stations it
will consider as only one CAL.?
but it in sql 7.0 it was concurrent license

>--Original Message--
licence.[vbcol=seagreen]
>SQL CALs are not "concurrent" licences. You need a CAL
per device or user
>regardless of how many connections are made concurrently.
Under the
>Server/CAL licensing model you only need one licence per
device or user even
>if they connect to multiple servers, whether clustered or
otherwise.
>Refer to:
>http://www.microsoft.com/sql/howtobuy/default.asp
>--
>David Portas
>SQL Server MVP
>--
>
>.
>|||> so even if i have 10 connections from one work stations it
> will consider as only one CAL.?
Yes. Conversely if you have 100 workstations you need 100 device CALs even
if no more than 50 are ever connected concurrently.

> but it in sql 7.0 it was concurrent license
From the Licensing FAQ:
Q. Do you still offer per-server (concurrency) CALs?
A. No. SQL Server 2000 is only available by means of a Server plus device
CAL, Server plus user CAL, or a Processor license.
David Portas
SQL Server MVP
--

Adding a user to a Read-Only database

Hey guys,

I currently have a sql server 2000 sp4 instance with a read-only db on it. We get tlogs shipped from an outside vendor and keep a copy of the production database in house for reporting purposes. The database is in read-only mode because we apply new tlogs daily. Recently we did a full recover and applied 2 tlogs.

The issue:
There is a user YYY in the database and at the server level, but they aren't linked. So I do what I usually do and run the following command:

exec sp_change_users_login 'Update_One', 'YYY','YYY'

Except for I get the following error:

Server: Msg 3906, Level 16, State 1, Procedure sp_change_users_login, Line 109
Could not run BEGIN TRANSACTION in database 'ZZZ' because the database is read-only.

Now this error makes sense and I understand why, so my question is how do I fix the users not being linked?Easiest would be to drop the login on your server, and re-create it with the SID of the user in the database. Alternatively, you can try to have the vendor drop the user and login on their end, and recreate it with your login's SID. The new SID will be brought to your server with the appropriate TLog restore.