T-SQL query

  • David McKinney

    SSChampion

    Points: 10449

    Sean Walker (5/2/2008)


    Umm, it may be that my brain is fuzzy on a Fri morning, but isn't Select datename(m,'2008-07-12') = 'July' (in English) which I'm pretty sure doesn't have an "e" in it, yet 7 is one of the months listed.

    I didn't get it right, however, many people have proposed solutions that *do* work without using OR or IN, but as written the QoD answer is incorrect. (in English, it'd have even more limitations in other languages).

    always best to have a coffee before posting πŸ™‚ You're brain is still a little fuzzy.

  • Sean Walker

    Right there with Babe

    Points: 756

    You're so right, feeling very silly right now. That's it, no QoD on Friday, my brain obviously has shut down from this week.

  • Q -631159

    Ten Centuries

    Points: 1077

    Jack Corbett (5/2/2008)


    I just figured you would have a months table with the number of days in the month as each of the months mentioned have 31 days. So I would have

    Where days_in_month = 31 and month(dob) < 9.

    Jack,

    I believe April has 30 days in it.;)

    Q

    Please take a number. Now serving emergency 1,203,894

  • webrunner

    SSC-Dedicated

    Points: 30351

    WayneS (5/2/2008)


    Since the question clearly states:

    The condition should not use both IN and OR operators.

    use could use:

    where DatePart(m, ) IN (1,3,4,5,7,8)

    OR is not used, so since they BOTH aren't used, this is valid.

    you also could use:

    where DatePart(m, date) = 1

    or DatePart(m, date) = 3 ....

    as long as you don't use IN, this also would satisify the requirement not to use both.

    The question should have stated

    The condition should not use either the IN and OR operators.

    Yes, I know what was meant. But the requirements didn't adequately specify the intention.

    I agree. But I think the English phrasing should be: "The condition should not use either the IN or the OR operators." Or, alternatively, "The condition should use neither the IN nor the OR operators."

    "The condition should not use either the IN and OR operators" correctly follows the computer logic needed (for example, something like condition_used <> 'IN' AND condition_used <> 'OR'). But in English "or" (or "nor") is used for this purpose.

    Or maybe I'm using "or" too much. πŸ™‚

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • Jack Corbett

    SSC Guru

    Points: 184381

    Q (5/2/2008)


    Jack Corbett (5/2/2008)


    I just figured you would have a months table with the number of days in the month as each of the months mentioned have 31 days. So I would have

    Where days_in_month = 31 and month(dob) < 9.

    Jack,

    I believe April has 30 days in it.;)

    Oops. Guess I should be with Sean and at least avoid posting on Friday.:D The great thing is a I got the answer right without even having a correct way of doing it.:w00t:

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • GSquared

    SSC Guru

    Points: 260824

    I'd never try to use some clever "all the months without 'e' in their name", so I just took the list as having some business-rule basis, and decided that, if I needed to implement it, I could easily use a CTE, derived table, or temp table, with those numbers in it, joined to the table being queried. No "in", no "or".

    Yeah, the answer given is based on the silly "how many 2-cent stamps in a dozen", or "do they have the 4th of July in England", type process, but that doesn't make the answer I came up with technically inaccurate.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • saimsaboor

    SSC Journeyman

    Points: 81

    In the "where" Clause the "datename" function is Nondeterministic function also when we use "like" operator then query can not use indexes on that key resulted in table scan rather than index seek.

    I will prefer the Case Statement

    i.e.

    where

    case month(dob)

    when 1 then 1

    when 3 then 1

    when 4 then 1

    when 5 then 1

    when 7 then 1

    when 8 then 1

    else 0 end = 1

    In the above statement month is used which is deterministic function and this query will take part in index seek.

    Saim

  • Q -631159

    Ten Centuries

    Points: 1077

    At least some of you guys thought about it on a Friday morning. I have to admit all I did was look at the question and say yeah you can find a way to do it if you have the energy.

    GSquared,

    Can you still buy 2 cent stamps? That was even before my old bones! πŸ˜€

    Q

    Please take a number. Now serving emergency 1,203,894

  • Patrick Cahill

    SSCertifiable

    Points: 5703

    I also used a simple case statement in the where clause. Although longer than the given answer, I prefer this method.

  • Peter Bourlet

    Old Hand

    Points: 376

    This solution assumes your database is in English, if it would be in Dutch the fifth month is β€˜mei’ (has an e in it).

    However ingenious this solution is I think it is rather farfetched and makes some assumptions.

  • Terry Maher

    SSC Enthusiast

    Points: 157

    Make very sure that you always add the T-SQL statement SET LANGUAGE ENGLISH before this request.

  • Tom Garth

    SSCertifiable

    Points: 6173

    It's nice to see that I'm not the only one who is avoiding doing any real work this morning.

    TGIF

    Tom Garth
    Vertical Solutions[/url]

    "There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
  • Jack Corbett

    SSC Guru

    Points: 184381

    Tom Garth (5/2/2008)


    It's nice to see that I'm not the only one who is avoiding doing any real work this morning.

    TGIF

    It is Friday and that is international professional development day, isn't it?

    I know can we really call this professional development?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

  • Yelena Varshal

    SSC-Dedicated

    Points: 34270

    For some reason nobody mentioned as solutions:

    - cursors

    - visual basic application

    - junior developer selecting records one by one

    - Oracle administrator using his Oracle PL-SQL

    - outputting all records to the flat file, printing them and let an intern use a pensil

    and other convenient techniques

    Regards,Yelena Varsha

  • aditi.iyer

    Old Hand

    Points: 322

    [not like '%e%'] will not work as it will include January and August also πŸ™‚

Viewing 15 posts - 16 through 30 (of 52 total)

You must be logged in to reply to this topic. Login to reply