Find the greatest of three columns per each row and display

Hi,

i have a problem where in i have to display the greatest marks scored by each student.

How can i do this? Is there any built in TSQL function.

Rgds..,

Aazad

You either use a CASE Statement or Pivot the data to make a relational query possible through the aggregation functions.

SELECT CASE WHEN COlA > COLB THEN COLA ELSE

(CASE WHEN COLB > COLC THEN COLB ELSE COLC END)

END

FROM SomeTable

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hi,

I worked. Nice one. I got the other way too..!

Rgds..,

Aazad.

Find first and last date of a month?

Afternoon all,

Just a quickie, is there an expression built into Reporting Services from which you can find the first date and last date of any given month? The first date isn't much of a problem as it isn't quite as dynamic as the last date.

I can do it in an IIf statement but wouldn't be able to make it recognise 29th February in a leap year very easily.

Thanks,

Paul

Hello Paul,

If you have the first day of the month, you can just add a month to it and subtract a day.

=DateAdd("d", -1, DateAdd("m", 1, FirstDayOfMonth))

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply, yes it helps a lot. Your solution is so simple it's brilliant.

Thanks,

Paul

|||

Phew,

Just thought I'd add this since I have proven that moving to SP2 9.0.3054 broke all my sub reports that passed a start and end date! (It blew up on the end date since the month and day were aways passed the wrong way round (i.e US when I'm en-GB)).

(No Language settings ANYWHERE fixed it, only code!)

Type in the "Code" section of report properties:

Public Function GetISODate(tmDate as DateTime)
return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
End Function

and use it on the navigation:
=Code.GetISODate(Parameters!<parameter>.Value)

It seems that the ISO formatted date (year-month-day) will be parsed the
same by every culture.

Credit goes to "Darren France", I just wished I'd assumed the DateTime object was in fact being passed as a date string!

Filtering the parameter content

Hello,

I have built a report using a Cube (and not a relational database).

I have a date as a parameter and I would like to filter its content: the parameter goes from 1900 to 2090 and I would like the user to see only from 2006 to 2090.

Can you help me by giving me tricks to do it ? There may have several ways of doing it but I can't find them.

Thank you in advance !

Alexis

If you always want it to start from 2006( not change to 2007 to 2090 when it is 2008) it′s easy in MDX:

SELECT {[Time].[Year].&[2006] : NULL } on rows, bla bla bla.

This will make it take every member from the time dimension from 2006 up til the end, in your case 2090.|||

Thank you, it works perfectly !

It would be interesting to make dynamic date range according to the actual year but.... next time !