MAX and DATEDIFF: value

Showing posts with label value. Show all posts

Wednesday, March 21, 2012

MaxLocksPerFile

I wrote a script in Installshield that changes the MaxLocksPerFile value in
Registry for Jet 3.x and Jet 4.0
While installing the application through Installshield, the custom value for
MaxLocksPerFile is being overwritten to 9500 in Registry for Jet
4.0(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0) but not
for Jet 3.x(HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 3.x).
The value is being overwritten to 9500 before the control reaches at the
script code.

I am wondering from where this 9500 value is being retrieved and set in
Registry.
Is there some file where these default values are mentioned.
If so then why 9500 value is not being set for Jet 3.x and is being set only
for Jet 4.0.

i am using MS Office 2002. Drivers for ODBC MS Access is above 4.0
and Installshield Version is 5.5.

Please help me to resolve this Issue.

-Regards
Harpreet

The default value for both Jet 3.5 and 4.0 is 9500.

If your installation script is causing Jet to be re-registered it will reset the values in the registry back to their defaults and over write yours.

The 9500 value was originally put in for Netware servers and typically you do not need to change it from its default pf 9500.

See page 611 of "The Jet Database Engine Programmers Guide" or futher searching of MSDN.

|||

Hi Joel,

Thanx for your reply.

Could you please let me know from where this default value of 9500 is retrieved and set the registry variable.

Could you please get me access to "The Jet Database Engine Programmers Guide" . I currently do not have this guide.

-Regards

Harpreet

|||

Here is a link to the book on Amazon:

http://www.amazon.com/Microsoft-Database-Programmers-Professional-Editions/dp/B00005TNX9

The registry is:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

default is 9500

|||

Hi Joel

Thanks for the information.

Still I have one question: Is there some file (configuration file or so) from where this default value of 9500 is taken and set in to the registry.

-Regards

Harpreet

|||The registry value is created in setup or when registering msjet40.dll.|||

HI Joel,

Can we change this registry value during registering.We have _drivers.ini file which contains default values for arguments.I tried to set the custom value in that ini file, but during installation, registry value is again set to default value of 9500.

sql

MaxLocksPerFile

i am using MS Office 2002. Drivers for ODBC MS Access is above 4.0
and Installshield Version is 5.5.

Please help me to resolve this Issue.

-Regards
Harpreet

The default value for both Jet 3.5 and 4.0 is 9500.

If your installation script is causing Jet to be re-registered it will reset the values in the registry back to their defaults and over write yours.

The 9500 value was originally put in for Netware servers and typically you do not need to change it from its default pf 9500.

See page 611 of "The Jet Database Engine Programmers Guide" or futher searching of MSDN.

|||

Hi Joel,

Thanx for your reply.

Could you please let me know from where this default value of 9500 is retrieved and set the registry variable.

Could you please get me access to "The Jet Database Engine Programmers Guide" . I currently do not have this guide.

-Regards

Harpreet

|||

Here is a link to the book on Amazon:

http://www.amazon.com/Microsoft-Database-Programmers-Professional-Editions/dp/B00005TNX9

The registry is:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

default is 9500

|||

Hi Joel

Thanks for the information.

Still I have one question: Is there some file (configuration file or so) from where this default value of 9500 is taken and set in to the registry.

-Regards

Harpreet

|||The registry value is created in setup or when registering msjet40.dll.|||

HI Joel,

MaxLocksPerFile

i am using MS Office 2002. Drivers for ODBC MS Access is above 4.0
and Installshield Version is 5.5.

Please help me to resolve this Issue.

-Regards
Harpreet

The default value for both Jet 3.5 and 4.0 is 9500.

If your installation script is causing Jet to be re-registered it will reset the values in the registry back to their defaults and over write yours.

The 9500 value was originally put in for Netware servers and typically you do not need to change it from its default pf 9500.

See page 611 of "The Jet Database Engine Programmers Guide" or futher searching of MSDN.

|||

Hi Joel,

Thanx for your reply.

Could you please let me know from where this default value of 9500 is retrieved and set the registry variable.

Could you please get me access to "The Jet Database Engine Programmers Guide" . I currently do not have this guide.

-Regards

Harpreet

|||

Here is a link to the book on Amazon:

http://www.amazon.com/Microsoft-Database-Programmers-Professional-Editions/dp/B00005TNX9

The registry is:

HKEY_LOCAL_MACHINE\Software\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

default is 9500

|||

Hi Joel

Thanks for the information.

Still I have one question: Is there some file (configuration file or so) from where this default value of 9500 is taken and set in to the registry.

-Regards

Harpreet

|||The registry value is created in setup or when registering msjet40.dll.|||

HI Joel,

Monday, March 19, 2012

maximum width

When loading a report that another developer created, I got an error
message saying that the deserialization failed because the width value
needs to be between 0 and 85.3333 in. I was able to get around this by
opening the RDL file in notepad and changing the width values manually.
But my questions is, how was the developer able to set the width >
85.3333 if it was not allowed?Probably, the report was manually edited.
As for the 85.3 in limit - in RS 2000 SP2 it will increase to 160 in.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Bucky" <uw_badgers@.mail.com> wrote in message
news:1103826459.105803.261470@.f14g2000cwb.googlegroups.com...
> When loading a report that another developer created, I got an error
> message saying that the deserialization failed because the width value
> needs to be between 0 and 85.3333 in. I was able to get around this by
> opening the RDL file in notepad and changing the width values manually.
>
> But my questions is, how was the developer able to set the width >
> 85.3333 if it was not allowed?
>

Maximum value of mulitple columns

Hi all

Using sql server 2005, Im trying in a query to get the maximum value of multiple columns of a table for each of its records.

What im trying to get is the last date an index was used using the table sys.dm_db_usage_stats using the date fields (last_user_seek, last_user_update...).

I looked around the forum for a solution but those i found dont seem to apply really easily to a query.

Anyone got a suggestion?

Dale:

There have been a number of discussions about a similar issue; give a look to this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=733186&SiteID=1

|||

Dale L.,

Try using new operators UNPIVOT and "CROSS APPLY".

Code Snippet

create table dbo.t1 (

pk int not null identity unique,

c1 int,

c2 int,

c3 int

)

insert into dbo.t1(c1, c2, c3) values(1, 2, 3)

insert into dbo.t1(c1, c2, c3) values(4, 6, 5)

insert into dbo.t1(c1, c2, c3) values(9, 7, 8)

select

a.pk,

b.max_value

from

dbo.t1 as a

cross apply

(

select

max(unpvt.[value]) as max_value

from

(

select

t.pk,

t.c1 as [1],

t.c2 as [2],

t.c3 as [3]

from

dbo.t1 as t

where

t.pk = a.pk

) as pvt

unpivot

([value] for [col] in ([1], [2], [3])) as unpvt

group by

unpvt.pk

) as b

drop table dbo.t1

AMB

Maximum value for Unique ID

quote:

> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>

|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:

quote:

> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>
>
>

|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...

quote:

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
property,[QUOTE]
range[QUOTE]
>

|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...

quote:

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
property,[QUOTE]
range[QUOTE]
>

|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:

quote:

> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>
>

Maximum value for Unique ID

Hi,
Am a newbie to sql server so apologies if this question seems a bit
simple but here goes anyway. All our tables are defined with a unique
id column with the identity attribute. What is the maximum value for
this identity column and what happens if this maximum value is reached?
Many thanks in advance
Lee.It depends what type the column was declared as. IDENTITY is a property,
not a type. There is an underlying column type - it could be an integer
type (tinyint, smallint, int, bigint) or a decimal type with scale 0
(decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
of the declared type, you will get an overflow error.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> Hi,
> Am a newbie to sql server so apologies if this question seems a bit
> simple but here goes anyway. All our tables are defined with a unique
> id column with the identity attribute. What is the maximum value for
> this identity column and what happens if this maximum value is reached?
>
> Many thanks in advance
> Lee.
>|||Many thanks for the response steve.
Just to make sure I understand then, if our column that has the identity
attribute is declared as int and the value tries to go above
2,147,483,647 an overflow error will be returned, rather than sql going
back through using any gaps.
Many thanks
Lee.
Steve Kass wrote:
> It depends what type the column was declared as. IDENTITY is a property,
> not a type. There is an underlying column type - it could be an integer
> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the range
> of the declared type, you will get an overflow error.
> SK
>
> "monkey" <monkey@.email.com> wrote in message
> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>>Hi,
>>Am a newbie to sql server so apologies if this question seems a bit
>>simple but here goes anyway. All our tables are defined with a unique
>>id column with the identity attribute. What is the maximum value for
>>this identity column and what happens if this maximum value is reached?
>>
>>Many thanks in advance
>>Lee.
>
>|||correct - if you want to see it in action, use dbcc checkident and reseed
with the max value. Note, however, that you can use negative values (in
case you start with 1 as the original seed)
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Yes. The identity property simply generates sequential values. Whether a
value generated ends up in the table or not is irrelevant, as is the actual
data in the table. The identity property does guarantee uniqueness or that
there will be no gaps.
SK
"monkey" <monkey@.email.com> wrote in message
news:bv3ho6$16$1$8302bc10@.news.demon.co.uk...
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
> > It depends what type the column was declared as. IDENTITY is a
property,
> > not a type. There is an underlying column type - it could be an integer
> > type (tinyint, smallint, int, bigint) or a decimal type with scale 0
> > (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
range
> > of the declared type, you will get an overflow error.
> >
> > SK
> >
> >
> > "monkey" <monkey@.email.com> wrote in message
> > news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
> >
> >>Hi,
> >>
> >>Am a newbie to sql server so apologies if this question seems a bit
> >>simple but here goes anyway. All our tables are defined with a unique
> >>id column with the identity attribute. What is the maximum value for
> >>this identity column and what happens if this maximum value is reached?
> >>
> >>
> >>Many thanks in advance
> >>
> >>Lee.
> >>
> >
> >
> >
>|||Many thanks for all the responses, with your help I think I have a
pretty good understanding now.
Thanks again
lee.
monkey wrote:
> Many thanks for the response steve.
> Just to make sure I understand then, if our column that has the identity
> attribute is declared as int and the value tries to go above
> 2,147,483,647 an overflow error will be returned, rather than sql going
> back through using any gaps.
> Many thanks
> Lee.
> Steve Kass wrote:
>> It depends what type the column was declared as. IDENTITY is a property,
>> not a type. There is an underlying column type - it could be an integer
>> type (tinyint, smallint, int, bigint) or a decimal type with scale 0
>> (decimal(1,0), decimal(2,0), ..., decimal(38,0)). If you exceed the
>> range
>> of the declared type, you will get an overflow error.
>> SK
>>
>> "monkey" <monkey@.email.com> wrote in message
>> news:bv3ftf$sg7$1$8300dec7@.news.demon.co.uk...
>> Hi,
>> Am a newbie to sql server so apologies if this question seems a bit
>> simple but here goes anyway. All our tables are defined with a unique
>> id column with the identity attribute. What is the maximum value for
>> this identity column and what happens if this maximum value is reached?
>>
>> Many thanks in advance
>> Lee.
>>
>>
>

Maximum value a datetime datatype can hold

Hi
Could anyone tell me what is the maximum value a datetime datatype can hold
in SQL Server 2005.
ThanksGT wrote on Mon, 12 Jun 2006 00:53:01 -0700:
> Hi
> Could anyone tell me what is the maximum value a datetime datatype can
> hold in SQL Server 2005.
> Thanks
BOL says December 31st 9999.
Dan|||Thanks Dan
"Daniel Crichton" wrote:
> GT wrote on Mon, 12 Jun 2006 00:53:01 -0700:
> > Hi
> >
> > Could anyone tell me what is the maximum value a datetime datatype can
> > hold in SQL Server 2005.
> >
> > Thanks
> BOL says December 31st 9999.
> Dan
>
>

Maximum value a datetime datatype can hold

GT wrote on Mon, 12 Jun 2006 00:53:01 -0700:

> Hi
> Could anyone tell me what is the maximum value a datetime datatype can
> hold in SQL Server 2005.
> Thanks
BOL says December 31st 9999.
DanThanks Dan
"Daniel Crichton" wrote:

> GT wrote on Mon, 12 Jun 2006 00:53:01 -0700:
>
> BOL says December 31st 9999.
> Dan
>
>|||Hi
Could anyone tell me what is the maximum value a datetime datatype can hold
in SQL Server 2005.
Thanks|||GT wrote on Mon, 12 Jun 2006 00:53:01 -0700:

> Hi
> Could anyone tell me what is the maximum value a datetime datatype can
> hold in SQL Server 2005.
> Thanks
BOL says December 31st 9999.
Dan|||Thanks Dan
"Daniel Crichton" wrote:

> GT wrote on Mon, 12 Jun 2006 00:53:01 -0700:
>
> BOL says December 31st 9999.
> Dan
>
>

Maximum Value

Hey guys, I have this query which tries to get the maximum values from a table.

Code Snippet

SELECT scbcrse_subj_code,

MAX(scbcrse_eff_term), scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code

FROM Courses

WHERE scbcrse_csta_code = ''A''

GROUP BY scbcrse_subj_code, SCBCRSE_CRSE_NUMB, scbcrse_coll_code, scbcrse_csta_code, scbcrse_title

ORDER BY scbcrse_subj_code

Sample Table

scbcrse_subj_code scbcrse_eff_term scbcrse_crse_numb scbcrse_coll_code scbcrse_title ACCT 200620 4010 SB Advanced Accounting ACCT 200530 4010 SB Financial Accounting IV

Now, there is a column which is called scbcrse_title which shows the title of the course, which in this case on the table the titles are different. One is called Advanced Accounting and the other is called Financial Accounting IV. My question is, how can I only show the highest number on the scbcrse_eff_term? The problem occurs when there are different titles.

Does scbcrrse_title need to be part of the group by? Can you just group on crse_numb, coll_code, and subj_code only ? Then subselect the title using the three group by values where eff_term is max? Something like the below (i did not check the code for errors sorry)

Code Snippet

SELECT C.scbcrse_subj_code,

C.scbcrse_crse_numb,

C.scbcrse_coll_code,

C.scbcrse_csta_code,

MAX(C.scbcrse_eff_term),

(SELECT scbcrse_title

FROM Courses

WHERE scbcrse_subj_code = C.scbcrse_subj_code

AND scbcrse_crse_numb = C.scbcrse_crse_numb

AND scbcrse_coll_code = C.scbcrse_coll_code

AND scbcrse_csta_code = C.scbcrse_csta_code

AND scbcrse_eff_term) = (SELECT MAX(C.scbcrse_eff_term) FROM Courses WHERE C.Cscbcrse_csta_code = 'A' GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code ) AS [Title]

FROM Courses C

WHERE C.Cscbcrse_csta_code = 'A'

GROUP BY C.scbcrse_subj_code, C.SCBCRSE_CRSE_NUMB, C.scbcrse_coll_code, C.scbcrse_csta_code

ORDER BY scbcrse_subj_code

|||Thanks Dave, I'll check the code to see if it works. And yes, the GROUP BY command does force me to include scbcrse_title.

|||

Maybe this:

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Diango,

You can do this without a GROUP BY as follows:

SQL Server 2000

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code = ''A''
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

In other words, choose all rows for Courses where the table does not contain a later (measured by eff_term) row with the same (subj_code, crse_numb, coll_code) combination. This assumes you want one result row for each (subj_code, crse_numb, coll_code) combination, and you can adjust the inner WHERE clause if this is not the set of columns that you want one row for each combination of.

In SQL Server 2005, you have another option:

SQL Server 2005

WITH Courses_ranked AS (
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code,
RANK() OVER (
PARTITION BY scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code
ORDER BY scbcrse_eff_term DESC
) AS rk
)
SELECT
scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb,
scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses_ranked
WHERE rk = 1

Both solutions will return the "latest" row (or rows in the case of ties for latest) for each combination (scbcrse_subj_code, scbcrse_crse_numb, scbcrse_coll_code) in the table.

Steve Kass
Drew University
http://www.stevekass.com
|||The first solution almost worked but it had the problem that when there was a code that only had one value, it got omitted. The second solution I have never done before so I'm having some problems implementing it.|||I notice I left the = 'A' out of the inner query in the first solution. That could be it, but it could also be something about your data that you didn't mention. If you post some sample data and your adapted query that fails, I can take a look.

SK
|||That worked, you rock! As soon as I added the ''A'' in the inner join, it came back with the correct result.|||Steve, I don't mean to be a pain in the ass, but I have a new issue. I'm still a bit of a noob so bare with me. Your code worked great by the way, it did the job as intended. New update is required which I wasn't aware. I want to take into account those courses that do have '' I '' as well as the maximum value on the scbcrse_eff_term. I can do this easily by simply removing the WHERE clause where scbcrse_csta_code = ''A'' on both occasions. The thing is, for those courses that have the maximum value which have an '' I '' I need those courses removed completly. ( I don't mean delete those records that '' I '' , I just mean Omitting them some how.|||I hope I'm understanding, because you didn't give any specific example.

You want to see the latest row for each (subject code, course number, college code) from among those rows with csta code either 'A' or 'I', only if that latest row happens to be one of the 'A' rows. Note that if 'A' and 'I' are the only possible values of csta_code, you don't have to say WHERE scbcrse_csta_code IN ('A','I'). I think this will do it.

SELECT scbcrse_subj_code, scbcrse_eff_term, scbcrse_crse_numb, scbcrse_coll_code, scbcrse_title, scbcrse_csta_code
FROM Courses
WHERE scbcrse_csta_code IN ('A','I')
AND NOT EXISTS (
SELECT * FROM Courses AS C
WHERE C.scbcrse_subj_code = Courses.scbcrse_subj_code
AND C.scbcrse_crse_numb = Courses.scbcrse_crse_numb
AND C.scbcrse_coll_code = Courses.scbcrse_coll_code
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
AND C.scbcrse_csta_code IN ('A','I')
)
AND Courses.scbcrse_csta_code = 'A'
ORDER BY scbcrse_subj_code
|||

Sorry, I should have explained myself a little better. Ok, this is your code:

AND C.scbcrse_csta_code = ''A''
AND C.scbcrse_scbcrse_eff_term > Courses.scbcrse_eff_term
)
ORDER BY scbcrse_subj_code

From the original code, we had a clause WHERE = ''A''. By having those two clauses, it returned the top value from scbcrse_eff_term Where scbcrse_csta_code = A, agreed? I found out later that the original requirement was wrong. The correct requirement was, get the highest scbcrse_eff_term as long as the class is active, or otherwise known as having a csta_code of ''A''. I know it sounds the same but let me explain further.

For example:

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

From the original code, it should return the second row with a value of 200023 because it's the highest row that has a csta_code of ''A''. Here is where it changes, on the example, since the highest value for the class is 221457 and has a csta_code of '' I '' the class has become inactive and therefore ACCT 4041 must no longer show up in our query. So in essence, every class that has the highest scbcrse_eff_term with a value of csta_code '' I '' should not show up at all in our query return. So ACCT 4041 would not show up on our list.

|||

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||Dale, that's not working for me. I'm getting some errors when I try to implement it. I get the error of it's not a group by function. I also think that the code would return the maximum value of the courses that have a csta_code value of ''A'', not taking into account '' I ''. Which I do want to take into account '' I '' , but if the maximum scbcrse_eff_term of a course has a csta_code of '' I '' the class should be omitted completely from the result. It's not just eliminating all the '' I '', but it's eliminating all the courses from the list that has a maximum scbcrse_eff_term with scbcrse_csta_code of '' I ''. I don't know if that makes sense to you.|||

See if this is better.

I forgot the group by in the subquery.

This will gather up all the courses with the most current date, then only keep the ones that are A.

My understanding from what you've been saying is that you want to disregard all entries for the class if its current row is an I.

This should do just that.

Code Snippet

SELECT scbcrse_subj_code,

scbcrse_eff_term,

scbcrse_crse_numb,

scbcrse_coll_code,

scbcrse_title,

scbcrse_csta_code

FROM Courses c

inner join

(

SELECT scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code,

MAX(scbcrse_eff_term) as scbcrse_eff_term

FROM Courses

GROUP BY scbcrse_subj_code,

scbcrse_crse_numb,

scbcrse_coll_code

) selhigh

on c.scbcrse_subj_code = selhigh.scbcrse_subj_code

and c.scbcrse_crse_numb = selhigh.scbcrse_crse_numb

and c.scbcrse_coll_code = selhigh.scbcrse_coll_code

and c.scbcrse_eff_term = selhigh.scbcrse_eff_term

WHERE c.scbcrse_csta_code = 'A'

|||

It doesn't bring back the desired result. Let me show you what I mean.

scbcrse_subj_code scbcrse_crse_numb scbcrse_eff_term scbcrse_csta_code

ACCT 4041 195600 A

ACCT 4041 200023 A

ACCT 4041 221457 I

This is an example of a class that has gone inactive. This table has been poorly designed, which is why it's so challenging to create the proper effect. As you can see from this class, it has become inactive. When we run your query, it returns for example ACCT 4041 200023 and a code of A. The desired result is that this course doesn't come back at all because the maximum value for it is 221457 and because it has a scbcrse_csta_code of '' I ''.

Steve's code is great because it really can eliminate duplicates and it also brings the highest value of a course no matter what csta_code it has, because I removed the two WHERE clauses. Now, from the result set, I need to remove the courses that have a maximum value with a csta_code of '' I '' combination.

Maximum value

Hello!
What's the maximum value of Money field?
Thanks
JamesHi
From Books Online:
Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1
(+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary
unit.
John
"James T." <gimenei@.hotmail.com> wrote in message
news:eI4Q7$cPFHA.2824@.TK2MSFTNGP10.phx.gbl...
> Hello!
> What's the maximum value of Money field?
> Thanks
> James
>|||Thanks John!

> Hi
> From Books Online:
> Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 -
> 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a
> monetary unit.
> John

Wednesday, March 7, 2012

Maximum allowed value for integer in Analysis Services

Is there a maximum number up to which Analysis Services will do a SUM up to.

The reason I ask is that I get a negative number when I run the following MDX ([Mesaure] is aggregated with SUM)

SELECT [Measures].[Measure] ON AXIS(0)
FROM [Cube]

However each row in my fact table has a positive (and quite large value) for its Measure.

Thanks in advance

Stephen

Yes. There a maximun value of integer, I believe its just over 2 billion or so. When you first built your cube, AS defaulted your measure to the datatype in your source. You will have to change the datatype to a bigint, then you won't see those negative values.

Van Dieu

|||Sweet. Bigint it is. Don't know why it never occured to me to change the datatype of the Measure in Analysis Services.

Oh well, the simple things.

Saturday, February 25, 2012

MAX_LENGTH Field does not match the value displayed?

Hi,

I wonder if anyone can help. I have installed the adventureWorksDW and when I run this query

select * from sys.all_columns where object_id = 85575343 and column_id = 2

The max_length filed value is 50 but if I view the column in explorer view the max_length is 25 I.e. ProductAlternateKey(nvarchar(25) ,null)

I don;'t know if I am looking at the correct table value?

Hi METAJOB,

nvarchar [ ( n | max ) ]

Variable-length Unicode character data. n can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes. The storage size, in bytes, is two times the number of characters entered + 2 bytes. The data entered can be 0 characters in length. The SQL-2003 synonyms for nvarchar are national char varying and national character varying.

tosc

|||

AdventureWorks is created using unicode (nchar, nvarchar, ntext).

Each 'single' character requires TWO bytes for storage. Therefore a nvarchar(25) field will require 50 bytes for storage. Max_length reports the actual storage size in bytes.

Max/Min

I can easily get the max and min values of a dataset, but I want to get
another value in the record associated with that max or min. For example, I
am using a stored procedure to get average number of minutes per day of
week. I would want to get the min value, plus the day of week associated
with that min value. How would I go about that in reporting services?
Thanks.Using SQL:
SELECT ShipCity, 'Minimum Freight' = MIN(Freight)
FROM northwind..orders
GROUP BY ShipCity
ORDER BY 1
Using RS: See the rdl attached below that runs against the local Northwind
database.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnathon Dupuis" <johnathon_dupuis_no_spam@.tsged-removeme.com> wrote in
message news:uzcKK3SiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> I can easily get the max and min values of a dataset, but I want to get
> another value in the record associated with that max or min. For example,
I
> am using a stored procedure to get average number of minutes per day of
> week. I would want to get the min value, plus the day of week associated
> with that min value. How would I go about that in reporting services?
> Thanks.
>
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Ship City</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Minimum Freight</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<DataSetName>Northwind</DataSetName>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
</TableColumns>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ShipCity">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>ShipCity</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ShipCity.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<Format>C</Format>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Min(Fields!Freight.Value,
"table1_Group1")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ShipCity.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!ShipCity.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
</TableGroup>
</TableGroups>
</Table>
</ReportItems>
<Style />
<Height>0.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>2.875in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="ShipCity">
<DataField>ShipCity</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT ShipCity, Freight
FROM Orders</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>|||The sample report at the end of this posting should be close to what you
need.
The key idea here is to test if the current field matches your minimum. If
that is true the display the associated field:
=iif(Fields!OrderTotal.Value = MIN( Fields!OrderTotal.Value),
Fields!OrderDate.Value, "")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Johnathon Dupuis" <johnathon_dupuis_no_spam@.tsged-removeme.com> wrote in
message news:uzcKK3SiEHA.1280@.TK2MSFTNGP09.phx.gbl...
> I can easily get the max and min values of a dataset, but I want to get
> another value in the record associated with that max or min. For example,
I
> am using a stored procedure to get average number of minutes per day of
> week. I would want to get the min value, plus the day of week associated
> with that min value. How would I go about that in reporting services?
> Thanks.
>
ShowAssociatedFieldBasedOnMin.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>0.75in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Order Total</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>If Min Order Show Order Date</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="OrderID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>OrderID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="OrderTotal">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>OrderTotal</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!OrderTotal.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=iif(Fields!OrderTotal.Value = MIN(
Fields!OrderTotal.Value), Fields!OrderDate.Value, "")</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>3a58aeb0-8249-40c7-a202-4970a3e67640</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="OrderTotal">
<DataField>OrderTotal</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 Orders.OrderID, [Order
Details].UnitPrice, [Order Details].Quantity, Orders.OrderDate,
[Order Details].UnitPrice * [Order Details].Quantity
AS OrderTotal
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID
ORDER BY OrderTotal</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>663ad6a0-1d1d-4fd1-a456-dde00c15a5f1</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Monday, February 20, 2012

max(field) but the field value is not always numeric

I need to find the highest number in one column. Here is a catch, the column
type is varchar and some of the rows will have non number character in it. I
am trying to find the highest number ignoring any row that is not a numeric
value. Here is my query.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
Thanks for any input.You may want to cast the column as an int within the max
SELECT max(cast(item_abrv as int)) as mx from item where
isnumeric(item_abrv) = 1
"UGH" <nospam@.noSPam.com> wrote in message
news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> I need to find the highest number in one column. Here is a catch, the
column
> type is varchar and some of the rows will have non number character in it.
I
> am trying to find the highest number ignoring any row that is not a
numeric
> value. Here is my query.
>
> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> Thanks for any input.
>|||I tired that and I got an error. its said converting the varchar value
'100ISBN' to a column of data type int. I just need the query to leave out
any field that has non numeric characters in it.
Thanks.
"Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> You may want to cast the column as an int within the max
>
> SELECT max(cast(item_abrv as int)) as mx from item where
> isnumeric(item_abrv) = 1
> "UGH" <nospam@.noSPam.com> wrote in message
> news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> I need to find the highest number in one column. Here is a catch, the
> column
>> type is varchar and some of the rows will have non number character in
>> it.
> I
>> am trying to find the highest number ignoring any row that is not a
> numeric
>> value. Here is my query.
>>
>> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> Thanks for any input.
>>
>|||What is wrong with IsNumeric()?
http://www.aspfaq.com/show.asp?id=2390
AMB
"UGH" wrote:
> I tired that and I got an error. its said converting the varchar value
> '100ISBN' to a column of data type int. I just need the query to leave out
> any field that has non numeric characters in it.
> Thanks.
> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
> >
> > You may want to cast the column as an int within the max
> >
> >
> > SELECT max(cast(item_abrv as int)) as mx from item where
> > isnumeric(item_abrv) = 1
> >
> > "UGH" <nospam@.noSPam.com> wrote in message
> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
> >> I need to find the highest number in one column. Here is a catch, the
> > column
> >> type is varchar and some of the rows will have non number character in
> >> it.
> > I
> >> am trying to find the highest number ignoring any row that is not a
> > numeric
> >> value. Here is my query.
> >>
> >>
> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
> >>
> >> Thanks for any input.
> >>
> >>
> >
> >
>
>|||That was it and I modified my query to do this.
SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1 and
item_abrv not like '%e%' and item_abrv not like '%d%'
Thanks for your help.
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:E52CDDD8-D5ED-49EA-B6B1-FAA39EC0D07C@.microsoft.com...
> What is wrong with IsNumeric()?
> http://www.aspfaq.com/show.asp?id=2390
>
> AMB
> "UGH" wrote:
>> I tired that and I got an error. its said converting the varchar value
>> '100ISBN' to a column of data type int. I just need the query to leave
>> out
>> any field that has non numeric characters in it.
>> Thanks.
>> "Armando Prato" <aprato@.REMOVEMEkronos.com> wrote in message
>> news:%23V0WT9UXFHA.3228@.TK2MSFTNGP10.phx.gbl...
>> >
>> > You may want to cast the column as an int within the max
>> >
>> >
>> > SELECT max(cast(item_abrv as int)) as mx from item where
>> > isnumeric(item_abrv) = 1
>> >
>> > "UGH" <nospam@.noSPam.com> wrote in message
>> > news:%23nEN%23vUXFHA.2768@.tk2msftngp13.phx.gbl...
>> >> I need to find the highest number in one column. Here is a catch, the
>> > column
>> >> type is varchar and some of the rows will have non number character in
>> >> it.
>> > I
>> >> am trying to find the highest number ignoring any row that is not a
>> > numeric
>> >> value. Here is my query.
>> >>
>> >>
>> >> SELECT max(item_abrv) as mx from item where isnumeric(item_abrv) = 1
>> >>
>> >> Thanks for any input.
>> >>
>> >>
>> >
>> >
>>

Max Worker Threads setting in X64 system.

Hi All,

As we know, the default max worker thread in SQL 2005 is 0. The default value for Max worker threads, 0, allows SQL Server to automatically configure the number of worker threads AT STARTUP.

http://msdn2.microsoft.com/en-us/library/ms187024.aspx

However, what's the actual max worker thread while the "max worker thread" option is configured as 0? Is it the one list in this table?

Number of CPUs

32-bit computer

64-bit computer

<= 4 processors

256

512

8 processors

288

576

16 processors

352

704

32 processors

480

960

This table is got form the above article. Please kindly help me confirm

This is the formula that SQL uses is it is at 0.

x86

<= 4 procs = 256

> 4 procs = 256+((# procs-4)*8)

x64

<= 4 procs = 512

> 4 procs = 512+((# procs-4)*16)

It is also wise to set this back to 0 if it is an upgrade from 2000.

|||Thanks a lot for your confirmation and formula!

max value of table column

Hi all,
Could anyboyd tell me how i can get the max value of a table column.
A column in my table contains numeric values. in my table footer i
want to display
the max value of that column.
my table itself has one group. This group is called GroupByHour and is
simply a group by hour for all 24 hours in a day (from 00h until 23h).
In the specific column of each line i calculate a value.
It is the max value of those calculated values that i want in the
footer of this column.
I don't know if i explained myself clearly enough, so here is an
example:
hour | ColA | ColB | ColC | ColD
---
00 15 14 16 16
01
02
03 20 23 21 23
...
23 75 16 55 75
----
90 51 114 75
In the tablefooter of ColA, ColB and ColC you have the sumed value for
the columns.
The footer of ColD displays the max value of ColD.
So, what expression or function do i have use to get the max value of
ColD in my table footer?
Greetings
VinnieWon't the "Max( Fields!ColD.Value )" work?
There is a Max for 2 numbers, and a max for column values. Look in 'Common
Functions -> Operators'
//Andrew
> Hi all,
> Could anyboyd tell me how i can get the max value of a table column.
> A column in my table contains numeric values. in my table footer i
> want to display
> the max value of that column.
> my table itself has one group. This group is called GroupByHour and is
> simply a group by hour for all 24 hours in a day (from 00h until 23h).
> In the specific column of each line i calculate a value.
> It is the max value of those calculated values that i want in the
> footer of this column.
> I don't know if i explained myself clearly enough, so here is an
> example:
> hour | ColA | ColB | ColC | ColD
> ---
> 00 15 14 16 16
> 01
> 02
> 03 20 23 21 23
> ...
> 23 75 16 55 75
> ----
> 90 51 114 75
> In the tablefooter of ColA, ColB and ColC you have the sumed value for
> the columns.
> The footer of ColD displays the max value of ColD.
> So, what expression or function do i have use to get the max value of
> ColD in my table footer?
> Greetings
> Vinni|||On Aug 8, 6:24 pm, Andrew Backer <awbac...@.gmail.com> wrote:
> Won't the "Max( Fields!ColD.Value )" work?
> There is a Max for 2 numbers, and a max for column values. Look in 'Common
> Functions -> Operators'
> //Andrew
>
> > Hi all,
> > Could anyboyd tell me how i can get the max value of a table column.
> > A column in my table contains numeric values. in my table footer i
> > want to display
> > the max value of that column.
> > my table itself has one group. This group is called GroupByHour and is
> > simply a group by hour for all 24 hours in a day (from 00h until 23h).
> > In the specific column of each line i calculate a value.
> > It is the max value of those calculated values that i want in the
> > footer of this column.
> > I don't know if i explained myself clearly enough, so here is an
> > example:
> > hour | ColA | ColB | ColC | ColD
> > ---
> > 00 15 14 16 16
> > 01
> > 02
> > 03 20 23 21 23
> > ...
> > 23 75 16 55 75
> > ----
> > 90 51 114 75
> > In the tablefooter of ColA, ColB and ColC you have the sumed value for
> > the columns.
> > The footer of ColD displays the max value of ColD.
> > So, what expression or function do i have use to get the max value of
> > ColD in my table footer?
> > Greetings
> > Vinnie- Hide quoted text -
> - Show quoted text -
Hi Andrew,
I am aware of the Max function, but i cannot specify the column.
ColD is just a title of the column. In fact the name for the column is
TableColumn15
But if i do =max(Fields!TableColumn15.value) i get an error returned :
"Error 1 [rsFieldReference] The Value expression for the textbox
'textbox168' refers to the
field 'TableColumn15'. Report item expressions can only refer to
fields within the current data set scope or,
if inside an aggregate, the specified data set scope."
Is there a way to say to RS that you want the max value of all values
in a specific column.
I cannot calculate this value in my footer because the group contains
24 max values (one for each hour),
and in the report footer i would have the max value of the total of
the group (which i don't need of course).
So .. anybody have some more info on this ?
Greetings
Vinnie

Max value of table column

hello,

i have a table with different columns

my table has 1 group (group per hour)

my last column of my table is a calculation of different values from that line.

in my footer of my column i want to display the max value of that column.

somehow i can't manage to achieve this.

this i an example of my table (how i want i to look like)

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

ColD is the max value of ColA, ColB and ColC.

The footer contains the sumed values for A, B and C

In the footer for ColD i would like to display the max value of ColD (in this case 75).

This may be a stupid question, but i'm stuck on it ..

Vinnie

Here the query,

Code Snippet

Create Table #data (

[hour] Varchar(100) ,

[ColA] int ,

[ColB] int ,

[ColC] int ,

[ColD] int

);

Insert Into #data Values('00','15','14','16',NULL);

Insert Into #data Values('03','20','21','23',NULL);

Insert Into #data Values('23','55','16','75',NULL);

Select

hour,

max(case when colid=1 Then case when hour is not null then data1 else data2 end end) as [cola],

max(case when colid=2 Then case when hour is not null then data1 else data2 end end) as [colb],

max(case when colid=3 Then case when hour is not null then data1 else data2 end end) as [colc],

max(case when colid=4 Then data1 end) as [cold]

from

(

select

hour,

Isnull(id,4) colid,

Max(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data1,

Sum(Case When id=1 Then cola

When id=2 Then colb

When id=3 Then colc End) data2

from

#data

cross join

(select 1 id Union Select 2 Union Select 3) as d

Group By

hour,id

With Cube

) as Data

Group by

hour

Order By

isnull(hour,99999)

|||

Hi,

my problem is with a table in reporting services.

my data already comes from a dataset.

so my question is how to retreive the max value of a table column.

also, i work with reporting models and not with query based datasets.

but that has nothing to do with my question.

Greetings

Vinnie

|||

How much of this table have you implemented successfully? Everything but the last value at the bottom right?

Are you using report builder or Business Intelligence Studio?

|||

Hello Greg,

Thanks for the answer.

I am using visual studio to build my reports.

The table itself works fine. The only thing i cannot accomplish is to

have the max value of that specific table column in my footer.

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

any help is welcome.

Greetings

Vinnie

|||

VSempoux wrote:

Hello Greg,

Is what i'm asking impossible to do or am i that stupid that it's right under

my nose and cannot figure it out ?

Neither.

VSempoux wrote:

also, i work with reporting models and not with query based datasets

I don't understand this. So there is no query to generate your report? You said you weren't using report builder, so how could you be using reporting models?

We have to understand how you are going about this before we can help you. The only way I know of how to get that max value is to write it into a query....

|||

Okay Greg,

Let me clarify my situation.

I am writing reports for an application that is in dev for the moment.

We want to give our customers the opportunity to also create their own report, based on the data that

we use in our standard reports that are supplied with the app.

So, in order to don't have to do the work twice, i am making report models that i use in my reports.

With VS or BIS you can choose your original datasource, so in my case it is a report model.

My orignal report models are based on queries that i have put into views.

So in my report model data source view i select all the data from the sql view and from that i generate

my model. Afterwards i add some calculated fields in the model itself, if i have the need for them.

Then in the report itself i define datasets.

But for my datasets i can only do drag and drop the fields (enteties) i need in my report.

Of course i can define filters for the datasource.

Does this give you a better insight into my situation ?

If it would make your life easier, i could sent you some screenshots ...

Greetings

Vinnie

|||

VSempoux wrote:

My orignal report models are based on queries that i have put into views.

Then I suppose the answer is you need to create a field in the model with a query that selects the max of the dataset that produces ColA, ColB and ColC.

When I say dataset here, I am not talking about the SQL dataset. I am talking about the collection of data. It really makes no difference whether it comes from a dataset, a view, or a donkey for that matter.

hour | ColA | ColB | ColC | ColD

00 15 14 16 16

03 20 21 23 23

....

23 55 16 75 75

90 51 114 75

|||

Hi,

I already have such a field and it's doing what it's suposed to do.

But this field is set into the table group (remember the group that groups per hour)

So, when my table is displayed after generation, i have 24 lines with in the last column the max values

of several columns.

example :

this is how my table layout looks like

table group= hour(Fields!TimeValue.value) -> this groups all my data per hour

table group header contains 7 colums

column 1 : hour(Fields!TimeValue.value) sorted ascending

column 2-3-4-5-6 contains a calculation like this : (sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value))*100

this returns a ratio value

column 7 contains an expression that gives me max value of column 2-3-4-5. this is the expression i use :

=CInt(math.Max((sum(Fields!CountTest1.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest2.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest3.Value)/sum(Fields!CountTotalTest.Value)*100), math.Max((sum(Fields!CountTest4.Value)/sum(Fields!CountTotalTest.Value)*100), (sum(Fields!CountTest5.Value)/sum(Fields!CountTotalTest.Value)*100))))))

This expression returns the highest ratio value for a specif hour.

At the end i have 24 values in column 7, each time the highest value per hour.

In my table footer i cannot use the expression above, because this will calculate the highest value for the total 24 hours (which is of course different from the highest single value per hour).

Do you see now what i want to do ?

Since i do various other calculations inside my report with fields like Fields!CountTest1.value, .... i cannot do this (i think) in my sql view.

Fields!CountTest1 is also a calculated field within my dataset (=iif(Fields!TestState.Value=101 and Fields!ContactTotalTest.Value=1,1,0))

Soooooo, the only simple thing i want ... the max value, of the generated max values per hour, in my table footer !

I don't know how i can make this more clear.

Greetings

Vinnie

|||See this post:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2021871&SiteID=1&mode=1

Ian

Wednesday, March 21, 2012

MaxLocksPerFile

MaxLocksPerFile

MaxLocksPerFile

Monday, March 19, 2012

maximum width

Maximum value of mulitple columns

Maximum value for Unique ID

Maximum value for Unique ID

Maximum value a datetime datatype can hold

Maximum value a datetime datatype can hold

Maximum Value

Maximum value

Wednesday, March 7, 2012

Maximum allowed value for integer in Analysis Services

Saturday, February 25, 2012

MAX_LENGTH Field does not match the value displayed?

Max/Min

Monday, February 20, 2012

max(field) but the field value is not always numeric

max(field) but the field value is not always numeric

max(field) but the field value is not always numeric

Max Worker Threads setting in X64 system.

max value of table column

Max value of table column

MAX and DATEDIFF

Blog Archive

About Me