Query

Question

Query

jsshivalik

SSChasing Mays

Points: 612
More actions
May 31, 2020 at 10:41 am

#3758950

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Try adding this to your WHERE clause:

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 2

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 4 years, 5 months ago by Steve Collins.

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246960 More actions · Answer 3

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Steve Collins SSCrazy Points: 2157 More actions · Answer 5

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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 6

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

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

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