Post reply

Help with Inserting data into Temp Table

  • May 5, 2010 at 11:38 am

    #138907

    Hi,

    I am trying to count number of calls according to 3 different filters and I am using the Insert Into statement. However right now the logic works but it creates a new row for each statement. How do I Insert/Update the table so that the data displays in one row. Right now my results come out like this:

    100 Null Null

    Null 250 Null

    Null Null 55

    And I want this:

    100 250 55

    If you could just point me in the direction I would appreciate it. Here is my code:

    Declare @CallMonth Datetime

    Set @CallMonth = dateadd(m,-1,getdate())

    Create Table #MonthlyCalls

    (

    NewCalls int,

    ClosedCalls int,

    CarriedOverCalls int

    )

    Insert into #MonthlyCalls (NewCalls)

    --Values

    select Count(*)

    From heat.dbo.CallLog

    where

    year( recvddate ) = Year(@CallMonth)

    AND

    Month( recvddate ) = Month(@CallMonth)

    Insert into #MonthlyCalls (ClosedCalls)

    Select Count(*)

    From heat.dbo.CallLog

    where

    Year( recvddate ) = Year(@CallMonth)

    AND

    Month( recvddate ) = Month(@CallMonth)

    And

    Year( ClosedDate ) = Year(@CallMonth)

    AND

    Month( ClosedDate ) = Month(@CallMonth)

    Insert into #MonthlyCalls (CarriedOverCalls)

    Select Count(*)

    From heat.dbo.CallLog

    where

    Year( recvddate ) <= Year(@CallMonth)

    AND

    Month( recvddate ) < Month(@CallMonth)

    And

    Year( ClosedDate ) >= Year(@CallMonth)

    AND

    Month( ClosedDate ) >= Month(@CallMonth)

    select * from #MonthlyCalls

    Drop table #monthlycalls

  • May 5, 2010 at 11:51 am

    #1162660

    Paul thank you so much for providing the code you were trying to use; it gave me everything i needed to try and look at the issue. Since this was also your first post, that goes double! thank you again!

    i think you can skip the whole temp table altogether; see if this works for you. i'm just selecting from all three queries you were using, by simply making them all sub-selects with aliases.

    Declare @CallMonth Datetime

    Set @CallMonth = dateadd(m,-1,getdate())

    --Values

    SELECT

    Alias1.NewCalls,

    Alias2.ClosedCalls,

    Alias3.CarriedOverCalls

    FROM

    (select Count(*) As NewCalls

    From heat.dbo.CallLog

    where year( recvddate ) = Year(@CallMonth)

    AND Month( recvddate ) = Month(@CallMonth)) Alias1,

    (Select Count(*) As ClosedCalls

    From heat.dbo.CallLog

    where Year( recvddate ) = Year(@CallMonth)

    AND Month( recvddate ) = Month(@CallMonth)

    And Year( ClosedDate ) = Year(@CallMonth)

    AND Month( ClosedDate ) = Month(@CallMonth) ) Alias2,

    (Select Count(*) As CarriedOverCalls

    From heat.dbo.CallLog

    where Year( recvddate ) <= Year(@CallMonth)

    AND Month( recvddate ) < Month(@CallMonth)

    And Year( ClosedDate ) >= Year(@CallMonth)

    AND Month( ClosedDate ) >= Month(@CallMonth) )Alias3

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 5, 2010 at 12:10 pm

    #1162676

    That worked perfectly, thanks.

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

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