asked on

Adding Query results as new columns to existing table

I have a table and trying to add two columns to it to show CallTypeCount and AlarmTime. The syntax for the columns is below. Basically count the Number of calls, and difference between two times, to know how long each call took

            SELECT       count(Call_type) CallTypeCount,
            DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) as AlarmTime
            FROM DBO.FD_Table b
            WHERE      DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) > 1 and DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) < 180
            group by      Call_Type,EventFirstDispatched,EventFirstEnroute,Call_Source

I tried to join the query above to the original table , the query had an infinite loop, could not stop executing

SELECT a.*,CallTypeCount, AlarmTime
            FROM dbo.FD_Table A
            LEFT JOIN
            (

            SELECT       count(Call_type) AS CallTypeCount,
            DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) AS AlarmTime
            FROM DBO.FD_Table b
            WHERE      DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) > 1 and DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) < 180
            group by      Call_Type,EventFirstDispatched,EventFirstEnroute,Call_Source

            )b

            ON             DATEDIFF(SECOND,a.EventFirstDispatched,a.EventFirstEnroute)= b.AlarmTime

Brian Crowe

Your queries don't make any sense as written so before we work on combining them we need to fix that.

            SELECT       count(Call_type) CallTypeCount,
            DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) as AlarmTime
            FROM DBO.FD_Table b
            WHERE      DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) > 1 and DATEDIFF(SECOND,EventFirstDispatched,EventFirstEnroute) < 180
            group by        Call_Type,EventFirstDispatched,EventFirstEnroute,Call_Source

Open in new window

I think this one should probably be:

SELECT Call_Type, COUNT(*) AS CallTypeCount
FROM dbo.FD_Table
WHERE DATEDIFF(SECOND, EventFirstDispatched, EventFirstEnroute) > 1
   AND DATEDIFF(SECOND, EventFirstDispatched, EventFirstEnroute) < 180
GROUP BY Call_Type

Open in new window

Fred

ASKER

Brian your query returns count of incidents, however the query does not return the differences in the two times
Call Type CallTypeCount
MOTOR-CYCLE 303
AUTO-ACCIDENT 5876
AUTO-STRUCTURE 88
AUTO-HIGH SPEED CRASH 202
AUTO-BICYLE 1048
AUTO-PEDESTRIAN 1178
I wanted to first create a query to show the two columns( CallTypeCount) and AlarmTime, then join those two columns to the table where the data came from ( some sort of self join)

Call TypeCount AlarmTime
1 60
1 60
1 60
1 60
1 60
1 120

Fred

ASKER

Open in new window

That is the original table am trying to add those two columns to

awking00

Can you post the original table shown as a file or text so we can use it to develop a test for the query you want?

ASKER CERTIFIED SOLUTION

Fred

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial