Claculated field in Where clause

Question

Claculated field in Where clause

Hogie503

SSC-Addicted

Points: 483
More actions
January 7, 2019 at 3:40 pm

#382555

Hello,
am looking for the best, or most efficient way to implement a store procedure where I have several calculated fields and would like to use those fields in the where clause to filter on.
Any help will be greatly appreciated. Thank You..
example part of the SP:

@ID AS INT,
@Year AS SMALLINT = NULL,
@FYTD_Min DECIMAL(18,2) = NULL,
@CYTD_Min DECIMAL(18,2) = NULL,
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN
SELECT
Pt.ID
      At.Name,
      At.address,
   Pt.PayAmount,
   Pt.month,
      Pt.Year,
   CASE when (Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) THEN
       SUM(PayAmount)
      ELSE 0
    END AS FYTD,
   CASE
    WHEN (Pt.Year = @Year - 1 AND Pt.month IN (7,8,9,10,11,12) OR Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6) ) THEN
       SUM(PayAmount)
         ELSE 0
    END AS CYTD
                       FROM    Payment_Table AS Pt
    LEFT OUTER JOIN Addres_Table AS At


   WHERE        Pt.ID = @ID
          AND Pt.Year = @year
          AND ((FYTD >= @FYTD_Min) OR @FYTD_Min IS NULL)) ********
          AND ((CYTD >= @CYTD_Min) OR @CYTD_Min IS NULL)) ********

   GROUP By Pt.ID
      At.Name,
      At.address
                    Pt.PayAmount,
                                  Pt.month,
      Pt.Year

                  ***** IF the user leave the FYTD and CYTD field blank then all records are returned. If the user enter an amount then its filter base on the amount entered.
   I am not sure if you have to use CTE, Temp table or another clever method. Also how is the best way to initialize the parameters (@FYTD_Min, @CYTD_Min) for
this situation.. Thank you.

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

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

Mike01 SSChampion Points: 11381 More actions · Answer 1

If it's a stored procedure, then either @FYTD_Min and @CYTD_Min are passed in as parameters or looked up somewhere, just don't hardcode them. You could have them passed with default values (sorta hardcoding, but at least they can be changed without changing the proc. As far as the code, you will have to use a having clause and change your code. The sum will have to change too

sum(CASE when (Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) THEN PayAmount ELSE 0 END) AS FYTD, sum(CASE WHEN (Pt.Year = @Year - 1 AND Pt.month IN (7,8,9,10,11,12) OR Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6) ) THEN PayAmount ELSE 0 END) AS CYTD

then the having part having. Just keep in mind that the having clause will only be applied after the entire result set is returned, so it could be very large data set coming back. By the way, why are you doing this "AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) " ??

(sum(CASE when (Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) THEN PayAmount
ELSE 0
END) >= @FYTD_Min
or sum(CASE when (Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6,7,8,9,10,11,12)) THEN
PayAmount
ELSE 0
END) is null)
and
(
sum(CASE
WHEN (Pt.Year = @Year - 1 AND Pt.month IN (7,8,9,10,11,12) OR Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6) ) THEN
PayAmount
ELSE 0
END) >= @CYTD_Min
or
sum(CASE
WHEN (Pt.Year = @Year - 1 AND Pt.month IN (7,8,9,10,11,12) OR Pt.Year = @Year AND Pt.month IN (1,2,3,4,5,6) ) THEN
PayAmount
ELSE 0
END) is null)

For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

The problem with optional parameters like that is that they're a performance problem. Erratic performance, varying usually between moderately bad and very bad.
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Hogie503 SSC-Addicted Points: 483 More actions · Answer 3

Thanks for responding, what I am trying to do is sum up payments for individual person, I need to sum FYTD;
Example:: ( July 1st 2017 to July 30 2018) and CYTD, (January 1st 2018 to December 31st 2018), so they use
cycles for the month, they can have 13 cycles so for the calculation for CYTD we use the year and all 12 cycles(1...12)
for FYTD we need to use 6 month from one year and 6 from the other such as ( July 1st 2017 to July 30 2018)

1974lg Old Hand Points: 345 More actions · Answer 4

GilaMonster - Wednesday, January 9, 2019 12:45 PM
The problem with optional parameters like that is that they're a performance problem. Erratic performance, varying usually between moderately bad and very bad.
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/

I tend to agree with Gail.
nevertheless, in term of performance, perhaps could be a not very bad idea to use "OPTION(OPTIMIZE FOR UNKNOWN) "

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 5

1974lg - Wednesday, January 9, 2019 8:48 PM
GilaMonster - Wednesday, January 9, 2019 12:45 PM
The problem with optional parameters like that is that they're a performance problem. Erratic performance, varying usually between moderately bad and very bad.
https://sqlinthewild.co.za/index.php/2018/03/13/revisiting-catch-all-queries/
I tend to agree with Gail.
nevertheless, in term of performance, perhaps could be a not very bad idea to use "OPTION(OPTIMIZE FOR UNKNOWN) "

Doesn't help this query pattern.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Brandie Tarvin SSC Guru Points: 172918 More actions · Answer 6

My suggestion is to force the NULLs. And since you're accepting parameters, might I also suggest adding a bit of code to help you get around SQL Injection issues? You'll probably want to add something for @ID and @Year as well.

Here's a sample of what I'm talking about. Note, this probably doesn't entirely solve the performance issue Gail is talking about. But it shouldn't hurt things either.

@ID AS INT, @Year AS SMALLINT = NULL, @FYTD_Min DECIMAL(18,2) = NULL, @CYTD_Min DECIMAL(18,2) = NULL, AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; BEGIN DECLARE @FYTDMin_Set DECIMAL(18,2) = (SELECT ISNULL(@FYTD_Min,0000000000000000.00)) DECLARE @CYTDMin_Set DECIMAL(18,2) = (SELECT ISNULL(@CYTD_Min,0000000000000000.00))

....
WHERE Pt.ID = @ID 
 AND Pt.Year = @year
 AND FYTD >= @FYTDMin_Set
 AND CYTD >= @CYTDMin_Set

 GROUP By Pt.ID
 At.Name,
 At.address
 Pt.PayAmount,
 Pt.month,
 Pt.Year

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 7

Just turns it into a different performance problem. One that's always bad, rather than bad depending on parameter passed, because the rows are estimated for <column> >= NULL, which always returns 0 rows, and so the execution plan is always non-optimal instead.

Section titled "Changing Parameter Values" in https://www.red-gate.com/simple-talk/sql/t-sql-programming/how-to-confuse-the-sql-server-query-optimizer/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass