Count IN and OUT items for each day in period

Analytics

Count IN and OUT items for each day in period

I have a table like this

 Item nameDate inDate out
Item12017-03-312017-04-03
Item22017-03-312040-01-01
Item32017-03-312017-04-04
Item42017-04-022017-04-03
Item52017-04-022040-01-01
Item62017-04-022040-01-01
Item72017-04-032017-04-04
Item82017-04-032040-01-01
Item92017-04-042040-01-01
Item102017-04-042040-01-01
Item112017-04-042040-01-01
Item122017-04-042040-01-01
Item132017-04-062040-01-01
Item142017-04-072040-01-01

 

Need to count IN and OUT items for each day in period (for example 2017-03-31 - 2017-04-07).

So i want to get table like this

DateCount(IN)Count(OUT)
2017-03-3130
2017-04-0100
2017-04-0230
2017-04-0322
2017-04-0442
2017-04-0500
2017-04-0610
2017-04-0710

 

Big big tnx for any helping!

2 REPLIES
Senior Supporter

Re: Count IN and OUT items for each day in period

try the following

CREATE VOLATILE TABLE vt_a AS (
SELECT 'item' !! TRIM (c1.day_of_calendar) AS item_name, ('2017-03-01' ( DATE )) + random(0, 8) AS date_in, date_in + random(1, 5) AS date_out
FROM sys_calendar.calendar c1
WHERE c1.day_of_calendar BETWEEN 1 AND 14
) WITH DATA NO PRIMARY INDEX ON COMMIT PRESERVE ROWS;

SELECT
  c1.calendar_date         AS ref_dt,
  zeroifnull(iin.cnt_in)   AS cnt_in,
  zeroifnull(iout.cnt_out) AS cnt_out
FROM
  sys_calendar.calendar c1
  LEFT OUTER JOIN
  (
    SELECT
      date_in  AS ref_dt,
      count(*) AS cnt_in
    FROM vt_a
    GROUP BY 1
  ) AS iin
    ON c1.calendar_date = iin.ref_dt
  LEFT OUTER JOIN
  (
    SELECT
      date_out AS ref_dt,
      count(*) AS cnt_out
    FROM vt_a
    GROUP BY 1
  ) AS iout
    ON c1.calendar_date = iout.ref_dt
WHERE c1.calendar_Date BETWEEN '2017-03-01' AND '2017-03-31'
ORDER BY 1

Re: Count IN and OUT items for each day in period

It works. Very nice! Thank you for helping!