(mssql) How can i add extra columns in extracted data

Question

i have this code below (running on sql server 2017):

WITH selection AS (
    SELECT servertimestamp 
    FROM eventlog 
    WHERE servertimestamp BETWEEN '5/29/2018' AND DATEADD(dd, +1, '6/29/2019')

    AND (attributes LIKE '%N<=>PeopleIn%'))
(SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, servertimestamp) - (DATEDIFF(HOUR, 0, servertimestamp) % 2), 0) as timestamp , COUNT(servertimestamp) AS GONE_OUT
FROM selection
WHERE DATEPART(hh, servertimestamp) BETWEEN 8 AND 20

GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, servertimestamp) - (DATEDIFF(HOUR, 0, servertimestamp) % 2), 0))
ORDER BY timestamp

Also the screenshot below shows the result of the executed code:

What this code does is showing how many people came in a building each day. The data is grouped in a 2 hour basis.

What i want to do, is adding a column that shows how many people have gone out of the building for the same time slots that i'm already using. Below i'm giving you an example of what i want to do:

Notice that on the 6th line i'm using the LIKE operator (attributes LIKE '%N<=>PeopleIn%'). This means that for the additional column, i'll have to make similar selections, but with the difference of using attributes LIKE '%N<=>PeopleOut%'.

Can i make it by using the UNION operator? Is there any other more obvious or easier way to do it?

Your help will be appreciated, thank you.

You could do it with a UNION or you could do the whole thing slightly more cleanly with a PIVOT, I think. I'd need to spend an hour or so working and writing out the logic, though. — Dylan Brams, Jul 05 '18 at 09:09

score 0 · Answer 1 · answered Jul 05 '18 at 09:14

UNION appends result sets in the order of execution. UNION will not be my first choice in solving this problem.

What I see in you screenshot is that GONE_OUT and CAME_IN are grouped on a datetime which is unique and a category on which aggregated values are grouped. You may have two (sub)queries, one for GONE_OUT and one for CAME_IN and then build a relation.

SELECT *
FROM GONE_OUT AS go 
LEFT JOIN CAME_IN AS ci ON go.timestamp = ci.timestamp

score 0 · Accepted Answer · answered Jul 05 '18 at 11:18

You could do it by sort of labeling the servertimestamp field in your CTE based on the activity, then sum up the labels.

WITH selection
AS (
  SELECT 
    servertimestamp
    ,CASE WHEN attributes LIKE '%N<=>PeopleIn%' THEN 1 ELSE 0 END AS PPL_IN
    ,CASE WHEN attributes LIKE '%N<=>PeopleOut%' THEN 1 ELSE 0 END AS PPL_OUT
  FROM eventlog
  WHERE 
    servertimestamp BETWEEN '5/29/2018' AND DATEADD(dd, + 1, '6/29/2019')
    AND 
    (attributes LIKE '%N<=>PeopleIn%'
     OR
     attributes LIKE '%N<=>PeopleOut%')
  ) 
  (
    SELECT 
      DATEADD(HOUR, DATEDIFF(HOUR, 0, servertimestamp) - (DATEDIFF(HOUR, 0, servertimestamp) % 2), 0) AS TIMESTAMP
      ,SUM(PPL_OUT) AS GONE_OUT
      ,SUM(PPL_IN) AS CAME_IN
    FROM selection
    WHERE DATEPART(hh, servertimestamp) BETWEEN 8
        AND 20
    GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, servertimestamp) - (DATEDIFF(HOUR, 0, servertimestamp) % 2), 0)
   )
ORDER BY TIMESTAMP

(mssql) How can i add extra columns in extracted data

2 Answers2