pcs at azizaz.com
pcs at azizaz.com
Thu Oct 11 21:24:50 CDT 2007
Michael, Yes, when I was suggested the trick I thought the same... won't try it on a char field though.... To fill you in with regards to You wrote: >I still don't understand how the 3rd part can actually work? >To me it evaluates to (5-3) which seems to do nothing, at all... @p2_intNumberOfDays- CASE WHEN datediff(d, CONVERT(DATETIME, @p1_ReqStartDate , 102),T1.StartDate)>=@p2_intNumberOfDays THEN @p2_intNumberOfDays ELSE datediff(d, CONVERT(DATETIME, @p1_ReqStartDate , 102),T1.StartDate) @p1_ReqStartDate is the Start Date of the requested relief position @p2_intNumberOfDays is the duration period of the requested relief position T1.StartDate is the start date of the first relief assignment / unavailability period of a Relief Staff Candidate that this Relief Staff Candidate is already committed to ... a subquery has already filtered out those Relief Staff Candidates for which there is a 'collision' with the Start Date of the Requested Period .... So the resulting integer (2) indicates in this context is the number of days a Person cannot serve in the Requested Period - so 0 means I can serve the full period, 1 I can serve the full period except the last day etc... In the subset of queried Candidate records the sort order then is by who can serve the most days of the requested period all other search parameters being equal .... and the aim of the whole query excercise is to identify the top one and offer this person the Requested Relief Positon ... the rule being if we can't get a person the whole period let's at least get a person part of the period and keep looking who can fill the remaining period .... Regards borge ---- Original message ---- >Date: Fri, 12 Oct 2007 10:17:09 +1000 >From: "Michael Maddison" <michael at ddisolutions.com.au> >Subject: Re: [dba-SQLServer] Control of SortOrder? >To: <dba-sqlserver at databaseadvisors.com> > >What a shifty little hack that is ;-))) > >To do the same with chars I guess you would need to convert them to >ints... >a = 1 >b = 2 > >Don't think there is a builtin function for this but I assume its what >SQL does in the background anyway. > >I still don't understand how the 3rd part can actually work? >To me it evaluates to (5-3) which seems to do nothing, at all... > >cheers > >Michael M > >-----Original Message----- >From: dba-sqlserver-bounces at databaseadvisors.com >[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of >pcs at azizaz.com >Sent: Thursday, 11 October 2007 10:12 PM >To: dba-sqlserver at databaseadvisors.com >Subject: Re: [dba-SQLServer] Control of SortOrder? > >Michael, > >Since the column to sort on is Integer I was suggested to use this >workaround... which works: > >ORDER BY > CASE > WHEN @p7_ReqPosID = 0 > THEN T2.HighestQualificationEnum * -1 > ELSE T2.HighestQualificationEnum > END DESC, > >..notice placement of the DESC > >But what to do if the column was a char field? > >The last CASE statement below has been tested to work ok too.. > >Regards >Borge > >---- Original message ---- >>Date: Thu, 11 Oct 2007 18:25:23 +1000 >>From: "Michael Maddison" <michael at ddisolutions.com.au> >>Subject: Re: [dba-SQLServer] Control of SortOrder? >>To: <dba-sqlserver at databaseadvisors.com> >> >>Borge, >> >>I believe you must Order By a field. BTW I'm testing in >SQL2K >> >>So, >>T3.Ranking, is fine >> >>I don't believe you can change ASC to DESC this way. >>'(CASE WHEN @p7_ReqPosID = 0 THEN ASC ELSE DESC END)' >>It wont compile for me because of the reserved words ASC & >DESC >> >>The last one is interesting, but is it doing what you think >it is? >>The case statement seems to return a #, I assumed it will >then sort by >>the column ordinal returned??? >>I couldn't actually get it to work though, it would compile >but had no >>effect on sort order. >> >>When I have complicated sort orders I would create a >computed column to >>then sort on. >>Append your 3 columns together in a way that makes sense >and sort on >>that? >> >>cheers >> >> >>Michael M >> >>Hello, >> >>SQL2005 >> >>Based on the value of an input parameter I want to control >the SortOrder >>on a particular column. >> >>Can it be done, and if so how? >> >>I've been trying with CASE statements .. no luck, I'm stuck! >> >>I've tried >> >>ORDER BY >>T2.HighestQualificationEnum (CASE WHEN @p7_ReqPosID = 0 >THEN ASC ELSE >>DESC END), T3.Ranking, >>@p2_intNumberOfDays- >> CASE >> WHEN datediff(d, CONVERT(DATETIME, >>@p1_ReqStartDate , 102),T1.StartDate)>=@p2_intNumberOfDays >> THEN @p2_intNumberOfDays >> ELSE datediff(d, CONVERT(DATETIME, >>@p1_ReqStartDate , 102),T1.StartDate) >> END >> >>(The last sortorder item that also includes a CASE statement >>works!) >> >>regards >>borge >>_______________________________________________ >>dba-SQLServer mailing list >>dba-SQLServer at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>http://www.databaseadvisors.com >> >> >>_______________________________________________ >>dba-SQLServer mailing list >>dba-SQLServer at databaseadvisors.com >>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >>http://www.databaseadvisors.com >> >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com > > >_______________________________________________ >dba-SQLServer mailing list >dba-SQLServer at databaseadvisors.com >http://databaseadvisors.com/mailman/listinfo/dba-sqlserver >http://www.databaseadvisors.com >