A Bug With Running Total Query?

Using SQL Server 2005
Query Result From 1 Table with 3 Cols:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
7/1/2005 415.0000 830.0000
8/1/2005 415.0000 1245.0000
9/1/2005 415.0000 1660.0000
10/1/2005 415.0000 2075.0000
11/1/2005 415.0000 2490.0000
12/1/2005 415.0000 2905.0000
1/1/2006 415.0000 3320.0000
2/1/2006 415.0000 3735.0000
3/1/2006 415.0000 4150.0000
4/1/2006 415.0000 4565.0000
5/1/2006 415.0000 4980.0000
Query:
SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
RunningTotal
FROM AmountTable AS Table1 INNER JOIN
AmountTable AS Table2 ON Table1.AmountDate >=
Table2.AmountDate
GROUP BY Table1.AmountDate, Table1.Amount
ORDER BY Table1.AmountDate
The problem is with the 7/1 date sum. It only added 100 and not 415
Does anybody see a problem I did or is this something I should post to MSDN
Product FeedBack?
The query was a bit more complex when I unioned 2 tables and the amount was
not coming out right and I broke it down very simply and found this to be a
n
issue.
On a side note relating to the union relation and doing a running total, I
only had 1 Amount in the other table for now and for some reason, that wasn'
t
summing with the rest of the set.
SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount
INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
CROSS JOIN
(SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
FROM Table1Amount INNER JOIN
Table1 ON Table1Amount.Table1ID = Table1.Table1ID
UNION
SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
FROM Table2Amount INNER JOIN
Table1 AS Table1_1 ON Table2Amount.Table2ID =
Table1_1.Table2ID
WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
ORDER BY AmountTable1.AmountDate
Produces:
Date Amount RunningTotal
6/1/2005 730.0000 730.0000
6/3/2005 10.0000 20.0000*****From Table1
7/1/2005 415.0000 1245.0000
8/1/2005 415.0000 1660.0000
9/1/2005 415.0000 2075.0000
10/1/2005 415.0000 2490.0000
11/1/2005 415.0000 2905.0000
12/1/2005 415.0000 3320.0000
1/1/2006 415.0000 3735.0000
2/1/2006 415.0000 4150.0000
3/1/2006 415.0000 4565.0000
4/1/2006 415.0000 4980.0000
5/1/2006 415.0000 5395.0000
6/1/2006 415.0000 5810.0000
So that 6/3 Amount from Table1 is excluded from summing with the rest of the
set.
I thought it would have been included with the Union.
Any input is welcome,
NathanHi
This should be posted to the beta newsgroups along with version number, DDL
and sample data so that the problem can be recreated.
http://communities.microsoft.com/ne...onalserver.tsql
John
"Nathan" <Nathan@.discussions.microsoft.com> wrote in message
news:C771A09A-69DA-49D0-B71C-5C541E15EC51@.microsoft.com...
> Using SQL Server 2005
> Query Result From 1 Table with 3 Cols:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 7/1/2005 415.0000 830.0000
> 8/1/2005 415.0000 1245.0000
> 9/1/2005 415.0000 1660.0000
> 10/1/2005 415.0000 2075.0000
> 11/1/2005 415.0000 2490.0000
> 12/1/2005 415.0000 2905.0000
> 1/1/2006 415.0000 3320.0000
> 2/1/2006 415.0000 3735.0000
> 3/1/2006 415.0000 4150.0000
> 4/1/2006 415.0000 4565.0000
> 5/1/2006 415.0000 4980.0000
> Query:
> SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
> RunningTotal
> FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
> Table2.AmountDate
> GROUP BY Table1.AmountDate, Table1.Amount
> ORDER BY Table1.AmountDate
> The problem is with the 7/1 date sum. It only added 100 and not 415
> Does anybody see a problem I did or is this something I should post to
> MSDN
> Product FeedBack?
> The query was a bit more complex when I unioned 2 tables and the amount
> was
> not coming out right and I broke it down very simply and found this to be
> an
> issue.
> On a side note relating to the union relation and doing a running total, I
> only had 1 Amount in the other table for now and for some reason, that
> wasn't
> summing with the rest of the set.
> SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
> FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate,
> Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
> Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
> WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
> GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
> ORDER BY AmountTable1.AmountDate
> Produces:
> Date Amount RunningTotal
> 6/1/2005 730.0000 730.0000
> 6/3/2005 10.0000 20.0000*****From Table1
> 7/1/2005 415.0000 1245.0000
> 8/1/2005 415.0000 1660.0000
> 9/1/2005 415.0000 2075.0000
> 10/1/2005 415.0000 2490.0000
> 11/1/2005 415.0000 2905.0000
> 12/1/2005 415.0000 3320.0000
> 1/1/2006 415.0000 3735.0000
> 2/1/2006 415.0000 4150.0000
> 3/1/2006 415.0000 4565.0000
> 4/1/2006 415.0000 4980.0000
> 5/1/2006 415.0000 5395.0000
> 6/1/2006 415.0000 5810.0000
> So that 6/3 Amount from Table1 is excluded from summing with the rest of
> the
> set.
> I thought it would have been included with the Union.
> Any input is welcome,
> Nathan|||
Nathan wrote:

>Using SQL Server 2005
>Query Result From 1 Table with 3 Cols:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>7/1/2005 415.0000 830.0000
>8/1/2005 415.0000 1245.0000
>9/1/2005 415.0000 1660.0000
>10/1/2005 415.0000 2075.0000
>11/1/2005 415.0000 2490.0000
>12/1/2005 415.0000 2905.0000
>1/1/2006 415.0000 3320.0000
>2/1/2006 415.0000 3735.0000
>3/1/2006 415.0000 4150.0000
>4/1/2006 415.0000 4565.0000
>5/1/2006 415.0000 4980.0000
>Query:
>SELECT Table1.AmountDate, Table1.Amount, SUM(Table1.Amount) AS
>RunningTotal
>FROM AmountTable AS Table1 INNER JOIN
> AmountTable AS Table2 ON Table1.AmountDate >=
>Table2.AmountDate
>GROUP BY Table1.AmountDate, Table1.Amount
>ORDER BY Table1.AmountDate
>
Nathan,
The results are correct for this query. You are grouping by and summing
the same column: Table1.Amount. Table2 only determines how many times
each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
Table1.Amount:
Table1.Amount*(number of rows in the table with earlier or the same date).
Note that your "running sums" are not that at all. They are multiples:
730*1 = 730
415*2 = 830
415*3 = 1245
415*4 = 1660
...
415*12 = 4980
If you want running sums, you will need SUM(Table2.Amount).
Steve Kass
Drew University

>The problem is with the 7/1 date sum. It only added 100 and not 415
>Does anybody see a problem I did or is this something I should post to MSDN
>Product FeedBack?
>The query was a bit more complex when I unioned 2 tables and the amount was
>not coming out right and I broke it down very simply and found this to be
an
>issue.
>On a side note relating to the union relation and doing a running total, I
>only had 1 Amount in the other table for now and for some reason, that wasn
't
>summing with the rest of the set.
>SELECT AmountTable1.AmountDate, SUM(AmountTable1.AmountAmount)
>FROM (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount
> INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmount
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable1
> CROSS JOIN
> (SELECT Table1Amount.AmountDate, Table1Amount.AmountAmount
> FROM Table1Amount INNER JOIN
> Table1 ON Table1Amount.Table1ID = Table1.Table1ID
> UNION
> SELECT Table2Amount.AmountDate, Table2Amount.AmountAmou
nt
> FROM Table2Amount INNER JOIN
> Table1 AS Table1_1 ON Table2Amount.Table2ID =
>Table1_1.Table2ID
> WHERE (Table2Amount.Table2ID = '1')) AS AmountTable2
>WHERE (AmountTable1.AmountDate >= AmountTable2.AmountDate)
>GROUP BY AmountTable1.AmountDate, AmountTable1.AmountAmount
>ORDER BY AmountTable1.AmountDate
>Produces:
>Date Amount RunningTotal
>6/1/2005 730.0000 730.0000
>6/3/2005 10.0000 20.0000*****From Table1
>7/1/2005 415.0000 1245.0000
>8/1/2005 415.0000 1660.0000
>9/1/2005 415.0000 2075.0000
>10/1/2005 415.0000 2490.0000
>11/1/2005 415.0000 2905.0000
>12/1/2005 415.0000 3320.0000
>1/1/2006 415.0000 3735.0000
>2/1/2006 415.0000 4150.0000
>3/1/2006 415.0000 4565.0000
>4/1/2006 415.0000 4980.0000
>5/1/2006 415.0000 5395.0000
>6/1/2006 415.0000 5810.0000
>So that 6/3 Amount from Table1 is excluded from summing with the rest of th
e
>set.
>I thought it would have been included with the Union.
>Any input is welcome,
>Nathan
>|||AAAAHHHHHH!!!!
I can't believe I didn't catch that one. I was working on the original issue
for so long that ended up going blind.
Kudos for the good catch Steve
Nathan
"Steve Kass" wrote:

>
> Nathan wrote:
>
> Nathan,
> The results are correct for this query. You are grouping by and summing
> the same column: Table1.Amount. Table2 only determines how many times
> each Table1 row is repeated, so SUM(Table1.Amount) is just a multiple of
> Table1.Amount:
> Table1.Amount*(number of rows in the table with earlier or the same date).
> Note that your "running sums" are not that at all. They are multiples:
> 730*1 = 730
> 415*2 = 830
> 415*3 = 1245
> 415*4 = 1660
> ...
> 415*12 = 4980
>
> If you want running sums, you will need SUM(Table2.Amount).
> Steve Kass
> Drew University
>
>

A bug in SQL Server 2000?

Hi!
I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
I've got a SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
CHECKDB of every DB on that server came up clean (0
allocation and 0 consistency errors). Could this
effectively mean that I've discovered a bug in SQL Server?
Thanks,
Osk
As you alluded to, this typically indicates corruption in your database.
However, there is a potential for in-memory kinds of corruption events in
which the page in memory is corrupted, but the image on disk is not. That
is most often caused by hardware issues. I would recommend that you open a
case with Microsoft PSS (http://support.microsoft.com) after you have
verified that you have no hardware issues.
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:243401c50d2a$2000fb90$a401280a@.phx.gbl...
> Hi!
> I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
> I've got a SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
> MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
> CHECKDB of every DB on that server came up clean (0
> allocation and 0 consistency errors). Could this
> effectively mean that I've discovered a bug in SQL Server?
> --
> Thanks,
> Osk
>

A bug in SQL Server 2000?

Hi!
I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
I've got a SQL Server Assertion: File:
< p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
CHECKDB of every DB on that server came up clean (0
allocation and 0 consistency errors). Could this
effectively mean that I've discovered a bug in SQL Server?
Thanks,
OskAs you alluded to, this typically indicates corruption in your database.
However, there is a potential for in-memory kinds of corruption events in
which the page in memory is corrupted, but the image on disk is not. That
is most often caused by hardware issues. I would recommend that you open a
case with Microsoft PSS (http://support.microsoft.com) after you have
verified that you have no hardware issues.
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:243401c50d2a$2000fb90$a401280a@.phx.gbl...
> Hi!
> I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
> I've got a SQL Server Assertion: File:
> < p:\sql\ntdbms\storeng\drs\include\record
.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=
> MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
> CHECKDB of every DB on that server came up clean (0
> allocation and 0 consistency errors). Could this
> effectively mean that I've discovered a bug in SQL Server?
> --
> Thanks,
> Osk
>sql

A bug in SQL Server 2000?

Hi!
I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
I've got a SQL Server Assertion: File:
<p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <= MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
CHECKDB of every DB on that server came up clean (0
allocation and 0 consistency errors). Could this
effectively mean that I've discovered a bug in SQL Server?
--
Thanks,
OskAs you alluded to, this typically indicates corruption in your database.
However, there is a potential for in-memory kinds of corruption events in
which the page in memory is corrupted, but the image on disk is not. That
is most often caused by hardware issues. I would recommend that you open a
case with Microsoft PSS (http://support.microsoft.com) after you have
verified that you have no hardware issues.
--
Ryan Stonecipher
Microsoft SQL Server Storage Engine, DBCC
This posting is provided "AS IS" with no warranties, and confers no rights.
"Osk" <anonymous@.discussions.microsoft.com> wrote in message
news:243401c50d2a$2000fb90$a401280a@.phx.gbl...
> Hi!
> I'm running MS Windows 2000 SP4 + SQL Server 2000 SP3a.
> I've got a SQL Server Assertion: File:
> <p:\sql\ntdbms\storeng\drs\include\record.inl>, line=1447
> Failed Assertion = 'm_SizeRec > 0 && m_SizeRec <=> MAXDATAROW'. Error: 3624, Severity: 20, State: 1. The
> CHECKDB of every DB on that server came up clean (0
> allocation and 0 consistency errors). Could this
> effectively mean that I've discovered a bug in SQL Server?
> --
> Thanks,
> Osk
>

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advanceSee if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/default.aspx?scid=kb;en-us;826161&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advance
See if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/default...&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

a bug in Enterprise Manager ?

if I use enterprise manager to change Login permissions, I
am prompted to confirm the password even if the password
is not changed. Worse thing is that most of the time I
don't know the login password , so I am not able use EM to
assign permissions. I have access to other computers with
SQL EM working, and if I need to I can still use QA. I use
my pc for most of the sql admin work and would like if I
can resolve this.
What should I do ?
Thanks in advanceSee if this helps:
FIX: You Are Prompted for Password Confirmation After You Change a Standard
SQL Server Login
http://support.microsoft.com/defaul...1&Product=sql2k
Rand
This posting is provided "as is" with no warranties and confers no rights.

64000 dimension members limit?

I get an error populating a dimension that says that there are over 64000
members.
The dimension only has one level.
Is that a limit, bug or am I doing something wrong?
Thanks in advance,
Philip
Analysis Services has a 64K limit on the number of children that any one
parent can have. Since you have only one level, there is only one parent
(i.e. the ALL member); thus you are running into this problem. You can
either add a level which makes sense to your structure, for example taking
the first 3 characters of the customer name and using it as an intermediate
level, or selecting another field which makes sense, e.g. adding State to
the level above Customer, or you can turn on the Analysis Services automatic
grouping level feature where AS will automatically create a level for you.
BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philip McComish" <philvmc@.yahoo.com.br> wrote in message
news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
> I get an error populating a dimension that says that there are over 64000
> members.
> The dimension only has one level.
> Is that a limit, bug or am I doing something wrong?
> Thanks in advance,
> Philip
>
|||Dave,
Thanks for the tips. They solved my problem.
Good news about Yukon.
Best regards,
Philip
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escreveu na mensagem
news:exZFrPpJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Analysis Services has a 64K limit on the number of children that any one
> parent can have. Since you have only one level, there is only one parent
> (i.e. the ALL member); thus you are running into this problem. You can
> either add a level which makes sense to your structure, for example taking
> the first 3 characters of the customer name and using it as an
> intermediate
> level, or selecting another field which makes sense, e.g. adding State to
> the level above Customer, or you can turn on the Analysis Services
> automatic
> grouping level feature where AS will automatically create a level for you.
> BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Philip McComish" <philvmc@.yahoo.com.br> wrote in message
> news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
>

64000 dimension members limit?

I get an error populating a dimension that says that there are over 64000
members.
The dimension only has one level.
Is that a limit, bug or am I doing something wrong?
Thanks in advance,
PhilipAnalysis Services has a 64K limit on the number of children that any one
parent can have. Since you have only one level, there is only one parent
(i.e. the ALL member); thus you are running into this problem. You can
either add a level which makes sense to your structure, for example taking
the first 3 characters of the customer name and using it as an intermediate
level, or selecting another field which makes sense, e.g. adding State to
the level above Customer, or you can turn on the Analysis Services automatic
grouping level feature where AS will automatically create a level for you.
BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Philip McComish" <philvmc@.yahoo.com.br> wrote in message
news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
> I get an error populating a dimension that says that there are over 64000
> members.
> The dimension only has one level.
> Is that a limit, bug or am I doing something wrong?
> Thanks in advance,
> Philip
>|||Dave,
Thanks for the tips. They solved my problem.
Good news about Yukon.
Best regards,
Philip
"Dave Wickert [MSFT]" <dwickert@.online.microsoft.com> escreveu na mensag
em
news:exZFrPpJFHA.2604@.TK2MSFTNGP15.phx.gbl...
> Analysis Services has a 64K limit on the number of children that any one
> parent can have. Since you have only one level, there is only one parent
> (i.e. the ALL member); thus you are running into this problem. You can
> either add a level which makes sense to your structure, for example taking
> the first 3 characters of the customer name and using it as an
> intermediate
> level, or selecting another field which makes sense, e.g. adding State to
> the level above Customer, or you can turn on the Analysis Services
> automatic
> grouping level feature where AS will automatically create a level for you.
> BTW: In SQL Server 2005 (Yukon), this limitation is lifted.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> "Philip McComish" <philvmc@.yahoo.com.br> wrote in message
> news:u700f4mJFHA.3332@.TK2MSFTNGP15.phx.gbl...
>