T-SQL query

  • Raghav-673586

    SSC Enthusiast

    Points: 109

    Comments posted to this topic are about the item T-SQL query

  • Vivien Xing

    SSChampion

    Points: 12297

    Very tricky! I made a guess. Yes. Anything is possible.

  • hodgy

    SSCertifiable

    Points: 5735

    you could use a case statement:

    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

    Life: it twists and turns like a twisty turny thing

  • David McKinney

    SSChampion

    Points: 10449

    Handy to know...I'm sure I'll be using that in my code very soon!

  • Knut Boehnert

    SSCrazy

    Points: 2947

    That was a very nice question for a Friday morning.

    Another solution would be:

    datepart(m,dob) between 1 and 8

    and datepart(m,dob) <> 2

    and datepart(m,dob) <> 6

    Though the single condition definitely beats this solution.

  • Kevin Gill

    SSCrazy

    Points: 2316

    Knut Boehnert (5/2/2008)


    That was a very nice question for a Friday morning.

    I'd have to disagree. I think these ones about working out pointless conditions to satisfy one off special cases that nobody will EVER use, are more about your ability (and level of interest) in working out what's different about those months than the others. As such, they have almost nothing to to with T-SQL (which was the 'Question Type') other than that they chose to write the solution in it. I imagine many people will simply guess yes (there's usually a way to do anything that starts with 'is there a way...') without really knowing why. I decided to be contrary and guessed no, based on the last (equally pointless) one of these being 'yes'.

    -- Kev

    -------------------------------
    Oh no!

  • Anipaul

    SSC-Insane

    Points: 24681

    Kevin Gill (5/2/2008)


    Knut Boehnert (5/2/2008)


    That was a very nice question for a Friday morning.

    I'd have to disagree. I think these ones about working out pointless conditions to satisfy one off special cases that nobody will EVER use, are more about your ability (and level of interest) in working out what's different about those months than the others. As such, they have almost nothing to to with T-SQL (which was the 'Question Type') other than that they chose to write the solution in it. I imagine many people will simply guess yes (there's usually a way to do anything that starts with 'is there a way...') without really knowing why. I decided to be contrary and guessed no, based on the last (equally pointless) one of these being 'yes'.

    -- Kev

    Kevin all solutions may not be the best but sometimes these type of solutions/questions shake our brains. We sometimes do monotonous work, so this solutions help us to break the shackle. We learnt something differently is also interesting. ๐Ÿ˜Ž

  • Vinay Kumar

    SSCertifiable

    Points: 6098

    You can use UNION...

    good one...;)

    Thanks
    Vinay Kumar
    -----------------------------------------------------------------
    Keep Learning - Keep Growing !!!

  • WayneS

    SSC Guru

    Points: 95392

    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.

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes


    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • ronmoses@gmail.com

    SSCarpal Tunnel

    Points: 4480

    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.

    Exactly the answer I was about to give. ๐Ÿ™‚ I figured the question had to have been misworded, it's a complete no-brainer as is.

    Ron Moses

    -----
    a haiku...

    NULL is not zero
    NULL is not an empty string
    NULL is the unknown

  • Christian Buettner-167247

    SSChampion

    Points: 13729

    As I already stated in one of the previous QODs with a similar question:

    The suggested answer does not work with languages other than english...

    Try this instead:

    WHERE '1;3;4;5;7;8' LIKE '%' + CAST(MONTH(dob) as varchar(2)) + '%'

    Best Regards,

    Chris Bรผttner

  • Bharat Shah-487869

    Grasshopper

    Points: 15

    where '_'+ convert(varchar, datepart(d, dob) ) + '_' like '%_1_3_4_5_7_8_%'

    What about this ?

  • David McKinney

    SSChampion

    Points: 10449

    Lots of different solutions.

    The only one that won't always work is the answer given.

    As a reader based in continental europe, I should point out the fact that if you're running SQL server in any language other than English, then you'll almost certainly get a different result for datepart(m,dob) like '%e%'

    Try changing the language to Italian, and see how your code runs ๐Ÿ˜‰

    SET LANGUAGE Italian

  • Jack Corbett

    SSC Guru

    Points: 184381

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

  • Sean Walker

    Right there with Babe

    Points: 756

    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).

Viewing 15 posts - 1 through 15 (of 52 total)

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