MAX and DATEDIFF: values

Showing posts with label values. Show all posts

Wednesday, March 28, 2012

MDAC 2.8 not accepting nulls

Hi,
My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
clustered SqlServer 2005 database. SqlServer definition says null values are
acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
accepted. The same application connecting to the same database/table from
other clients using up to MDAC 2.7 do not have any problem. Is this a bug? I
f
so any idea on ways to get around it?
Any input is appreciated.
Thanks.
--
SankarVSWhat object are you using in ADODB to update the table?
Would you mind posting some example code? I am trying to reproduce.
Thanks,
Adam
"sankarvs" wrote:

> Hi,
> My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
> clustered SqlServer 2005 database. SqlServer definition says null values a
re
> acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
> accepted. The same application connecting to the same database/table from
> other clients using up to MDAC 2.7 do not have any problem. Is this a bug?
If
> so any idea on ways to get around it?
> Any input is appreciated.
> Thanks.
> --
> SankarVS|||The application uses ODBC (System DSN) connection to the SqlServer database.
A simple insert statement fails:
insert into temp1( gf_code, gf_key ) values( :sGFCode, :sGFKey )
where gf_key is a foreign key linked to another string column
SankarVS
"Goose" wrote:
[vbcol=seagreen]
> What object are you using in ADODB to update the table?
> Would you mind posting some example code? I am trying to reproduce.
> Thanks,
> Adam
> "sankarvs" wrote:
>

MDAC 2.8 not accepting nulls

Hi,
My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
clustered SqlServer 2005 database. SqlServer definition says null values are
acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
accepted. The same application connecting to the same database/table from
other clients using up to MDAC 2.7 do not have any problem. Is this a bug? If
so any idea on ways to get around it?
Any input is appreciated.
Thanks.
SankarVS
What object are you using in ADODB to update the table?
Would you mind posting some example code? I am trying to reproduce.
Thanks,
Adam
"sankarvs" wrote:

> Hi,
> My Windows XP client machines are using MDAC 2.8 SP1 to connect to a
> clustered SqlServer 2005 database. SqlServer definition says null values are
> acceptable in foreign key columns. But MDAC 2.8 SP1 says nulls are not
> accepted. The same application connecting to the same database/table from
> other clients using up to MDAC 2.7 do not have any problem. Is this a bug? If
> so any idea on ways to get around it?
> Any input is appreciated.
> Thanks.
> --
> SankarVS
|||The application uses ODBC (System DSN) connection to the SqlServer database.
A simple insert statement fails:
insert into temp1( gf_code, gf_key ) values( :sGFCode, :sGFKey )
where gf_key is a foreign key linked to another string column
SankarVS
"Goose" wrote:
[vbcol=seagreen]
> What object are you using in ADODB to update the table?
> Would you mind posting some example code? I am trying to reproduce.
> Thanks,
> Adam
> "sankarvs" wrote:

Monday, March 26, 2012

MD5 Hashing rows?

I'm trying to implement some sort of security checking against database
modification. I'm thinking to store a list with Hash values for row sets.
i.e.
[pseudocode]
byte[] returnHash = MD5Hash( SELECT stuff FROM database WHERE junk )
[/pseudocode]
Is there a way to do an MD5 hash or equivilent without outputting the result
to a file and just hashing the file?
Is there a better way how to achieve the same goal?
Any comments appreciated. Thanks for your time!
-EdgarsEdgars Klepers wrote:
> I'm trying to implement some sort of security checking against database
> modification. I'm thinking to store a list with Hash values for row sets
.
> i.e.
> [pseudocode]
> byte[] returnHash = MD5Hash( SELECT stuff FROM database WHERE junk )
> [/pseudocode]
> Is there a way to do an MD5 hash or equivilent without outputting the resu
lt
> to a file and just hashing the file?
> Is there a better way how to achieve the same goal?
> Any comments appreciated. Thanks for your time!
> -Edgars
In SQL Server 2005 you could use the HashBytes function.
In earlier versions I think you'll have to use the .NET crypto classes
or Microsoft's COM crypto API. That means client side code or a call to
external code from SQL Server. Maybe you could write an extended proc
to do it (would require C++).
SQL Server 2000 has the CHECKSUM / BINARY_CHECKSUM functions but these
are just simple checksums not strong hashes.
David Portas
SQL Server MVP
--|||If you're using SQL Server 2000, there is an extended stored procedure
for MD5 hashing (and it's quick)
http://www.codeproject.com/database/xp_md5.asp|||markc600@.hotmail.com wrote:
> If you're using SQL Server 2000, there is an extended stored procedure
> for MD5 hashing (and it's quick)
> http://www.codeproject.com/database/xp_md5.asp
That's

. Thanks for the link.
David Portas
SQL Server MVP
--|||I did come across that. How would one put in an entire row, or more
importantly an entire row set into that function to hash?
"markc600@.hotmail.com" wrote:

> If you're using SQL Server 2000, there is an extended stored procedure
> for MD5 hashing (and it's quick)
> http://www.codeproject.com/database/xp_md5.asp
>|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> In SQL Server 2005 you could use the HashBytes function.
Beware that in SQL 2005 RTM, HashBytes returns a random value if you pass it
a NULL value. SQL Server MVP Steve Kass has filed bug about it, and the
bug has been acknolweged as fixed, although it is unknown what result
hasbytes(NULL) yields after the fix.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Exactly how will depend upon your business requirements.
At the simplest level you can concatenate the relevant columns
select dbo.fn_md5( coalesce(colA,'') + coalesce(colB,'') )
from sometable
However, this may give you unexpected collisions in that if you have
a row with, for example, colA='X' and colB='YZ' and another row with
colA='XY' and colB='Z'. It also doesn't distinguish NULLs from empty
strings.
This may be acceptable to you though.
Also consider folding all character data to upper case and
removing leading/trailing spaces.
Lots of options, you decide.
Regards.

Wednesday, March 21, 2012

May I have my attributes discretized based on my own expression?

Hi, all here.

I am just having one question about discretization of continous attributes values. Cos the current discretization methods available in SQL Server 2005 data mining engine are these 3 ones:

.......................................................................................

automatic;

equal areas;

clusters.

..........................................................................................

So how these 3 methods work respectively? I mean like clusters method, how dose it discretize the continous values?

More importantly, can we have a discretization based on our own expression? like when i have one column with values ranging from 1 to 10, may we discretize this column based on expression like: 1-3,4-6,7-10?

Thanks a lot for any guidance.

User-defined ranges are not supported.

Here are descriptions of the supported discretization methods:

· Clusters: This finds buckets by performing single-dimensional clustering on the input values using the K-Means algorithm. It uses Gaussian distributions.

· EqualAreas: This examines the distribution of values across the population and creates bucket ranges such that that the total population is distributed equally across the buckets. In other words, if the distribution of continuous values were plotted as a curve, the areas under the curve covered by each bucket range would be equal. This is useful when there are a large number of duplicate values.

· Automatic: If this is selected, we try obtaining the requested number of buckets by applying the above discretization methods in the following order: Clusters, EqualAreas. We use the first method that gets closest to the number of requested buckets.

The Clusters method use random sampling (with a sample size of 1000) so EqualAreas may be used in situations where sampling is not desirable.

|||Hi, Thanks a lot.|||

However, you can always add a calculated column to do your own discretization. For example you can add a column "AgeDisc" with the expression

CASE WHEN [Age]<20 THEN 'Under 20'
WHEN [Age] <= 30 THEN 'Between 20 and 30'
ELSE 'Over 30'
END

Of course, you will have to map any input data to these values for predictions.

|||Jamie, thanks a lot. Very helpful.

Monday, March 19, 2012

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 row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?
scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>
|||cheers

Maximum row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>|||cheers

Maximum row count of 50389 exceeded ?

Hi,
Im using VB6 to return values in an SQL 2000 table and get the following
error:
Error in ScrollBox.refresh
Maximum row count of 50389 exceeded
17713 rows have not been displayed
Does anyone know if this problem is related to SQL 2000 table limitations or
is it VB based ?
Thanks for any information.
Scott.
Maximum row count of 50389 exceeded ?scott
VB's issue
"scott" <nospamscott@.yahoo.com> wrote in message
news:uEucDictEHA.2124@.TK2MSFTNGP11.phx.gbl...
> Hi,
> Im using VB6 to return values in an SQL 2000 table and get the following
> error:
> Error in ScrollBox.refresh
> Maximum row count of 50389 exceeded
> 17713 rows have not been displayed
> Does anyone know if this problem is related to SQL 2000 table limitations
or
> is it VB based ?
> Thanks for any information.
> Scott.
> Maximum row count of 50389 exceeded ?
>
>|||cheers

Wednesday, March 7, 2012

Maximum Attribute Values

I have a procedure that detarmines the maximum current values of user table
attributes. However I don't know how to use SQL to get the maximum value of
a
datatype. Can anyone help?There's no way to get them via SQL AFAIK, but you can just look them up in
Books Online, under the specific datatypes.
Jacco Schalkwijk
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>I have a procedure that detarmines the maximum current values of user table
> attributes. However I don't know how to use SQL to get the maximum value
> of a
> datatype. Can anyone help?|||To add to Jacco's response, you can also get the min/max permissible values
for numeric types in .Net application code using constants in the
System.Data.SqlTypes namespace (e.g. SqlInt32.MaxValue).
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>I have a procedure that detarmines the maximum current values of user table
> attributes. However I don't know how to use SQL to get the maximum value
> of a
> datatype. Can anyone help?|||try looking at the system table systypes
regards,
Mark Baekdal
http://www.dbghost.com
http://www.innovartis.co.uk
+44 (0)208 241 1762
Build, Comparison and Synchronization from Source Control = Database change
management for SQL Server
"marcmc" wrote:

> I have a procedure that detarmines the maximum current values of user tabl
e
> attributes. However I don't know how to use SQL to get the maximum value o
f a
> datatype. Can anyone help?|||Thx
So you can't get them in SQL? How then does it know ehen they are exceeded?
"Dan Guzman" wrote:

> To add to Jacco's response, you can also get the min/max permissible value
s
> for numeric types in .Net application code using constants in the
> System.Data.SqlTypes namespace (e.g. SqlInt32.MaxValue).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>
>|||> So you can't get them in SQL? How then does it know ehen they are
exceeded?
because the engine is not written in tsql.|||I know that it's not recommended to query system tables directly, but isn't
the information accessible through the systypes table.
INT is listed with a length of 4 but you can easily calculate the max and
min values from this.
The problem that I see would be that Microsoft modifies this table in the
future.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ustFpMBQFHA.3544@.TK2MSFTNGP12.phx.gbl...
> There's no way to get them via SQL AFAIK, but you can just look them up in
> Books Online, under the specific datatypes.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "marcmc" <marcmc@.discussions.microsoft.com> wrote in message
> news:247D52AD-2959-493F-9DC5-E38E6F031EF7@.microsoft.com...
>|||Because an overflow exception is thrown by the engine.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E68737B1-89BA-4915-A1F0-EF816A91DF05@.microsoft.com...
> Thx
> So you can't get them in SQL? How then does it know ehen they are
> exceeded?
>
> "Dan Guzman" wrote:
>|||I know of no way to get this info in Transact-SQL. The characteristics of
built-in datatypes are hard-coded in the engine code so these don't need to
be stored as meta-data or exposed.
Hope this helps.
Dan Guzman
SQL Server MVP
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E68737B1-89BA-4915-A1F0-EF816A91DF05@.microsoft.com...
> Thx
> So you can't get them in SQL? How then does it know ehen they are
> exceeded?
>
> "Dan Guzman" wrote:
>|||Keep in mind that underneath SQL's engine is a simple check that detemrines
whether the numeric data it is trying to store is larger than the byte thres
hold
(Integer=4, SmallInt=2, TinyInt=1). It's not "storing" the maximum decimal v
alue
per se. You just have to know that the range of an integer is 2^31 to 2^31-1
.
Thomas
"marcmc" <marcmc@.discussions.microsoft.com> wrote in message
news:E68737B1-89BA-4915-A1F0-EF816A91DF05@.microsoft.com...
> Thx
> So you can't get them in SQL? How then does it know ehen they are exceeded
?
>
> "Dan Guzman" wrote:
>

Saturday, February 25, 2012

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 Values (Query)

Hi,

I have a problem to get the max values. I have table like this...

STATUS CUSTOMER NUMBER OTHER
0 000001 1 DATA1
0 000001 2 DATA2
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 1 DATA1
0 000003 2 DATA2

I need to get this

STATUS CUSTOMER NUMBER OTHER
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 2 DATA2

Please advice

Thanks

Try:

;with cte

(

select *, row_number() over(partition by customer order by number DESC) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

Here it is,

Code Snippet

Create Table #data (

[STATUS] int ,

[CUSTOMER] Varchar(100) ,

[NUMBER] int ,

[OTHER] Varchar(100)

);

Insert Into #data Values('0','000001','1','DATA1');

Insert Into #data Values('0','000001','2','DATA2');

Insert Into #data Values('0','000001','3','DATA3');

Insert Into #data Values('0','000002','1','DATA1');

Insert Into #data Values('0','000003','1','DATA1');

Insert Into #data Values('0','000003','2','DATA2');

select status,customer,max(number),max(Other) from #data

group by status,customer

|||
Ismael try this,

SELECT MainTable.*

FROM MainTable

JOIN (

SELECT CUSTOMER

, MAX(NUMBER) MaxNUMBER

FROM MainTable

GROUP BY CUSTOMER

) SQ

ON MainTable.CUSTOMER = SQ.CUSTOMER

AND MainTable.NUMBER = SQ.MaxNUMBER

MAX Values (Query)

Hi,

I have a problem to get the max values. I have table like this...

STATUS CUSTOMER NUMBER OTHER
0 000001 1 DATA1
0 000001 2 DATA2
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 1 DATA1
0 000003 2 DATA2

I need to get this

STATUS CUSTOMER NUMBER OTHER
0 000001 3 DATA3
0 000002 1 DATA1
0 000003 2 DATA2

Please advice

Thanks

Try:

;with cte

(

select *, row_number() over(partition by customer order by number DESC) as rn

from dbo.t1

)

select *

from cte

where rn = 1;

AMB

|||

Here it is,

Code Snippet

Create Table #data (

[STATUS] int ,

[CUSTOMER] Varchar(100) ,

[NUMBER] int ,

[OTHER] Varchar(100)

);

Insert Into #data Values('0','000001','1','DATA1');

Insert Into #data Values('0','000001','2','DATA2');

Insert Into #data Values('0','000001','3','DATA3');

Insert Into #data Values('0','000002','1','DATA1');

Insert Into #data Values('0','000003','1','DATA1');

Insert Into #data Values('0','000003','2','DATA2');

select status,customer,max(number),max(Other) from #data

group by status,customer

|||
Ismael try this,

SELECT MainTable.*

FROM MainTable

JOIN (

SELECT CUSTOMER

, MAX(NUMBER) MaxNUMBER

FROM MainTable

GROUP BY CUSTOMER

) SQ

ON MainTable.CUSTOMER = SQ.CUSTOMER

AND MainTable.NUMBER = SQ.MaxNUMBER

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