April 16, 2008 at 7:55 am
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.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 16, 2008 at 1:31 pm
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 Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
April 16, 2008 at 3:14 pm
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 int
Set @SelectionSwitches = power(2,2)
+power(2,6)
+power(2,7)
+power(2,8)
+power(2,11)
+power(2,12)
Declare @Dob datetime
Set @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
April 16, 2008 at 10:46 pm
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
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 17, 2008 at 12:15 am
Thanks, rbarry.
What was missing was on my end -- should have looked more closely:blush:. 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.
April 17, 2008 at 7:59 am
If we're looking for obscure/inefficient ways to produce the requested output:
select *
from stud_mast o
where
(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) != 10
Perhaps 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.
April 18, 2008 at 1:56 am
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
April 18, 2008 at 6:52 am
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.
April 21, 2008 at 12:48 am
hmmmmmmmm 🙂
You r right dude !!!
April 21, 2008 at 8:29 am
select * from stud_mast
join (select 2 choice union select 6 union select 7
union select 8 union select 11 union select 12) choices
where month(dob) = choice
April 22, 2008 at 5:48 pm
Well, the question was whether there is another solution. So there might be many, you just have to say, "Yes". As mentioned above, there are plenty, including "union", "negation/and", subquery, I would add CTE, etc. The answer provided is a correct one for this particular case that is rather mensa-oriented. In large scale systems, you would avoid relying on the modulo due to the fact that month list would have to be as generic as possible... As also mentioned above. However, it was fun to answer.
June 4, 2008 at 1:53 pm
rbarryyoung (4/16/2008)
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.
Fantastic solution....
very good one RBarryYoung :w00t:
June 4, 2008 at 2:07 pm
Norma Jean Claeys (4/21/2008)
select * from stud_mastjoin (select 2 choice union select 6 union select 7
union select 8 union select 11 union select 12) choices
where month(dob) = choice
I guess there should be ON condition instead of WHERE clause..;)
July 10, 2008 at 3:05 am
we can even use
charindex(',' + Convert(varchar(2),month(dob)) + ',', ',2,6,7,8,11,12,') >0
This won't have impact even if the language is not english
MayurArt
Viewing 14 posts - 31 through 43 (of 43 total)
You must be logged in to reply to this topic. Login to reply