Select counts from two tables, and output the results in a specified format

Question

I have two tables with different overall structures, but they share 3 column names:

dbo.[Moves]

Id |  DeviceId  |      TL_Event_Date       |  ...
---|------------|--------------------------|------
 1 |    D810    |  2020-01-28 09:19:55.587 |  ...
 2 |    D810    |  2020-01-29 09:19:55.587 |  ...
 3 |    D710    |  2020-01-29 09:19:55.587 |  ...
 4 |    D812    |  2020-01-29 09:19:55.587 |  ...
...

dbo.[Faults]
Id |  DeviceId  |      TL_Event_Date       |  ...
---|------------|--------------------------|------
 1 |    D610    |        timestamp         |  ...
 2 |    D810    |        timestamp         |  ...
 3 |    D710    |        timestamp         |  ...
 4 |    D812    |        timestamp         |  ...
...

Both tables have more columns, but only the DeviceId and TL_Event_Date are needed for filling out the WHERE clause of the query.

I would like to get a count of the number of Moves and Faults for a specific DeviceId in a given time range, which will be passed as a Dataset for a SSRS Tablix.

I am trying to format the output of the query as

DeviceId  |  Aisle  |  Level  |  Lift  |  MoveCount  |  FaultCount
  D820    |  Aisle8 | Level20 | Upper  |     16      |       5
  D818    |  Aisle8 | Level18 | Upper  |     36      |       31
  D817    |  Aisle8 | Level17 | Upper  |     0       |       2
  D811    |  Aisle8 | Level11 | Upper  |     10      |       0

The following query works for a single device, but I am not sure if and how it could be expanded to show counts if the WHERE clause is changed to be a list of [Aisles] rather than a single specific device. The [Aisle] and [Level] are derived from the 3 digits in the DeviceId where Dxyz => Aisle x, Level yz.

SELECT
    dm.DeviceId
    , 'Aisle'+RIGHT(LEFT(DeviceId,4),1) as [Aisle]
    , 'Level'+RIGHT(DeviceId,2) as [Level]
    , case when RIGHT(DeviceId,2) >= 11 then 'Upper' else 'Lower' end as [Lift]
    , (SELECT COUNT(*) FROM DeviceMove WHERE DeviceId = 'D820' and TL_Event_Date BETWEEN '2020-01-10 09:15:38.980' and '2020-01-28 09:25:38.980') AS [MoveCount]
    , (SELECT COUNT(*) FROM  DeviceFaulted WHERE DeviceId = 'D820' and TL_Event_Date BETWEEN '2020-01-10 09:15:38.980' and '2020-01-28 09:25:38.980' ) AS [FaultCount]
FROM
    DeviceMove dm
WHERE
    dm.DeviceId = 'D820'
GROUP BY
    dm.DeviceId

So I would like to replace

WHERE
    dm.DeviceId = 'D820'

With something like the following (while the specific values would come from the Report parameters rather than being hard coded)

WHERE
    'Aisle'+RIGHT(LEFT(DeviceId,4),1) in ('Aisle8', 'Aisle7', 'Aisle6')

Create a temp table of device ids and join your original query with the temp table on matching device ids. — L0uis, Apr 06 '20 at 00:17
I'm not sure how that would be different than trying to join the existing [Moves] and [Faults] tables, both of which could have the same DeviceId in multiple rows. — AChrapko, Apr 06 '20 at 00:19

score 0 · Accepted Answer · answered Apr 06 '20 at 00:57

I have found a solution using common_table_expressions -

;WITH cte1 AS(
    SELECT
        DeviceId
        , COUNT(DeviceId) as [MoveCount]
    FROM
        DeviceMove
    WHERE
        DeviceId like 'OLS80[1-3]'
    GROUP BY
        DeviceId)
, cte2 AS(
    SELECT
        DeviceId
        , COUNT(DeviceId) as [FaultCount]
    FROM
        DeviceFaulted
    WHERE
        DeviceId like 'OLS80[1-3]'
    GROUP BY
        DeviceId)
SELECT
    moves.DeviceId
    , 'Aisle'+RIGHT(LEFT(moves.DeviceId,4),1) as [Aisle]
    , 'Level'+RIGHT(moves.DeviceId,2) as [Level]
    , case when RIGHT(moves.DeviceId,2) >= 11 then 'Upper' else 'Lower' end as [Lift]
    , moves.MoveCount
    , faults.FaultCount
FROM
    cte1 moves
FULL JOIN cte2 faults ON moves.DeviceId = faults.DeviceId
ORDER BY
    [Aisle] desc, [Level] desc

Which returns accurate values

DeviceId  |  Aisle   |   Level   |  Lift   |  MoveCount  |  FaultCount
----------+----------+-----------+---------+-------------+---------
   D803   |  Aisle8  |  Level03  |  Lower  |    15515    |    708
   D802   |  Aisle8  |  Level02  |  Lower  |    17039    |    384
   D801   |  Aisle8  |  Level01  |  Lower  |    16306    |    399

Select counts from two tables, and output the results in a specified format

1 Answers1