Query

  • jsshivalik

    Mr or Mrs. 500

    Points: 500

    Hi

    I have query

    select T0.DocType,T0.Debit , T1.Credit , T1.account from tbl1 where Mdate <= @frDate.

    In this i want to add condition if @frdate Month = 3 and Date = 31 then documents of Type say TC should not be considered

    Thanks

  • Phil Parkin

    SSC Guru

    Points: 244584

    Try adding this to your WHERE clause:

          AND NOT (
    DocType = 'TC'
    AND MONTH(@frdate) = 3
    AND DAY(@frdate) = 31
    )

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • scdecade

    SSC Eights!

    Points: 827

    Phil Parkin wrote:

    Try adding this to your WHERE clause:

          AND NOT (
    DocType = 'TC'
    AND MONTH(@frdate) = 3
    AND DAY(@frdate) = 31
    )

    Is it this?

          AND NOT (
    DocType = 'TC'
    AND MONTH(Mdate) = 3
    AND DAY(Mdate) = 31
    )

    • This reply was modified 1 month, 1 week ago by  scdecade.
  • Phil Parkin

    SSC Guru

    Points: 244584

    In this i want to add condition if @frdate Month = 3 and Date = 31 ...

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • scdecade

    SSC Eights!

    Points: 827

    Phil Parkin wrote:

    In this i want to add condition if @frdate Month = 3 and Date = 31 ...

    That's what the OP wrote but is it what they meant?  🙂  Idk

  • scdecade

    SSC Eights!

    Points: 827

    this

    declare @frDate         date='2020-03-31';

    with a_cte(DocType, Debit, Credit, Account, Mdate) as
    (select 'TC', 10, 20, 123, '2020-03-30'
    union all
    select 'TC', 10, 20, 123, '2020-03-31'
    union all
    select 'TC', 10, 20, 123, '2020-04-01'
    )
    select
    DocType, Debit, Credit, Account, Mdate
    from a_cte where Mdate <= @frDate
    AND NOT (
    DocType = 'TC'
    AND MONTH(@frdate) = 3
    AND DAY(@frdate) = 31
    );

    or this

    declare @frDate         date='2020-03-31';

    with a_cte(DocType, Debit, Credit, Account, Mdate) as
    (select 'TC', 10, 20, 123, '2020-03-30'
    union all
    select 'TC', 10, 20, 123, '2020-03-31'
    union all
    select 'TC', 10, 20, 123, '2020-04-01'
    )
    select
    DocType, Debit, Credit, Account, Mdate
    from a_cte where Mdate <= @frDate
    AND NOT (
    DocType = 'TC'
    AND MONTH(Mdate) = 3
    AND DAY(Mdate) = 31
    );
  • Jeff Moden

    SSC Guru

    Points: 996655

    I'm actually a bit concerned about the original query because it basically returns EVERYTHING from the beginning of the table up to a point in time.  That could be a whole lot of stuff and that leads to two problems...

    1. If there's a lot of rows (and you should ALWAYS plan on there being a lot of rows), even the original query could be returning way too much data for practical use.
    2. It's going to treat every occurrence of 3/31 the same way, which may be a bad thing.

    Further, modifying an already questionable query to search for Month and Day in this fashion will make the query non-SARGable and that's going to make an index seek impossible even if they do straighten out the original query.

    --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".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

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

Viewing 7 posts - 1 through 7 (of 7 total)

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