Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««12345»»

T-SQL Query Expand / Collapse
Author
Message
Posted Wednesday, April 16, 2008 7:55 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #485659
Posted Wednesday, April 16, 2008 1:31 PM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Friday, April 4, 2014 4:40 PM
Points: 751, Visits: 917
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/
Post #485926
Posted Wednesday, April 16, 2008 3:14 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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

Post #485994
Posted Wednesday, April 16, 2008 10:46 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Thursday, June 5, 2014 10:54 AM
Points: 9,294, Visits: 9,480
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: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #486103
Posted Thursday, April 17, 2008 12:15 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 28, 2014 8:53 PM
Points: 1,388, Visits: 3,039
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

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-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 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.


Post #486445
Posted Friday, April 18, 2008 1:56 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, February 18, 2014 7:14 AM
Points: 1,274, Visits: 1,983
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

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC 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

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr 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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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) choices
where month(dob) = choice
Post #487999
« Prev Topic | Next Topic »

Add to briefcase «««12345»»

Permissions Expand / Collapse