Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    2

    Smile Unanswered: Rewriting Insert Statements

    Hi Friends,
    I have the following set of Insert Statements that calculates sums for various criteria and inserts a row at a time onto my table.
    I have a row for every month starting from January with sums for 4 severity levels. So for 12 months that would be 48 Insert Statements and if I want to do this for 4 different types of [EName] that would be 48 * 4 = 192 Insert Statements. Is there a better way to write this. Thanks for your help

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 1 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'January' and [Severity Level] = 1)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 2 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'January' and [Severity Level] = 2)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 3 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'January' and [Severity Level] = 3)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '1/1/06') AS TrendMonth, 4 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'January' and [Severity Level] = 4)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 1 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'February' and [Severity Level] = 1)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 2 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'February' and [Severity Level] = 2)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 3 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'February' and [Severity Level] = 3)

    INSERT INTO dbo.tbl_Ticket ([EName], TrendMonth, [Severity Level], [Count])
    SELECT 'OVERALL' AS [EName], DATENAME(MONTH, '2/1/06') AS TrendMonth, 4 , Sum([Count])
    FROM dbo.tbl_Ticket
    WHERE (TrendMonth LIKE 'February' and [Severity Level] = 4)

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Maybe something along the lines of
    Code:
    SELECT 'OVERALL' AS [EName], trendmonth AS TrendMonth, SeverityLevel , Sum([Count])
    FROM dbo.tbl_Ticket
    group by EName, Trendmonth, SeverityLevel

  3. #3
    Join Date
    Oct 2006
    Posts
    2
    Works Great!! Thank you very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •