Better Query instead of loop

  • I have to get the average for open tickets during each month for the last 12 months.

    First I have to find out if the ticket was open during that period.

    I run my query with a variable and dump the data in a temp table and then query the result.

    Is there a way to run this query in one shot?

    Thank you for all the help

    ---------------------------------------------------------------------------------------

    -- Declare the variable to be used.

    DECLARE @MyCounter int,

    @dtDate DATETIME

    -- Initialize the variable.

    SET @MyCounter = 0;

    -- Test the variable to see if the loop is finished for the last 12 months.

    WHILE (@MyCounter < 12)

    BEGIN;

    -- Initialize date to current end of month

    SET @dtDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1-@MyCounter,0))

    INSERT INTO TempTable(MMMYY, [YEAR],MONTHNUMBER,[ZONE], [AVG-DAYS],[COUNT],[DESC])

    SELECT

    CONVERT(varchar(3), @dtDate )+'-'+right(CONVERT(varchar(11),@dtDate ),2) as 'MMMYY',

    YEAR (@dtDate) YEAR,

    MONTH(@dtDate) 'MONTHNUMBER',

    ZONE,

    AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)) as 'AVG-DAYS',

    COUNT(*) As [Count],

    'KPI-XYZ' as [DESC]

    FROM CTR_RECORDS

    left outer join EVENTS on CTR_EVENT=EVT_CODE

    WHERE CTR_SERVICECATEGORY='XYZ'

    and (CTR_STATUS in ('CL', 'F', 'O') OR EVT_STATUS in ('C','R', 'Z'))

    and ( ----Last Day of Previous Month ----Last Day of Current Month

    ( EVT_STATUS='C' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    OR

    ( CTR_STATUS= 'CL' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    OR

    (EVT_STATUS in ('R','Z' ) AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    OR

    ( CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    )

    group by ZONE

    set @MyCounter =@MyCounter+1

    END

    Select * from TempTable

  • Please have a look at the first link in my signature and post ready to use sample data together with your expected result.

    For sure, there is a set based solution.



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • Thanks, Lutz.

    I hope I did it right this time.

    ---Updatetime can be null then current datetime of the query runtime should be used

    --Create the Data Table

    CREATE TABLE Incidents (

    Incident_ID int identity(1,1),

    Zone nvarchar(1),

    Status char(1),

    CTR_CREATED datetime,

    CTR_UPDATED datetime)

    ----

    -- Insert some sample data into our original data table

    INSERT INTO Incidents ( Incident_ID, Zone, Status, CTR_CREATED, CTR_UPDATED)

    SELECT '43087', '2', 'C', '6/30/2010', '2/17/2011'

    UNION ALL SELECT '43700', '2', 'C', '7/15/2010', '1/24/2011'

    UNION ALL SELECT '44767', '2', 'C', '8/20/2010', '9/7/2010 '

    UNION ALL SELECT '48172', '3', 'C', '12/2/2010', '1/3/2011 '

    UNION ALL SELECT '48709', '4', 'O', '12/20/2010', NULL

    UNION ALL SELECT '50214', '3', 'O', '1/19/2011', NULL

    UNION ALL SELECT '50491', '4', 'O', '1/26/2011', NULL

    UNION ALL SELECT '50697', '4', 'C', '2/2/2011', '6/8/2011'

    UNION ALL SELECT '50886', '2', 'O', '2/8/2011', NULL

    UNION ALL SELECT '52245', '3', 'C', '3/15/2011', '4/6/2011 '

    UNION ALL SELECT '52720', '2', 'O', '3/29/2011', NULL

    UNION ALL SELECT '54935', '2', 'C', '6/8/2011 ', '8/4/2011'

    UNION ALL SELECT '55077', '4', 'C', '6/13/2011', '6/16/2011'

    UNION ALL SELECT '56486', '2', 'O', '8/1/2011', '8/1/2011'

    UNION ALL SELECT '42172', '2', 'C', '6/4/2010', '8/11/2011'

    UNION ALL SELECT '42244', '2', 'C', '6/8/2010', '7/2/2010'

    UNION ALL SELECT '42434', '3', 'C', '6/11/2010', '6/24/2010'

    UNION ALL SELECT '42903', '2', 'C', '6/28/2009', '11/9/2010'

    --CREATE LINKING TABLE

    CREATE TABLE EVENTS(

    EVT_ID int identity(1,1),

    EVT_CODE int,

    EVT_STATUS char(1)

    ---- Insert some sample data into our lINK table

    INSERT INTO EVENTS(EVT_ID,EVT_CODE,EVT_STATUS)

    SELECT '10025','43700','C'

    UNION ALL SELECT '10026','44767','C'

    UNION ALL SELECT '10027','48172','C'

    UNION ALL SELECT '10028','48709','R'

    UNION ALL SELECT '10029','50214','R'

    UNION ALL SELECT '10030','50491','R'

    UNION ALL SELECT '10031','50697','C'

    UNION ALL SELECT '10032','50886','R'

    UNION ALL SELECT '10033','52245','C'

    UNION ALL SELECT '10034','52720','R'

    UNION ALL SELECT '10035','54935','C'

    UNION ALL SELECT '10036','55077','C'

    UNION ALL SELECT '10037','56486','Z'

    UNION ALL SELECT '10038','42172','C'

    UNION ALL SELECT '10039','42244','C'

    UNION ALL SELECT '10040','42434','C'

    UNION ALL SELECT '10041','42903','C'

    UNION ALL SELECT '10042','42905','O'

    ---Results should look like this

    ----------------------------------------------------------------------------------------------------------

    | Year | Month | Zone | Avarage | Count |

    ----------------------------------------------------------------------------------------------------------

    | 2010 | December | 1 | 6.35 | 5 |

    ----------------------------------------------------------------------------------------------------------

    | 2010 | December | 2 | 3.39 | 10 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | January | 3 | 5.51 | 4 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | January | 4 | 7.89 | 10 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | February | 1 | 6.9 | 30 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | February | 2 | 89.9 | 2 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | February | 3 | 15.4 | 25 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | February | 4 | 12.45 | 1 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | March | 1 | 12.22 | 15 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | March | 3 | 5.25 | 21 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | March | 4 | 19.32 | 10 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | April | 3 | 7.45 | 2 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | May | 2 | 6.33 | 1 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | June | 1 | 5.26 | 1 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | July | 4 | 10.2 | 2 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | August | 1 | 21.15 | 0 |

    ----------------------------------------------------------------------------------------------------------

    | 2011 | August | 2 | 10.9 | 2

    /************************************

    To get this result above, my current solution is

    to run a query with a variable and dump the data in a temp table and then query the result.

    this but I believe there could be a better solution

    */

    ---------------------------------------------------------------------------------------

    -- Declare the variable to be used.

    DECLARE @MyCounter int,

    @dtDate DATETIME

    -- Initialize the variable.

    SET @MyCounter = 0;

    -- Test the variable to see if the loop is finished for the last 12 months.

    WHILE (@MyCounter < 12)

    BEGIN;

    -- Initialize date to current end of month

    SET @dtDate = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1-@MyCounter,0))

    INSERT INTO TempTable(MMMYY, [YEAR],MONTHNUMBER,[ZONE], [AVG-DAYS],[COUNT],[DESC])

    SELECT

    CONVERT(varchar(3), @dtDate )+'-'+right(CONVERT(varchar(11),@dtDate ),2) as 'MMMYY',

    YEAR (@dtDate) YEAR,

    MONTH(@dtDate) 'MONTHNUMBER',

    ZONE,

    AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)) as 'AVG-DAYS',

    COUNT(*) As [Count],

    'KPI-XYZ' as [DESC]

    FROM INCIDENTS left outer join EVENTS on Incident_ID=EVT_CODE

    WHERE CTR_SERVICECATEGORY='XYZ'

    and (CTR_STATUS in ('CL', 'F', 'O') OR EVT_STATUS in ('C','R', 'Z'))

    and ( ----Last Day of Previous Month ----Last Day of Current Month

    ( EVT_STATUS='C' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    OR

    ( CTR_STATUS= 'CL' AND CTR_UPDATED >DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate),0)) AND CTR_UPDATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    OR

    (EVT_STATUS in ('R','Z' ) AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    OR

    ( CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <=DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dtDate)+1,0))

    )

    )

    group by ZONE

    set @MyCounter =@MyCounter+1

    END

    select * from TempTable

  • Yes, there almost certainly is a better solution. But you haven't posted any DDL or data for EVENTS, so we can't offer you any tested code. A good place for you to start is to read about Numbers (or Tally) tables. You can use one of those to get the twelve months you need, instead of looping through each month.

    John

  • Substitute those variables for a 12-row table:

    SELECT

    dtDate,

    [MMMYY] = CONVERT(varchar(3), dtDate )+'-'+right(CONVERT(varchar(11),dtDate ),2),

    [YEAR] = YEAR(dtDate),

    [MONTHNUMBER] = MONTH(dtDate),

    lowerbound = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dtDate),0)),

    upperbound = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dtDate)+1,0))

    INTO #Dates

    FROM (

    SELECT dtDate = DATEADD(S,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1-n.n,0)) -- NOW HAS TO BE LESS THAN

    FROM (

    SELECT 0 AS n UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10 UNION ALL

    SELECT 11) n

    ) d

    --------------------------------------------------------

    SELECT

    d.[MMMYY],

    d.[YEAR],

    d.[MONTHNUMBER],

    ZONE,

    [AVG-DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),

    [Count] = COUNT(*),

    [DESC] = 'KPI-XYZ'

    FROM INCIDENTS

    CROSS JOIN (#Dates) d

    left outer join [EVENTS]

    on CTR_EVENT = EVT_CODE

    WHERE CTR_SERVICECATEGORY='XYZ'

    and (CTR_STATUS in ('CL', 'F', 'O') OR EVT_STATUS in ('C','R', 'Z'))

    and ( ----Last Day of Previous Month ----Last Day of Current Month

    (EVT_STATUS='C' AND CTR_UPDATED > d.lowerbound AND CTR_UPDATED <= d.upperbound)

    OR (CTR_STATUS= 'CL' AND CTR_UPDATED > d.lowerbound AND CTR_UPDATED <= d.upperbound)

    )

    OR (EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)

    OR (CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)

    )

    group by ZONE

    Next - get rid of the crazy "subtract a second" date arithmetic.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • I just added that.

    Thank you.

  • I am getting an error.

    Msg 102, Level 15, State 1, Line 36

    Incorrect syntax near ')'.

    The syntax seems to be correct

  • uciltas-924976 (8/23/2011)


    I am getting an error.

    Msg 102, Level 15, State 1, Line 36

    Incorrect syntax near ')'.

    The syntax seems to be correct

    Without seeing the query it's hard to tell.

    I think there's a "opinion mismatch" between SQL Server and you regarding a "correct syntax" 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • uciltas-924976 (8/23/2011)


    I am getting an error.

    Msg 102, Level 15, State 1, Line 36

    Incorrect syntax near ')'.

    The syntax seems to be correct

    The syntax was definitely incorrect, but it's my mistake, not yours. This corrects it:

    -----------------------------------------------------------------

    WHERE CTR_SERVICECATEGORY = 'XYZ' AND (CTR_STATUS IN ('CL', 'F', 'O') OR EVT_STATUS IN ('C','R', 'Z'))

    and ( ----Last Day of Previous Month ----Last Day of Current Month

    (EVT_STATUS='C' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)

    OR

    (CTR_STATUS= 'CL' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)

    OR

    (EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)

    OR

    (CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)

    )

    GROUP ZONE

    -------------------------------------------------------------------

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • It seems like it doesnt like the parenthesis around (#Dates)

  • How can I handle AVG_DAYS when is CTR_UPDATED null.

    Because if it is nul:

    l for the current month we have to use current date time.

    previous months data we have to use previous months' upper bound

    [AVG-DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),

  • I have one problem here:

    [AVG_DAYS] = AVG (dateDIFF(DAY,CTR_CREATED,CTR_UPDATED)),

    How do we handle [AVG-DAYS] when CTR_UPDATED is null ?

    when it is current month data CTR_UPDATED will be getdate()

    when previous month data it has to be previous months upper bound

    '

    '

    for May 2011 month data it has to be upper bound of May 2011.

  • -same post multiple times; sorry

  • SELECT

    d.[MMMYY],

    d.[YEAR],

    d.[MONTHNUMBER],

    ZONE,

    [AVG-DAYS] = AVG(DATEDIFF(DAY, CTR_CREATED, ISNULL(CTR_UPDATED, CASE WHEN d.dtDate = 0 THEN GETDATE() ELSE d.upperbound END))),

    [Count] = COUNT(*),

    [DESC] = 'KPI-XYZ'

    FROM INCIDENTS

    CROSS JOIN #Dates d

    LEFT JOIN [EVENTS]

    ON CTR_EVENT = EVT_CODE

    WHERE CTR_SERVICECATEGORY = 'XYZ' AND (CTR_STATUS IN ('CL', 'F', 'O') OR EVT_STATUS IN ('C','R', 'Z'))

    AND ( ----Last Day of Previous Month ----Last Day of Current Month

    (EVT_STATUS='C' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)

    OR

    (CTR_STATUS= 'CL' AND CTR_UPDATED >d.lowerbound AND CTR_UPDATED <= d.upperbound)

    OR

    (EVT_STATUS in ('R','Z' ) AND CTR_CREATED <= d.upperbound)

    OR

    (CTR_STATUS in ( 'F', 'O') AND CTR_CREATED <= d.upperbound)

    )

    GROUP BY ZONE

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Thank you very much.

    I will have to double check my numbers but this seems to be the solution I was looking for.

    If I wanted to create a view for this how can I handle the temp table (#Dates)

    Because it will say "the table already exists" if I don't drop it before running the view.

Viewing 15 posts - 1 through 15 (of 26 total)

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