Query Returning Zero

  • Good afternoon all,

    I have the query below and it returns zero but if I remove the first part of the WHERE clause, it works using the second half parameters. It also works if I search in 2014 only. The date is inserted by getDate(). Is there a reason I cannot get data?

    select count(distinct c.sn) as total_prod

    from completedUnit c

    join assignworkorder p

    on c.workorder = p.workorder

    join tlkp_item i

    on p.item = i.item

    where (MONTH(c.dateentered) >= 11 and DAY(c.dateentered) >= 23 and YEAR(c.dateentered) >= 2014)

    and (MONTH(c.dateentered) <= 01 and DAY(c.dateentered) <= 19 and YEAR(c.dateentered) <= 2015)

    Thanks,

    kabaari

  • You linked two Boolean expressions together with AND, meaning both must return TRUE in order for the WHERE clause to return TRUE.

    However, looking at the first parts already leads to contradictions.

    MONTH(c.dateentered) >= 11

    and

    MONTH(c.dateentered) <= 01

    If the first one is true, how can the second one ever be true? It's just not possible.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Koen Verbeeck (1/19/2015)


    You linked two Boolean expressions together with AND, meaning both must return TRUE in order for the WHERE clause to return TRUE.

    However, looking at the first parts already leads to contradictions.

    MONTH(c.dateentered) >= 11

    and

    MONTH(c.dateentered) <= 01

    If the first one is true, how can the second one ever be true? It's just not possible.

    The same thing applies to the Day.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • I believe that query won't work because your WHERE clause is quite contradictory.

    Basically, you're looking for where the MONTH datepart item is both >= 11 and <= 1; mathematically, that's not going to work. Ditto for the DAY datepart; it's looking for something >= 23 and <= 19, which won't work.

    The best means of doing this would be to just use the full date; for example:

    WHERE c.DateEntered >= '2014-11-23' AND c.DateEntered < '2015-01-20'

    Is there something preventing the use of a date search like that? It would be the cleanest way of doing it; note the ending date's notation, as it does a less-than search, it doesn't include the ending date, but it will include everything up to the precision of your data type to the last moment on 2015-01-19.

    Also, by calling the individual date functions on the DateEntered column, you're keeping your query from using any indexes that might be on that column, since the function has to process each value to find the ones that match your condition. Doing a conventional date search would improve performance quite a bit, assuming proper index structure.

    - 😀

  • Alvin Ramard (1/19/2015)


    Koen Verbeeck (1/19/2015)


    You linked two Boolean expressions together with AND, meaning both must return TRUE in order for the WHERE clause to return TRUE.

    However, looking at the first parts already leads to contradictions.

    MONTH(c.dateentered) >= 11

    and

    MONTH(c.dateentered) <= 01

    If the first one is true, how can the second one ever be true? It's just not possible.

    The same thing applies to the Day.

    I know, I was just too lazy to copy paste that too 😀

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • kabaari (1/19/2015)


    Good afternoon all,

    I have the query below and it returns zero but if I remove the first part of the WHERE clause, it works using the second half parameters. It also works if I search in 2014 only. The date is inserted by getDate(). Is there a reason I cannot get data?

    select count(distinct c.sn) as total_prod

    from completedUnit c

    join assignworkorder p

    on c.workorder = p.workorder

    join tlkp_item i

    on p.item = i.item

    where (MONTH(c.dateentered) >= 11 and DAY(c.dateentered) >= 23 and YEAR(c.dateentered) >= 2014)

    and (MONTH(c.dateentered) <= 01 and DAY(c.dateentered) <= 19 and YEAR(c.dateentered) <= 2015)

    Thanks,

    kabaari

    I'm guessing the WHERE clause you want would would work better as:

    WHERE CAST(c.dateentered AS DATE) BETWEEN '2014-11-23' AND '2015-01-19'



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • You have two impossible combinations in your selection criteria:

    MONTH(c.dateentered) >= 11 and (MONTH(c.dateentered) <= 01

    DAY(c.dateentered) >= 23 and DAY(c.dateentered) <= 19

    It appears you are looking for data from a date range, so you probably want something like this:

    where

    c.dateentered >= '20141123' and

    c.dateentered < '20150120'

  • Alvin and Andrew,

    Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!

    WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'

  • kabaari (1/19/2015)


    Alvin and Andrew,

    Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!

    WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'

    What is the datatype of the "dateentered" column in the actual table?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • kabaari (1/19/2015)


    Alvin and Andrew,

    Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!

    WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'

    Casting to DateTime instead of date will cause you problems with c.dateentered having values during the day on 2015-01-19 and has a value of 2015-01-09 01:02:03, for example.



    Alvin Ramard
    Memphis PASS Chapter[/url]

    All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

    For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Alvin Ramard (1/20/2015)


    kabaari (1/19/2015)


    Alvin and Andrew,

    Those both achieved my goal. I was having difficulty with querying against a getDate() Inserted value. The time is also captured at insert which had thrown some of my queries into a tizzy. Thanks!

    WHERE CAST(c.dateentered AS DATETIME) BETWEEN '2014-11-23' AND '2015-01-19'

    Casting to DateTime instead of date will cause you problems with c.dateentered having values during the day on 2015-01-19 and has a value of 2015-01-09 01:02:03, for example.

    Casting dateentered to DATE or DATETIME will make the query non-SARGable, meaning it cannot use a index for anytime except an index scan. Also, DATE was not a valid datatype in SQL Server 2005.

    Also, dates in the format '2014-11-23' and '2015-01-19' are not in an unambiguous format, meaning they can produce unexpected values or conversion errors depending on the setting of DATEFORMAT. I prefer using the unambiguous date format YYYYMMDD.

    set dateformat ydm

    select T1 = convert(datetime,'20141123')

    select T2 = convert(datetime,'2014-11-23')

    Results:

    T1

    -----------------------

    2014-11-23 00:00:00.000

    T2

    -----------------------

    Msg 242, Level 16, State 3, Line 3

    The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

    Assuming that the column dateentered is DATETIME (or even DATE or DATETIME2), the following selection criteria of greater than or equal to start date and less then the day after the end date using the unambiguous date format YYYYMMDD is probably the easiest way to get a SARGable query.

    where

    c.dateentered >= '20141123' and

    c.dateentered < '20150120'

  • Jeff,

    It's DATETIME.

Viewing 12 posts - 1 through 11 (of 11 total)

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