Search code examples
sql-serverdatabasessmssql-server-2017sql-server-2017-express

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

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:

enter image description here

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:

enter image description here

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.

Solution

  • 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