SQL Server Count Data – Counting Data on a Third Table Through Link

sql-server-2008-r2

Suppose I have the following three tables: SupportTicket, Device, and Link. I've created these in SQL Fiddle. I want to display a list of support tickets within a time range that meet specified criteria and show a count of the types of devices linked to it. The problem is that my current query is duplicating Support Tickets rather than counting all the devices linked to it on one row. Here is my query:

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  CASE
    WHEN d.Type = 'Cellphone' 
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Mobile Devices],
  CASE
    WHEN d.Type = 'Computer' OR d.Type = 'Disc'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Computers],
  CASE
    WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette'
    THEN COUNT(d.Device_ID)
    ELSE 0
  END AS [Audiovisual]
FROM 
  SupportTicket st
  JOIN Link l
    ON st.SupportTicket_ID = l.Entity_ID1
    OR st.SupportTicket_ID = l.Entity_ID2
  JOIN Device d
    ON l.Entity_ID1 = d.Device_ID
    OR l.Entity_ID2 = d.Device_ID
WHERE 
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted BETWEEN '20140201' AND CURRENT_TIMESTAMP
GROUP BY
  st.SupportTicket_ID, st.Submitting_User, st.Submitting_Department, d.Type
ORDER BY
  st.SupportTicket_ID

Here is the SQL Fiddle instance where you can see what is currently returned by this query.

A support ticket should only occupy one row with a count of how many devices (by type, as shown) were linked to it in the columns on that row.

Below is the desired output:

Desired output

Best Answer

Here's what I came up with (SQLFiddle):

SELECT
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department,
  SUM(
    CASE
      WHEN d.Type = 'Cellphone' THEN 1
      ELSE 0
    END
  ) AS [Mobile Devices],
  SUM(
    CASE
      WHEN d.Type = 'Computer' OR d.Type = 'Disc' THEN 1
      ELSE 0
    END
  ) AS [Computers],
  SUM(
    CASE
      WHEN d.Type = 'Camera' OR d.Type = 'Video Cassette' THEN 1
      ELSE 0
    END
  ) AS [Audiovisual]
FROM SupportTicket st
JOIN Link l 
  ON st.SupportTicket_ID IN (l.Entity_ID1, l.Entity_ID2)
JOIN Device d 
  ON d.Device_ID IN (l.Entity_ID1, l.Entity_ID2)
WHERE 
  l.Link_ID LIKE 'CAS%'
  AND st.Type = 'Tech Support'
  AND st.Date_Submitted >= CONVERT(datetime, '20140201', 112)
  AND st.Date_Submitted < CONVERT(datetime, CONVERT(date, CURRENT_TIMESTAMP))
GROUP BY
  st.SupportTicket_ID,
  st.Submitting_User,
  st.Submitting_Department
ORDER BY
  st.SupportTicket_ID;

The basic idea is to compute the sum of an expression that returns 1 if the current row has the required type, and zero otherwise. It's probably possible using PIVOT syntax as well.

Related Question