Pretty simple question, what is the maximum amount of memory SQL 2000
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!
32GB on Win 2003 Enterprise
64GB on Win 2003 Datacenter
(I think.)
*mike hodgson*
http://sqlnerd.blogspot.com
Marks70 wrote:
>Pretty simple question, what is the maximum amount of memory SQL 2000
>Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>refering to the 32-bit versions). Thanks!
>
|||The maximum amount of memory that can be supported on Windows Server 2003 is
4 GB.
Refer => http://support.microsoft.com/?id=274750
Thanks,
Sree
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!
|||Yes but the question was about Windows 2003 Enterprise which is 32GB in the
same article.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:3DB7B61C-D8DF-4846-8A68-596D683CA237@.microsoft.com...[vbcol=seagreen]
> The maximum amount of memory that can be supported on Windows Server 2003
> is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
|||Thanks guys!
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!
Showing posts with label pretty. Show all posts
Showing posts with label pretty. Show all posts
Friday, March 30, 2012
Max Memory Support of SQL 2000 Enterprise on Windows 2003 Enterpri
Pretty simple question, what is the maximum amount of memory SQL 2000
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!This is a multi-part message in MIME format.
--090006090404030906000503
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
32GB on Win 2003 Enterprise
64GB on Win 2003 Datacenter
(I think.)
--
*mike hodgson*
http://sqlnerd.blogspot.com
Marks70 wrote:
>Pretty simple question, what is the maximum amount of memory SQL 2000
>Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>refering to the 32-bit versions). Thanks!
>
--090006090404030906000503
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>32GB on Win 2003 Enterprise<br>
64GB on Win 2003 Datacenter<br>
<br>
(I think.)<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Marks70 wrote:
<blockquote cite="mid91F52420-72D3-4203-BE73-5C9A3AD9783A@.microsoft.com"
type="cite">
<pre wrap="">Pretty simple question, what is the maximum amount of memory SQL 2000
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!
</pre>
</blockquote>
</body>
</html>
--090006090404030906000503--|||The maximum amount of memory that can be supported on Windows Server 2003 is
4 GB.
Refer => http://support.microsoft.com/?id=274750
Thanks,
Sree
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!|||Yes but the question was about Windows 2003 Enterprise which is 32GB in the
same article.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:3DB7B61C-D8DF-4846-8A68-596D683CA237@.microsoft.com...
> The maximum amount of memory that can be supported on Windows Server 2003
> is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
>> Pretty simple question, what is the maximum amount of memory SQL 2000
>> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>> refering to the 32-bit versions). Thanks!|||Correction :) =>
The maximum amount of memory that can be supported on Windows Server 2003
is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of
physical RAM.
Thanks,
Sree
"Sreejith G" wrote:
> The maximum amount of memory that can be supported on Windows Server 2003 is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
> > Pretty simple question, what is the maximum amount of memory SQL 2000
> > Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> > refering to the 32-bit versions). Thanks!|||Thanks guys!
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!sql
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!This is a multi-part message in MIME format.
--090006090404030906000503
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
32GB on Win 2003 Enterprise
64GB on Win 2003 Datacenter
(I think.)
--
*mike hodgson*
http://sqlnerd.blogspot.com
Marks70 wrote:
>Pretty simple question, what is the maximum amount of memory SQL 2000
>Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>refering to the 32-bit versions). Thanks!
>
--090006090404030906000503
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>32GB on Win 2003 Enterprise<br>
64GB on Win 2003 Datacenter<br>
<br>
(I think.)<br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font></span> <b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"><br>
<font face="Tahoma" size="2"><a href="http://links.10026.com/?link=http://sqlnerd.blogspot.com</a></font></span>">http://sqlnerd.blogspot.com">http://sqlnerd.blogspot.com</a></font></span>
</p>
</div>
<br>
<br>
Marks70 wrote:
<blockquote cite="mid91F52420-72D3-4203-BE73-5C9A3AD9783A@.microsoft.com"
type="cite">
<pre wrap="">Pretty simple question, what is the maximum amount of memory SQL 2000
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!
</pre>
</blockquote>
</body>
</html>
--090006090404030906000503--|||The maximum amount of memory that can be supported on Windows Server 2003 is
4 GB.
Refer => http://support.microsoft.com/?id=274750
Thanks,
Sree
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!|||Yes but the question was about Windows 2003 Enterprise which is 32GB in the
same article.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:3DB7B61C-D8DF-4846-8A68-596D683CA237@.microsoft.com...
> The maximum amount of memory that can be supported on Windows Server 2003
> is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
>> Pretty simple question, what is the maximum amount of memory SQL 2000
>> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>> refering to the 32-bit versions). Thanks!|||Correction :) =>
The maximum amount of memory that can be supported on Windows Server 2003
is 4 GB. However, Windows Server 2003 Enterprise Edition supports 32 GB of
physical RAM.
Thanks,
Sree
"Sreejith G" wrote:
> The maximum amount of memory that can be supported on Windows Server 2003 is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
> > Pretty simple question, what is the maximum amount of memory SQL 2000
> > Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> > refering to the 32-bit versions). Thanks!|||Thanks guys!
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!sql
Max Memory Support of SQL 2000 Enterprise on Windows 2003 Enterpri
Pretty simple question, what is the maximum amount of memory SQL 2000
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!32GB on Win 2003 Enterprise
64GB on Win 2003 Datacenter
(I think.)
*mike hodgson*
http://sqlnerd.blogspot.com
Marks70 wrote:
>Pretty simple question, what is the maximum amount of memory SQL 2000
>Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>refering to the 32-bit versions). Thanks!
>|||The maximum amount of memory that can be supported on Windows Server 2003 is
4 GB.
Refer => http://support.microsoft.com/?id=274750
Thanks,
Sree
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!|||Yes but the question was about Windows 2003 Enterprise which is 32GB in the
same article.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:3DB7B61C-D8DF-4846-8A68-596D683CA237@.microsoft.com...[vbcol=seagreen]
> The maximum amount of memory that can be supported on Windows Server 2003
> is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
>|||Thanks guys!
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!
Enterprise Edition can support on Windows 2003 Enterprise Edition (all
refering to the 32-bit versions). Thanks!32GB on Win 2003 Enterprise
64GB on Win 2003 Datacenter
(I think.)
*mike hodgson*
http://sqlnerd.blogspot.com
Marks70 wrote:
>Pretty simple question, what is the maximum amount of memory SQL 2000
>Enterprise Edition can support on Windows 2003 Enterprise Edition (all
>refering to the 32-bit versions). Thanks!
>|||The maximum amount of memory that can be supported on Windows Server 2003 is
4 GB.
Refer => http://support.microsoft.com/?id=274750
Thanks,
Sree
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!|||Yes but the question was about Windows 2003 Enterprise which is 32GB in the
same article.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Sreejith G" <SreejithG@.discussions.microsoft.com> wrote in message
news:3DB7B61C-D8DF-4846-8A68-596D683CA237@.microsoft.com...[vbcol=seagreen]
> The maximum amount of memory that can be supported on Windows Server 2003
> is
> 4 GB.
> Refer => http://support.microsoft.com/?id=274750
> Thanks,
> Sree
>
> "Marks70" wrote:
>|||Thanks guys!
"Marks70" wrote:
> Pretty simple question, what is the maximum amount of memory SQL 2000
> Enterprise Edition can support on Windows 2003 Enterprise Edition (all
> refering to the 32-bit versions). Thanks!
Wednesday, March 28, 2012
Max Date for multiple columns
Hi all,
Okay... this should be a pretty question, but, can't seem to figure out
how to do it. In this database I'm working with, they have created 8 column
s
(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
I need to get the max date (Date1, Date2, Date3 or Date4) and the
information from the appropriate column (So, if Date2 is the max date, then
return the result set containing Version2 and Date2 data)
Any idea about how to approach this problem?
Any help is greatly apprectiated.
DougSure...
CREATE TABLE [dbo].[TEST] (
[ACCOUNT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE1] [datetime] NULL ,
[SYSTEM2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE2] [datetime] NULL ,
[SYSTEM3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE3] [datetime] NULL ,
[SYSTEM4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE4] [datetime] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Doug" wrote:
> Hi all,
> Okay... this should be a pretty question, but, can't seem to figure ou
t
> how to do it. In this database I'm working with, they have created 8 colu
mns
> (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
> information from the appropriate column (So, if Date2 is the max date, the
n
> return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
> Any help is greatly apprectiated.
> Doug|||I see several solutions
A. Self joins
B. Big if statement
or
C. Temp table
Create a temp table that has the record number, Version, and Date. Each
row represents a single Version / Date pair. Each row in your original
table would then become 4 rows in the temp table.
Then you could do
select recordid, max(datecol)
from tempTable
group by recordid|||>> Any idea about how to approach this problem?
The very fact that a simple query as this requires a complex solution itself
suggest that the table design could be improved. Rather than using column
names to represent data, consider something along the lines of:
CREATE TABLE tbl (
key_col ...
version ..
date_col DATETIME ) ;
This will allow you to add more versions without having to alter the schema.
Moreover the design is more flexible & allows for better constraint
enforcement as well.
If you are somehow forced to stick with the existing schema consider using a
view/ derived table to logically abstract the data like:
SELECT key_col,
CASE n WHEN 1 THEN version1
WHEN 2 THEN version2
WHEN 3 THEN version3
WHEN 4 THEN version4
END AS "version",
CASE n WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS "version_date"
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) T ( n );
Now, it is just a matter of using aggregate function MAX() on the
version_date column to get the required value.
Anith|||On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>Hi all,
> Okay... this should be a pretty question, but, can't seem to figure out
>how to do it. In this database I'm working with, they have created 8 colum
ns
>(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
>information from the appropriate column (So, if Date2 is the max date, then
>return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
Hi Doug,
Normalise your design. You should have a seperate table with Date and
Version as columns, plus a foreign key to the table where these 8
columns now are. Then, it's quite easy.
Assuming the normalised table looks like this
CREATE TABLE YourTable
(CustomerID int NOT NULL,
TheDate datetime NOT NULL,
Version varchar(20) NOT NULL,
PRIMARY KEY (CustomerID, TheDate),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
The query is like this
SELECT a.CustomerID, a.TheDate, a.Version
FROM YourTable AS a
INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
FROM YourTable
GROUP BY CustomerID) AS b
ON b.Customer = a.Customer
AND b.MaxDate = a.TheDate
Hugo Kornelis, SQL Server MVP|||Hi all,
Thanks for all of the help! Unfortunately, the design can't be
normalised. We are using the Goldmine application (commercial product) and
they designed the tables to work this way. This design has caused me a
number of headaches.
Overall, I went with a stored procedure to get the data into a
temporary table that was more normalized and then got my information using
standard techniques.
Dougie
"Hugo Kornelis" wrote:
> On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>
> Hi Doug,
> Normalise your design. You should have a seperate table with Date and
> Version as columns, plus a foreign key to the table where these 8
> columns now are. Then, it's quite easy.
> Assuming the normalised table looks like this
> CREATE TABLE YourTable
> (CustomerID int NOT NULL,
> TheDate datetime NOT NULL,
> Version varchar(20) NOT NULL,
> PRIMARY KEY (CustomerID, TheDate),
> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
> )
> The query is like this
> SELECT a.CustomerID, a.TheDate, a.Version
> FROM YourTable AS a
> INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
> FROM YourTable
> GROUP BY CustomerID) AS b
> ON b.Customer = a.Customer
> AND b.MaxDate = a.TheDate
> --
> Hugo Kornelis, SQL Server MVP
>
Okay... this should be a pretty question, but, can't seem to figure out
how to do it. In this database I'm working with, they have created 8 column
s
(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
I need to get the max date (Date1, Date2, Date3 or Date4) and the
information from the appropriate column (So, if Date2 is the max date, then
return the result set containing Version2 and Date2 data)
Any idea about how to approach this problem?
Any help is greatly apprectiated.
DougSure...
CREATE TABLE [dbo].[TEST] (
[ACCOUNT] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SYSTEM1] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE1] [datetime] NULL ,
[SYSTEM2] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE2] [datetime] NULL ,
[SYSTEM3] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE3] [datetime] NULL ,
[SYSTEM4] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PURCHASEDATE4] [datetime] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
"Doug" wrote:
> Hi all,
> Okay... this should be a pretty question, but, can't seem to figure ou
t
> how to do it. In this database I'm working with, they have created 8 colu
mns
> (Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
> information from the appropriate column (So, if Date2 is the max date, the
n
> return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
> Any help is greatly apprectiated.
> Doug|||I see several solutions
A. Self joins
B. Big if statement
or
C. Temp table
Create a temp table that has the record number, Version, and Date. Each
row represents a single Version / Date pair. Each row in your original
table would then become 4 rows in the temp table.
Then you could do
select recordid, max(datecol)
from tempTable
group by recordid|||>> Any idea about how to approach this problem?
The very fact that a simple query as this requires a complex solution itself
suggest that the table design could be improved. Rather than using column
names to represent data, consider something along the lines of:
CREATE TABLE tbl (
key_col ...
version ..
date_col DATETIME ) ;
This will allow you to add more versions without having to alter the schema.
Moreover the design is more flexible & allows for better constraint
enforcement as well.
If you are somehow forced to stick with the existing schema consider using a
view/ derived table to logically abstract the data like:
SELECT key_col,
CASE n WHEN 1 THEN version1
WHEN 2 THEN version2
WHEN 3 THEN version3
WHEN 4 THEN version4
END AS "version",
CASE n WHEN 1 THEN date1
WHEN 2 THEN date2
WHEN 3 THEN date3
WHEN 4 THEN date4
END AS "version_date"
FROM tbl, ( SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 ) T ( n );
Now, it is just a matter of using aggregate function MAX() on the
version_date column to get the required value.
Anith|||On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>Hi all,
> Okay... this should be a pretty question, but, can't seem to figure out
>how to do it. In this database I'm working with, they have created 8 colum
ns
>(Version1, Date1, Version2, Date2, Version3,Date3, Version4, Date4).
> I need to get the max date (Date1, Date2, Date3 or Date4) and the
>information from the appropriate column (So, if Date2 is the max date, then
>return the result set containing Version2 and Date2 data)
> Any idea about how to approach this problem?
Hi Doug,
Normalise your design. You should have a seperate table with Date and
Version as columns, plus a foreign key to the table where these 8
columns now are. Then, it's quite easy.
Assuming the normalised table looks like this
CREATE TABLE YourTable
(CustomerID int NOT NULL,
TheDate datetime NOT NULL,
Version varchar(20) NOT NULL,
PRIMARY KEY (CustomerID, TheDate),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
)
The query is like this
SELECT a.CustomerID, a.TheDate, a.Version
FROM YourTable AS a
INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
FROM YourTable
GROUP BY CustomerID) AS b
ON b.Customer = a.Customer
AND b.MaxDate = a.TheDate
Hugo Kornelis, SQL Server MVP|||Hi all,
Thanks for all of the help! Unfortunately, the design can't be
normalised. We are using the Goldmine application (commercial product) and
they designed the tables to work this way. This design has caused me a
number of headaches.
Overall, I went with a stored procedure to get the data into a
temporary table that was more normalized and then got my information using
standard techniques.
Dougie
"Hugo Kornelis" wrote:
> On Mon, 20 Mar 2006 06:59:42 -0800, Doug wrote:
>
> Hi Doug,
> Normalise your design. You should have a seperate table with Date and
> Version as columns, plus a foreign key to the table where these 8
> columns now are. Then, it's quite easy.
> Assuming the normalised table looks like this
> CREATE TABLE YourTable
> (CustomerID int NOT NULL,
> TheDate datetime NOT NULL,
> Version varchar(20) NOT NULL,
> PRIMARY KEY (CustomerID, TheDate),
> FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
> )
> The query is like this
> SELECT a.CustomerID, a.TheDate, a.Version
> FROM YourTable AS a
> INNER JOIN (SELECT CustomerID, MAX(TheDate) AS MaxDate
> FROM YourTable
> GROUP BY CustomerID) AS b
> ON b.Customer = a.Customer
> AND b.MaxDate = a.TheDate
> --
> Hugo Kornelis, SQL Server MVP
>
Friday, March 23, 2012
Matrix Subtotals
I've just started using SSRS 2005 and am pretty impressed, however, I have
hit a stumbling block with Matrix sub totals, I have data displayed like so :
Client 1, 2006-01, £10000
Client 1, 2006-02, £15000
client2, 2006-01, £25000
client2, 2006-02, £10000
client2, 2006-03, £5000
(I have left out the pivoted data for simplicity - the above are all rows)
If I add a subtotal it totals all of the pivoted totals at the base of the
matrix but I actually want to subtotal on each client, for example, client1
would have a total of £25000 and Client2 a total of £40000. The subtotal is
grouped by creditorID ie Client1, Client2 so I'm not sure what I am missing.
Is it even possible to subtotal by each group? Any help appreciated
MarkHi Mark,
Thank you for your posting!
Based on my experience, you could do this. I assume you use the date field
as the row group. The easied way to do this is right-click the text of the
date field and click Subtotal. Then you could get the subtotal of date
field and grouped by the creditID field.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks a lot for that Wei, I'm getting very strange results - if I add a
subtotal to the CreditorID field it adds a grand total at the end of the
matrix, if I add it below the period / date field it gives a wildly
inaccurate figure. Could it be something to do with Scope - been searching
the net for Scope info but not much luck - I think I'll need to go back to
the drawing board on this one,
Can you recommend any good books or links which go into Matrix reports in
detail. Any further help greatfully received.
Mark
"Wei Lu [MSFT]" wrote:
> Hi Mark,
> Thank you for your posting!
> Based on my experience, you could do this. I assume you use the date field
> as the row group. The easied way to do this is right-click the text of the
> date field and click Subtotal. Then you could get the subtotal of date
> field and grouped by the creditID field.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Mark,
Thank you for the update.
As for the subtotal issue, please send an email to me and I will send the
sample rdl file to you.
As for the books for Matrix report, you could refer the SQL Books online
help:
Working with Matrix Data Regions
http://msdn2.microsoft.com/en-us/library/ms157334(d=ide).aspx
My direct email address is weilu@.ONLINE.microsoft.com (please remove the
ONLINE when you send the email), you may send an email to me directly and I
will reply with the sample.
Please let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
hit a stumbling block with Matrix sub totals, I have data displayed like so :
Client 1, 2006-01, £10000
Client 1, 2006-02, £15000
client2, 2006-01, £25000
client2, 2006-02, £10000
client2, 2006-03, £5000
(I have left out the pivoted data for simplicity - the above are all rows)
If I add a subtotal it totals all of the pivoted totals at the base of the
matrix but I actually want to subtotal on each client, for example, client1
would have a total of £25000 and Client2 a total of £40000. The subtotal is
grouped by creditorID ie Client1, Client2 so I'm not sure what I am missing.
Is it even possible to subtotal by each group? Any help appreciated
MarkHi Mark,
Thank you for your posting!
Based on my experience, you could do this. I assume you use the date field
as the row group. The easied way to do this is right-click the text of the
date field and click Subtotal. Then you could get the subtotal of date
field and grouped by the creditID field.
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Thanks a lot for that Wei, I'm getting very strange results - if I add a
subtotal to the CreditorID field it adds a grand total at the end of the
matrix, if I add it below the period / date field it gives a wildly
inaccurate figure. Could it be something to do with Scope - been searching
the net for Scope info but not much luck - I think I'll need to go back to
the drawing board on this one,
Can you recommend any good books or links which go into Matrix reports in
detail. Any further help greatfully received.
Mark
"Wei Lu [MSFT]" wrote:
> Hi Mark,
> Thank you for your posting!
> Based on my experience, you could do this. I assume you use the date field
> as the row group. The easied way to do this is right-click the text of the
> date field and click Subtotal. Then you could get the subtotal of date
> field and grouped by the creditID field.
> Hope this will be helpful!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================> This posting is provided "AS IS" with no warranties, and confers no rights.
>|||Hi Mark,
Thank you for the update.
As for the subtotal issue, please send an email to me and I will send the
sample rdl file to you.
As for the books for Matrix report, you could refer the SQL Books online
help:
Working with Matrix Data Regions
http://msdn2.microsoft.com/en-us/library/ms157334(d=ide).aspx
My direct email address is weilu@.ONLINE.microsoft.com (please remove the
ONLINE when you send the email), you may send an email to me directly and I
will reply with the sample.
Please let me know the result. Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.
Monday, March 19, 2012
Matrix Question
Hello,
I have been working away with the SSRS and am getting pretty comfortable
with some of it's features, benefits and limitations. I have created a
series of Matrix reports (pivot tables) that show sales from 2004, 2005,
2006, 2007 and even future sales of 2008. My goal is to have a comparison of
2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
acomplish this goal but I don't see how I acomplish this calculation in a
matrix. My field for the year is 'year(eve_date)'.
Thanks in advance - I'm sure it's simply and I'm just being brain dead.
ChrisOn May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> Hello,
> I have been working away with the SSRS and am getting pretty comfortable
> with some of it's features, benefits and limitations. I have created a
> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> 2006, 2007 and even future sales of 2008. My goal is to have a comparison of
> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
> acomplish this goal but I don't see how I acomplish this calculation in a
> matrix. My field for the year is 'year(eve_date)'.
> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> Chris
I would suggest handling this functionality in the query/stored
procedure that is sourcing the report. I normally use while loops or
cursors to accomplish this. Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks - I hoped that this could be done on the report but we can try the
query idea.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
>> Hello,
>> I have been working away with the SSRS and am getting pretty comfortable
>> with some of it's features, benefits and limitations. I have created a
>> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
>> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
>> of
>> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
>> to
>> acomplish this goal but I don't see how I acomplish this calculation in a
>> matrix. My field for the year is 'year(eve_date)'.
>> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
>> Chris
>
> I would suggest handling this functionality in the query/stored
> procedure that is sourcing the report. I normally use while loops or
> cursors to accomplish this. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Best possible is to bring from query, if there is any limitations on writing
the query or access etc... try calculated fields
Amarnath
"Chris Marsh" wrote:
> Thanks - I hoped that this could be done on the report but we can try the
> query idea.
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> > On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> >> Hello,
> >>
> >> I have been working away with the SSRS and am getting pretty comfortable
> >> with some of it's features, benefits and limitations. I have created a
> >> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> >> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
> >> of
> >> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
> >> to
> >> acomplish this goal but I don't see how I acomplish this calculation in a
> >> matrix. My field for the year is 'year(eve_date)'.
> >>
> >> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> >>
> >> Chris
> >
> >
> > I would suggest handling this functionality in the query/stored
> > procedure that is sourcing the report. I normally use while loops or
> > cursors to accomplish this. Hope this is helpful.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>
I have been working away with the SSRS and am getting pretty comfortable
with some of it's features, benefits and limitations. I have created a
series of Matrix reports (pivot tables) that show sales from 2004, 2005,
2006, 2007 and even future sales of 2008. My goal is to have a comparison of
2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
acomplish this goal but I don't see how I acomplish this calculation in a
matrix. My field for the year is 'year(eve_date)'.
Thanks in advance - I'm sure it's simply and I'm just being brain dead.
ChrisOn May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> Hello,
> I have been working away with the SSRS and am getting pretty comfortable
> with some of it's features, benefits and limitations. I have created a
> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> 2006, 2007 and even future sales of 2008. My goal is to have a comparison of
> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how to
> acomplish this goal but I don't see how I acomplish this calculation in a
> matrix. My field for the year is 'year(eve_date)'.
> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> Chris
I would suggest handling this functionality in the query/stored
procedure that is sourcing the report. I normally use while loops or
cursors to accomplish this. Hope this is helpful.
Regards,
Enrique Martinez
Sr. Software Consultant|||Thanks - I hoped that this could be done on the report but we can try the
query idea.
"EMartinez" <emartinez.pr1@.gmail.com> wrote in message
news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
>> Hello,
>> I have been working away with the SSRS and am getting pretty comfortable
>> with some of it's features, benefits and limitations. I have created a
>> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
>> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
>> of
>> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
>> to
>> acomplish this goal but I don't see how I acomplish this calculation in a
>> matrix. My field for the year is 'year(eve_date)'.
>> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
>> Chris
>
> I would suggest handling this functionality in the query/stored
> procedure that is sourcing the report. I normally use while loops or
> cursors to accomplish this. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>|||Best possible is to bring from query, if there is any limitations on writing
the query or access etc... try calculated fields
Amarnath
"Chris Marsh" wrote:
> Thanks - I hoped that this could be done on the report but we can try the
> query idea.
> "EMartinez" <emartinez.pr1@.gmail.com> wrote in message
> news:1178755079.558957.64180@.y80g2000hsf.googlegroups.com...
> > On May 9, 2:32 pm, "Chris Marsh" <cma...@.synergy-intl.com> wrote:
> >> Hello,
> >>
> >> I have been working away with the SSRS and am getting pretty comfortable
> >> with some of it's features, benefits and limitations. I have created a
> >> series of Matrix reports (pivot tables) that show sales from 2004, 2005,
> >> 2006, 2007 and even future sales of 2008. My goal is to have a comparison
> >> of
> >> 2004 versus 2005; 2005 versus 2006 and so on. In Excel I understand how
> >> to
> >> acomplish this goal but I don't see how I acomplish this calculation in a
> >> matrix. My field for the year is 'year(eve_date)'.
> >>
> >> Thanks in advance - I'm sure it's simply and I'm just being brain dead.
> >>
> >> Chris
> >
> >
> > I would suggest handling this functionality in the query/stored
> > procedure that is sourcing the report. I normally use while loops or
> > cursors to accomplish this. Hope this is helpful.
> >
> > Regards,
> >
> > Enrique Martinez
> > Sr. Software Consultant
> >
>
>
Monday, March 12, 2012
Matrix Groups and Sums
I have what I thought was a pretty simple report, but have encountered two
issues.
I have three groups in this report.
1. When using sum on my detail row I am getting what appears to be a running
total in one of the columns. When I don't use sum then I get either the first
or last value for a detail record.
2. When using groups, the results are not what I would expect. It seems
almost impossible to get the grouping I would like. If I want to total on the
first group and then the second group and then a final total for all the
groups I can't. Am I missing something as this seems like it should be a
trivial effort.
If anybody can help I would appreciate it.
--
DCDUse Fields!fieldname.value (sounds like you are getting sum, first, and
last)... do not use a function at all..
Look at the sum documentation,, there is an additional parameter which
allows you to set the scope, Use a group name there and see if that helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Darryl" <ddillman@.tmsteam.com> wrote in message
news:F1127546-8532-4873-A11B-B6A7D3B8897E@.microsoft.com...
>I have what I thought was a pretty simple report, but have encountered two
> issues.
> I have three groups in this report.
> 1. When using sum on my detail row I am getting what appears to be a
> running
> total in one of the columns. When I don't use sum then I get either the
> first
> or last value for a detail record.
> 2. When using groups, the results are not what I would expect. It seems
> almost impossible to get the grouping I would like. If I want to total on
> the
> first group and then the second group and then a final total for all the
> groups I can't. Am I missing something as this seems like it should be a
> trivial effort.
> If anybody can help I would appreciate it.
> --
> DCD
issues.
I have three groups in this report.
1. When using sum on my detail row I am getting what appears to be a running
total in one of the columns. When I don't use sum then I get either the first
or last value for a detail record.
2. When using groups, the results are not what I would expect. It seems
almost impossible to get the grouping I would like. If I want to total on the
first group and then the second group and then a final total for all the
groups I can't. Am I missing something as this seems like it should be a
trivial effort.
If anybody can help I would appreciate it.
--
DCDUse Fields!fieldname.value (sounds like you are getting sum, first, and
last)... do not use a function at all..
Look at the sum documentation,, there is an additional parameter which
allows you to set the scope, Use a group name there and see if that helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Darryl" <ddillman@.tmsteam.com> wrote in message
news:F1127546-8532-4873-A11B-B6A7D3B8897E@.microsoft.com...
>I have what I thought was a pretty simple report, but have encountered two
> issues.
> I have three groups in this report.
> 1. When using sum on my detail row I am getting what appears to be a
> running
> total in one of the columns. When I don't use sum then I get either the
> first
> or last value for a detail record.
> 2. When using groups, the results are not what I would expect. It seems
> almost impossible to get the grouping I would like. If I want to total on
> the
> first group and then the second group and then a final total for all the
> groups I can't. Am I missing something as this seems like it should be a
> trivial effort.
> If anybody can help I would appreciate it.
> --
> DCD
Subscribe to:
Posts (Atom)