SQL Query Assistance

Question

Hi all,

I'm having a complete brain fart moment so i figured i'd ask away here. I have 3 tables that look like this

Equipment Table

EquipmentID | LocationID 
-------------------------
1           | 2
2           | 2
3           | 1
4           | 2
5           | 3
6           | 3

Location Table

LocationID | LocationName
--------------------------
1          | Pizza Hut
2          | Giordanos
3          | Lou Malnati's

Service Table

LocationID | EquipmentID | Status 
-----------------------------------
2          | 1           | Serviced
2          | 2           | Not Yet Serviced
2          | 4           | Not Yet Serviced
3          | 5           | Serviced

I need a way to list all locations that have had one or more equipment(s) serviced, but not all of the equipments at the location have been serviced yet.

So for the example above it would return the following results

LocationID | ServicedEquipmentID  | NotServicedEquipmentIDS   | LocationStatus
------------------------------------------------------------------------------
2          | 1                    | 2, 4                      | Partially Serviced
3          | 5                    | 6                         | Partially Serviced

Thanks for any help!

Answer 1

This query will give you the location status you desire, although not the individual equipment statuses:

SELECT  [LocationId]
       ,[LocationId]
       ,CASE ( [IsServiced] + [IsNotServiced] )
          WHEN 0 THEN 'Not Serviced'
          WHEN 1 THEN 'Partially Serviced'
          WHEN 2 THEN 'Serviced'
        END [LocationStatus]
FROM    ( SELECT    [l].[LocationId]
                   ,[e].[LocationId]
                   ,CASE [s].[Status]
                      WHEN 'Serviced' THEN 1
                      ELSE 0
                    END [IsServiced]
                   ,CASE [s].[Status]
                      WHEN 'Not Yet Serviced' THEN 1
                      ELSE 0
                    END [IsNotServiced]
          FROM      [Location] l
                    INNER JOIN [Equipment] e ON [l].[LocationId] = [e].[LocationId]
                    INNER JOIN [Service] s ON [l].[LocationId] = [s].[LocationId]
                                              AND [e].[EquipmentId] = [s].[EquipmentId]
        ) x

To add a comma-seperated list of equipmentIds that have been/not been serviced to the result set, you will need a CONCAT function of some sort. Either a UDF, CLR, or a recursive CTE (I don't have time to write that right now -- here's a link).