Post reply

Help with query

  • January 27, 2008 at 2:58 am

    #182306

    I'm not sure if this is the appropriate forum to ask this question, but I'm looking for advice on how to write a summary query.

    I have a table containing details of helpdesk issues. It has four columns:

    - IssueNumber

    - DateRaised

    - DateClosed

    How do I write a query which shows a weekly summary of number of open and closed issues? The query result set would be:

    - Week ending date

    - No. of issues opened

    - No. of issues closed.

    Any help would be much appreciated!

  • January 27, 2008 at 5:02 am

    #772181

    Could you please post table schema, sample data and desired results.

    Please see the following article - http://www.sqlservercentral.com/articles/Best+Practices/61537/

    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

  • January 27, 2008 at 10:55 am

    #772217

    Sorry - thanks for the pointer.

    The desired results are:

    Week_end_date, no_opened, no_closed

    Oct 14 2007,4,0

    Oct 21 2007,2,3

    Oct 28 2007,1,3

    Nov 04 2007,5,3

    Nov 11 2007,2,3

    Nov 18 2007,2,2

    Nov 25 2007,0,1

    Dec 02 2007,0,1

    Table setup is:

    --===== If the test table already exists, drop it

    IF OBJECT_ID('ian..#issuelog','U') IS NOT NULL

    DROP TABLE #issuelog

    --===== Create the test table with

    CREATE TABLE #issuelog

    (

    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table

    DateOpened DATETIME,

    DateClosed DATETIME

    )

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

    --===== All Inserts into the IDENTITY column

    SET IDENTITY_INSERT #issuelog ON

    --===== Insert the test data into the test table

    INSERT INTO #issuelog

    (ID, DateOpened, DateClosed)

    SELECT '1','Oct 11 2007 12:00AM','Oct 11 2007 01:20AM' UNION ALL

    SELECT '2','Oct 12 2007 12:00AM','Oct 19 2007 01:20AM' UNION ALL

    SELECT '3','Oct 13 2007 12:00AM','Oct 27 2007 01:20AM' UNION ALL

    SELECT '4','Oct 14 2007 12:00AM','Oct 15 2007 01:20AM' UNION ALL

    SELECT '5','Oct 17 2007 12:00AM','Oct 22 2007 01:20AM' UNION ALL

    SELECT '6','Oct 20 2007 12:00AM','Nov 07 2007 01:20AM' UNION ALL

    SELECT '7','Oct 27 2007 12:00AM','Oct 28 2007 01:20AM' UNION ALL

    SELECT '8','Oct 29 2007 12:00AM','Nov 10 2007 01:20AM' UNION ALL

    SELECT '9','Oct 30 2007 12:00AM','Oct 31 2007 01:20AM' UNION ALL

    SELECT '10','Oct 30 2007 12:00AM','Nov 03 2007 01:20AM' UNION ALL

    SELECT '11','Nov 01 2007 12:00AM','Nov 06 2007 01:20AM' UNION ALL

    SELECT '12','Nov 02 2007 12:00AM','Nov 02 2007 01:20AM' UNION ALL

    SELECT '13','Nov 05 2007 12:00AM','Nov 29 2007 01:20AM' UNION ALL

    SELECT '14','Nov 09 2007 12:00AM','Nov 18 2007 01:20AM' UNION ALL

    SELECT '15','Nov 17 2007 12:00AM','Nov 17 2007 01:20AM' UNION ALL

    SELECT '16','Nov 18 2007 12:00AM','Nov 20 2007 01:20AM'

    --===== Set the identity insert back to normal

    SET IDENTITY_INSERT #issuelog OFF

    --===== Setup any special required conditions especially where dates are concerned

    SET DATEFORMAT DMY

  • January 27, 2008 at 11:54 am

    #772231

    Easiest way to do this (I think) is with a 'tally' table. Lots of examples around here. Essentially, it's just a table filled with numbers and/or dates that you can use for range checks. A permanent tally table is best, I'm going to use a CTE for convenience. If you're doing this on a regular basis, create the date list as a permanent table.

    (Yes, I know, a recursive CTE isn't the fastest. It's easy for demo purposes).

    WITH TallyCTE(DateWeekStarted) AS (

    SELECT DATEADD(ww,DATEDIFF(ww,0,'2007/01/01'),0) AS DateWeekStarted

    UNION ALL

    SELECT DATEADD(ww,1,DateWeekStarted) AS DateWeekStarted FROM TallyCTE WHERE DateWeekStarted<getdate()

    )

    SELECT DATEADD(dd,6,DateWeekStarted) AS DateWeekEnded,

    SUM(Case when DateOpened between DateWeekStarted AND DATEADD(ms,-3,DATEADD(ww,1,DateWeekStarted)) THEN 1 ELSE 0 END) AS TotalIssuesLogged,

    SUM(Case when DateClosed between DateWeekStarted AND DATEADD(ms,-3,DATEADD(ww,1,DateWeekStarted)) THEN 1 ELSE 0 END) AS TotalIssuesClosed

    FROM TallyCTE Left Outer join (

    SELECT DateOpened, DateClosed, DATEADD(ww,DATEDIFF(ww,0,DateOpened),0) as WeekIssueLogged, DATEADD(ww,DATEDIFF(ww,0,DateClosed),0) as WeekIssueClosed FROM #issuelog

    ) WeeklyIssues

    on TallyCTE.DateWeekStarted = WeeklyIssues.WeekIssueLogged OR TallyCTE.DateWeekStarted = WeeklyIssues.weekIssueClosed

    group by DATEADD(dd,6,DateWeekStarted)

    There's probably a better way, but this seems to work.

    My results are a little different to what you put as desired. Your desired had 0 issues closed week ending 14 Oct, but the sample data had an issue closed on the 11th Oct. Did I misunderstand, or is that a data mistake?

    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

  • January 27, 2008 at 3:30 pm

    #772241

    You spotted the deliberate error in my test data 😉

    Thanks so much for this - I would never have arrived at this solution! This is exactly what I was looking for.

Viewing 5 posts - 1 through 4 (of 4 total)

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