Conditional sorting

I'm attempting to sort using the following conditions:
If the score is greater or equal to passing score, sort by score
descending, test taken date descending. If score is less than passing
score, sort by test taken date descending, score descending.
Expected results:
3 95 2005-01-02 00:00:00
1 90 2005-02-02 00:00:00
2 50 2004-02-02 00:00:00
4 75 2003-03-02 00:00:00
Actual results:
2 50 2004-02-02 00:00:00
4 75 2003-03-02 00:00:00
3 95 2005-01-02 00:00:00
1 90 2005-02-02 00:00:00
How can I achieve the expected results?
Here's the SQL I've tried so far:
CREATE TABLE CourseData (
id int,
SCORE int,
TESTTAKEN smalldatetime,
PASSSCORE int
)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(1,90,'2005-02-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(2,50,'2004-02-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(3,95,'2005-01-02',80)
INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
VALUES(4,75,'2003-03-02',80)
SELECT
id,
SCORE,
TESTTAKEN
FROM
CourseData
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
DROP TABLE CourseData
TIA.
PTry,
...
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE end desc,
case when SCORE < PASSSCORE then TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN END DESC,
CASE WHEN SCORE < PASSSCORE THEN SCORE END DESC
go
AMB
"go559@.hotmail.com" wrote:

> I'm attempting to sort using the following conditions:
> If the score is greater or equal to passing score, sort by score
> descending, test taken date descending. If score is less than passing
> score, sort by test taken date descending, score descending.
> Expected results:
> 3 95 2005-01-02 00:00:00
> 1 90 2005-02-02 00:00:00
> 2 50 2004-02-02 00:00:00
> 4 75 2003-03-02 00:00:00
> Actual results:
> 2 50 2004-02-02 00:00:00
> 4 75 2003-03-02 00:00:00
> 3 95 2005-01-02 00:00:00
> 1 90 2005-02-02 00:00:00
> How can I achieve the expected results?
> Here's the SQL I've tried so far:
> CREATE TABLE CourseData (
> id int,
> SCORE int,
> TESTTAKEN smalldatetime,
> PASSSCORE int
> )
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(1,90,'2005-02-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(2,50,'2004-02-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(3,95,'2005-01-02',80)
> INSERT INTO CourseData(id,SCORE,TESTTAKEN,PASSSCORE)
> VALUES(4,75,'2003-03-02',80)
> SELECT
> id,
> SCORE,
> TESTTAKEN
> FROM
> CourseData
> ORDER BY
> CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
> CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
> DROP TABLE CourseData
>
>
> TIA.
> P
>|||Thank you for the reply, it solved my issue. However, I'm not quite
clear why it works. Using the data set I posted, I evaluated the ORDER
BY clause using your solution and mine.
Example 1:
VALUES(1,90,'2005-02-02',80)
Mine:
ORDER BY SCORE DESC, TESTTAKEN DESC
Yours:
ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
Example 2:
VALUES(2,50,'2004-02-02',80)
Mine:
ORDER BY TESTTAKEN DESC, SCORE DESC
Yours:
ORDER BY NULL DESC, TESTTAKEN DESC, NULL DESC, SCORE DESC
Since the null else clauses are ignored, then it appears that both
solutions produce the same ORDER BY clause. Would you please let me
know what I'm missing?
TIA.
P
Alejandro Mesa wrote:
> Try,
> ...
> ORDER BY
> CASE WHEN SCORE >= PASSSCORE THEN SCORE end desc,
> case when SCORE < PASSSCORE then TESTTAKEN END DESC,
> CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN END DESC,
> CASE WHEN SCORE < PASSSCORE THEN SCORE END DESC
> go
>
> AMB
>
> "go559@.hotmail.com" wrote:
>
passing|||Try with two rows, each one belonging to a diff group.
AMB
"go559@.hotmail.com" wrote:

> Thank you for the reply, it solved my issue. However, I'm not quite
> clear why it works. Using the data set I posted, I evaluated the ORDER
> BY clause using your solution and mine.
> Example 1:
> VALUES(1,90,'2005-02-02',80)
> Mine:
> ORDER BY SCORE DESC, TESTTAKEN DESC
> Yours:
> ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
> Example 2:
> VALUES(2,50,'2004-02-02',80)
> Mine:
> ORDER BY TESTTAKEN DESC, SCORE DESC
> Yours:
> ORDER BY NULL DESC, TESTTAKEN DESC, NULL DESC, SCORE DESC
> Since the null else clauses are ignored, then it appears that both
> solutions produce the same ORDER BY clause. Would you please let me
> know what I'm missing?
> TIA.
> P
> Alejandro Mesa wrote:
> passing
>|||On 10 Feb 2005 11:29:02 -0800, go559@.hotmail.com wrote:

>Thank you for the reply, it solved my issue. However, I'm not quite
>clear why it works. Using the data set I posted, I evaluated the ORDER
>BY clause using your solution and mine.
>Example 1:
>VALUES(1,90,'2005-02-02',80)
>Mine:
>ORDER BY SCORE DESC, TESTTAKEN DESC
>Yours:
>ORDER BY SCORE DESC, NULL DESC, TESTTAKEN DESC, NULL DESC
(snip)
Hi P,
Not exactly. Your ORDER BY clause is:
Now you should know that CASE is an expression that can ony result in one
datatype. Which datatype depends on the datatypes of the various THEN
clauses and the WHEN clause. For the first CASE, the datatype of THEN
SCORT is int; the datatype of TESTTAKEN is smalldatetime. According to the
datatype precedence rules, SCORE will have to be converted to
smalldatetime.
The implicit conversion of int to smalldatetime works like this: take the
value of the integer and add that number of days to 19000101. So if the
score is 60, the result after converting to smalldatetime will be 19000302
and that's the value that will be used for the sorting.
It's actually quite easy to see for yourself why your order by won't work
as you'd like it to: just add the CASE expressions from the ORDER BY to
the SELECT clause:
SELECT
id,
SCORE,
TESTTAKEN,
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END AS ordering1,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END AS ordering2
FROM
CourseData
ORDER BY
CASE WHEN SCORE >= PASSSCORE THEN SCORE ELSE TESTTAKEN END DESC,
CASE WHEN SCORE >= PASSSCORE THEN TESTTAKEN ELSE SCORE END DESC
In Alejandro's version, no different datatypes are mixed within the same
CASE expressions. Therefore, there are no implicit conversions and
everything is working as expected.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Conditional Font Weight

I need to conditionally set the font weight of my data when a calculation is equal to or less than 70. I am using the following expression in the font weight property:
=iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100 <=70, "Extra Bold", "Normal")
I get no errors when I run my report, however it does not apply the correct weith to my display. I am positive the calculation is correct since I use it in other conditions and they work fine.
Any help would be apreciated.Extra Bold maps to 800. You can check this by placing a textbox on the
design surface and setting the Font Weight to Extra Bold. Next open the code
view for the report and check the value serialized for the textbox font
weight.
=iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100
<=70, "800", "Normal")
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"CarrieWells" <CarrieWells@.discussions.microsoft.com> wrote in message
news:4AFCE118-56E0-4B52-A7B8-B8D551290D6E@.microsoft.com...
> I need to conditionally set the font weight of my data when a calculation
is equal to or less than 70. I am using the following expression in the font
weight property:
> =iif(( Fields!Answer1.Value/ Fields!SumStudents.Value)*100 <=70, "Extra
Bold", "Normal")
> =iif(Fields!Quantity.Value > 10, "Normal", "Bold")
> I get no errors when I run my report, however it does not apply the
correct weith to my display. I am positive the calculation is correct since
I use it in other conditions and they work fine.
> Any help would be apreciated.
>

Conditional Count in RDLC Expression

I'd like to put a count in a textbox but only a count where a column
doesnt equal certain values. Here is what I tried:
=Count(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
Obviously this didn't work. Can anyone point me in the right
direction?
Thanks,
JasonOn Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DCOUNT(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||On Mar 14, 3:36=A0pm, Jason Wilson <wils...@.ausrad.com> wrote:
> I'd like to put a count in a textbox but only a count where a column
> doesnt equal certain values. =A0Here is what I tried:
> =3DCount(Fields!ReportStatus.Value<>"MAILED" AND Fields!
> ReportStatus.Value<>"PRINTED" AND Fields!ReportStatus<>"TRANSMIT")
> Obviously this didn't work. =A0Can anyone point me in the right
> direction?
> Thanks,
> Jason
See if this works:
=3DSUM(IIF(Fields!ReportStatus.Value<>"MAILED" AND Fields!
ReportStatus.Value<>"PRINTED" AND Fields!
ReportStatus.Value<>"TRANSMIT",1,0))
HTH
toolman|||It did thanks

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_idHello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id

Concatenating a fully qualified name

In the code below, the DatabaseName and table_id are variables. The table_id
is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
that I can pass it to the parameter @.database. In other words, how do I
concatenate so that it will be something like this @.db.dbo.tblName:
CREATE PROCEDURE sampleProcedure
(
@.database varchar (100),
@.table_id uniqueidentifier
)
AS
SET NOCOUNT ON
DECLARE @.db varchar (100)
SELECT @.db = @.database
INSERT INTO DatabaseName.dbo.tblName
SELECT * FROM tblName where table_id=@.table_id
Hello,
You need to use Dynamic SQL to do this.After that execute the Dynamic SQL
using EXEC or SP_ExecuteSQL. Take a look int the article.
http://www.sommarskog.se/dynamic_sql.html
Thanks
Hari
"morphius" <morphius@.discussions.microsoft.com> wrote in message
news:92783839-67A2-4880-938D-699519E3A9C0@.microsoft.com...
> In the code below, the DatabaseName and table_id are variables. The
> table_id
> is OK with the @.table_id. But, how do I make DatabaseName equal to @.db so
> that I can pass it to the parameter @.database. In other words, how do I
> concatenate so that it will be something like this @.db.dbo.tblName:
> CREATE PROCEDURE sampleProcedure
> (
> @.database varchar (100),
> @.table_id uniqueidentifier
> )
> AS
> SET NOCOUNT ON
> DECLARE @.db varchar (100)
> SELECT @.db = @.database
> INSERT INTO DatabaseName.dbo.tblName
> SELECT * FROM tblName where table_id=@.table_id