Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: Brand NOOB to MSSQL

    I have two queries that I would like to combine into one. I have made a few feable attempts with no results. This is a Computer Aided Dispatch system and I am a police officer. One query pulls information about calls that are completed and the other query pulls in the officer information associated with that completed that call. If I could combine them then I could have a list of calls completed and by whom. Both queries run fine by them self. They were written by the tech I called from the CAD company based on what I wanted but he kept them seperate. When I called back I can't get the same tech to come on the line.

    Query 1
    Code:
    use cad
    go
    select 
          a.inci_id as 'CAD CALL #',
          a.case_id as 'Case #',
          a.unitassign as 'Unit Assigned',
          b.unitcode as 'Unit Code',
          b.name as 'Officer Name',
          SUBSTRING(c.beat,1,1) as 'Sector',
          a.naturecode as 'Call Type',
          a.street as 'Address',
          a.closecode as 'Disp Code',
          a.nature as 'Call Desc',
          a.calltime as 'Call Time'
    from
          inmain a join unitper b on a.primeuper=b.unitperid join unit c on b.unitcode=c.unitcode
    where
          a.calltime between '2013-02-13 08:47:00' and '2013-02-13 08:50:00' AND
          a.case_id <> '' AND b.unitcode between 1110 and 1119
    The second query is this
    Code:
    use cad
    go
    select 
    	undisp.unitcode as 'Unit Code',
    	undisp.agency as 'Agency', 
    	emmain.emfname as 'First Name', 
    	emmain.emlname as 'LastName',
    	emmain.emdept_id as 'Emp #',
    	undisp.onduty as 'Start of Shift',
    	undisp.offduty as 'End of Shift'
    from undisp INNER JOIN unitper 
    	on undisp.unitperid=unitper.unitperid
    		INNER JOIN emmain
    			on unitper.emdept_id=emmain.emdept_id
    where 
    	undisp.onduty between '2013-01-01' and '2013-03-31'
    I have attached the output as jpgs to this message. The only thing I need from the second query is the EMP# that would be tied to the Unit Code in query 2 to the unit code in query 1. If this is possible, if the singular query would take the output as query1 and just add the EMP# as an extra column.

    This db is running on MSSQL 2008r2

    Thanks for taking a look,
    Nacman
    Attached Thumbnails Attached Thumbnails query1.JPG   query2.JPG  

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Hello and welcome!

    If I'm reading your requirements correctly, the following should do some of the trick:
    Code:
    SELECT
      a.inci_id 'CAD CALL #'
    , a.case_id 'Case #'
    , a.unitassign 'Unit Assigned'
    , b.unitcode 'Unit Code'
    , e.emdept_id 'EMP #'
    , b.name 'Officer Name'
    , SUBSTRING(c.beat, 1, 1) Sector
    , a.naturecode 'Call Type'
    , a.street 'Address'
    , a.closecode 'Disp Code'
    , a.nature 'Call Desc'
    , a.calltime 'Call Time'
    FROM
      inmain a
    INNER JOIN
      unitper b
    ON
      a.primeuper = b.unitperid
    INNER JOIN
      unit c
    ON
      b.unitcode = c.unitcode
    INNER JOIN
     undisp d
    ON
      a.unitperid = d.unitperid
    INNER JOIN
      emmain e
    ON
      b.emdept_id = e.emdept_id
    WHERE
      (a.calltime
    BETWEEN
      '2013-02-13 08:47:00'
    AND
      '2013-02-13 08:50:00')
    AND
      a.case_id <> ''
    AND
      b.unitcode between 1110 and 1119
    I worked on the basis that the date/time criteria from the first query are the required ones - combining the criteria from the second query would restrict your output to cases with a call time between three minutes on 13th Feb that were assigned to officers who were also on duty at some point in January.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  3. #3
    Join Date
    Feb 2013
    Posts
    2
    Thank you, that is what I needed. Amazing!!

  4. #4
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome!

    I still think that you should give serious thought to the structure of the database, though - a little work now can save a lot of work in the future.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

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