Just testing for a possible spacing trick

  • --===== 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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

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

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