Just testing for a possible spacing trick

Question

Just testing for a possible spacing trick

SSC Guru

Points: 1004323

March 30, 2021 at 3:29 am

--===== These are the parameters that we'd use in a stored procedure (without the preassignments).
DECLARE  @BeginDate DATE            = '20200601'
        ,@EndDate   DATE            = '20200630'
        ,@client    NVARCHAR(100)   = 'Client#5' --Just an example for testing.
;
   WITH cteDry AS
(--==== Do all the previously repetative calculations that did the "diff" in a cte so we
     -- "Don't Repeat Yourself".  This KISSes the code (Keep It Super Simple).
     -- This also seriously reduces the number of rows we have to worry about early.
 SELECT  Call_ID
        ,[Date]
        ,Agent
        ,Client
        ,CallTimeSec = ctsec.DurSec
        ,TalkTimeSec = ttsec.DurSec
        ,DiffSec     = ctsec.DurSec-ttsec.DurSec
   FROM #TestTable
  CROSS APPLY dbo.itvfGetSeconds(Call_Time) ctsec
  CROSS APPLY dbo.itvfGetSeconds(Talk_Time) ttsec
  WHERE [Date] >= @BeginDate AND [Date] <= @EndDate
    AND Client = @Client
)
 SELECT *
        ,Analysis = CASE --CASE conditions execute in the order given by the WHEN's 
                         --so doing this in reverse-order greatly simplifies the code.
                    WHEN DiffSec >= 600 THEN 6
                    WHEN DiffSec >= 300 THEN 5
                    WHEN DiffSec >= 190 THEN 4
                    WHEN DiffSec >=  60 THEN 3
                    WHEN DiffSec >=  10 THEN 2
                    WHEN DiffSec >=   1 THEN 1
                    ELSE 0 --This will probably never happen
                    END 
   FROM cteDry
;

--===== These are the parameters that we'd use in a stored procedure (without the preassignments).
DECLARE  @BeginDate DATE            = '20200601'
        ,@EndDate   DATE            = '20200630'
        ,@client    NVARCHAR(100)   = 'Client#5' --Just an example for testing.
;
   WITH cteDry AS
(--==== Do all the previously repetative calculations that did the "diff" in a cte so we
     -- "Don't Repeat Yourself".  This KISSes the code (Keep It Super Simple).
     -- This also seriously reduces the number of rows we have to worry about early.
 SELECT  Call_ID
        ,[Date]
        ,Agent
        ,Client
        ,CallTimeSec = ctsec.DurSec
        ,TalkTimeSec = ttsec.DurSec
        ,DiffSec     = ctsec.DurSec-ttsec.DurSec
   FROM #TestTable
  CROSS APPLY dbo.itvfGetSeconds(Call_Time) ctsec
  CROSS APPLY dbo.itvfGetSeconds(Talk_Time) ttsec
  WHERE [Date] >= @BeginDate AND [Date] <= @EndDate
    AND Client = @Client
)
 SELECT *
        ,Analysis = CASE --CASE conditions execute in the order given by the WHEN's 
                         --so doing this in reverse-order greatly simplifies the code.
                    WHEN DiffSec >= 600 THEN 6
                    WHEN DiffSec >= 300 THEN 5
                    WHEN DiffSec >= 190 THEN 4
                    WHEN DiffSec >=  60 THEN 3
                    WHEN DiffSec >=  10 THEN 2
                    WHEN DiffSec >=   1 THEN 1
                    ELSE 0 --This will probably never happen
                    END 
   FROM cteDry
;

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

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

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.