Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 T-SQL Query Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, April 16, 2008 7:55 AM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 FYI, it is ALWAYS possible to construct a mathematical expression that will fit ANY finite set of numbers. for instance:` WHERE (month(dob)-2) * (month(dob)-6) * (month(dob)-7) * (month(dob)-8) * (month(dob)-11) * (month(dob)-12) = 0`Works just as well. -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #485659
 Posted Wednesday, April 16, 2008 1:31 PM
 Right there with Babe Group: General Forum Members Last Login: Monday, November 24, 2014 4:29 PM Points: 752, Visits: 920
 There are almost always multiple ways of doing something. They would both be graceless and inelegant but you could use a series of union all statements and you can also do a join with another table or table like object (table variable, view, etc) which contained the desired months. ---Timothy A WisemanSQL Blog: http://timothyawiseman.wordpress.com/
Post #485926
 Posted Wednesday, April 16, 2008 3:14 PM
 Ten Centuries Group: General Forum Members Last Login: Monday, June 6, 2016 1:51 PM Points: 1,400, Visits: 3,059
 rbarryyoung, I don't quite follow your code, there. Somethng missing?Anyway, it did inspire what may be the most obscure solution yet. We define an integer to hold what is essentially an array of bit-switches, then test the birth month against it with a modulo-remainder. `Declare @SelectionSwitches intSet @SelectionSwitches = power(2,2) +power(2,6) +power(2,7) +power(2,8) +power(2,11) +power(2,12)Declare @Dob datetimeSet @Dob='2/24/08' Select @Dob as Dob ,case when ((@SelectionSwitches/power(2,datepart(mm,@Dob)))%2) = 1 then 'Yes' else 'No' end as Is_Selected`
Post #485994
 Posted Wednesday, April 16, 2008 10:46 PM
 SSCrazy Eights Group: General Forum Members Last Login: Tuesday, October 25, 2016 7:17 AM Points: 9,298, Visits: 9,517
 john.arnott (4/16/2008)rbarryyoung, I don't quite follow your code, there. Somethng missing?That was just the Where clause. Here is the whole query:`Select * From stud_mast WHERE ( month(dob)-2) * (month(dob)-6) * (month(dob)-7) * (month(dob)-8) * (month(dob)-11) * (month(dob)-12) ) = 0` -- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoungProactive Performance Solutions, Inc. "Performance is our middle name."
Post #486103
 Posted Thursday, April 17, 2008 12:15 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, June 6, 2016 1:51 PM Points: 1,400, Visits: 3,059
 Thanks, rbarry.What was missing was on my end -- should have looked more closely. Now it's obvious that any of the sought-after months would make one of the differences equal zero and that in turn forces the entire product to zero.
Post #486134
 Posted Thursday, April 17, 2008 7:59 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Thursday, July 22, 2010 8:59 AM Points: 110, Visits: 952
 If we're looking for obscure/inefficient ways to produce the requested output:select *from stud_mast owhere (select month( dob ) from stud_mast i where i.pk = o.pk) != 1 and (select month( dob ) from stud_mast i where i.pk = o.pk) != 3 and (select month( dob ) from stud_mast i where i.pk = o.pk) != 4 and (select month( dob ) from stud_mast i where i.pk = o.pk) != 5 and (select month( dob ) from stud_mast i where i.pk = o.pk) != 9 and (select month( dob ) from stud_mast i where i.pk = o.pk) != 10Perhaps the question should not have asked "is there any other way" (there are almost always other/worse ways) but could have asked "is there a more efficient way than.."I think if we're going to be challenged with writing different SQL, it should be to produce more useful code - not less.
Post #486445
 Posted Friday, April 18, 2008 1:56 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, February 22, 2016 8:32 AM Points: 1,274, Visits: 1,992
 Mike Dougherty (4/17/2008)If we're looking for obscure/inefficient ways to produce the requested output:...I think if we're going to be challenged with writing different SQL, it should be to produce more useful code - not less.The alternative is to start an Obfuscated T-SQL contest where entrants have to write an obscure query either to acheive a given result or which people have to work out what it does without actually running it.A couple of us (briefly) tried to think of a scenario where the number of characters in the month of a student's date of birth would be significant and hence the query would be appropriate, but couldn't come up with a beleivable one.The only idea that came close was printing the dates on certificates (or similar) where someone needed to decide which ones would need to be offset by half a character in order to center them. But it's a very contrived situation. Derek
Post #486954
 Posted Friday, April 18, 2008 6:52 AM
 SSC Journeyman Group: General Forum Members Last Login: Tuesday, October 12, 2010 5:56 AM Points: 79, Visits: 45
 Living in France, the proposed solution would not work in French, just like a previous post mentions about it not working in German. Relying on the characteristics of a specific language for a general algorithm is always a really bad idea, especially in this era of globalization.
Post #487153
 Posted Monday, April 21, 2008 12:48 AM
 Mr or Mrs. 500 Group: General Forum Members Last Login: Wednesday, April 30, 2008 4:36 AM Points: 527, Visits: 16
 hmmmmmmmm :)You r right dude !!!
Post #487722
 Posted Monday, April 21, 2008 8:29 AM
 Forum Newbie Group: General Forum Members Last Login: Sunday, November 1, 2009 11:25 AM Points: 3, Visits: 11
 select * from stud_mast join (select 2 choice union select 6 union select 7 union select 8 union select 11 union select 12) choiceswhere month(dob) = choice
Post #487999

 Permissions