I have a table with the below details. I need to count the number of devices installed within two date columns.
this condition needs to apply in the filter:
[The number of devices which have]
(end_date NULL OR end_date > current date) AND create_date<current date
C_ID device_id end_date create_date c_name
abc|1 abc|BIN|2164009 2011-09-30T00:08:58 2005-09-03T09:33:02 bob
abc|1 abc|def|6105880 2015-08-27T00:00:00 2009-03-19T10:31:11 bob
abc|1 abc|BIN|7224909 2015-08-27T00:00:00 2010-07-25T07:49:42 bob
abc|54 abc|def|163441 2004-04-15T00:00:00 2000-10-13T10:51:29 cts
abc|54 abc|def|425648 2004-04-26T00:00:00 2001-08-07T08:08:01 cts
abc|54 abc|def|6298 2004-04-15T00:00:00 2000-09-23T01:21:11 cts
abc|54 abc|def|436512 2004-04-26T00:00:00 2001-08-20T11:36:16 cts
abc|54 abc|def|5734 2002-04-26T00:00:00 2000-09-23T01:16:26 cts
abc|240 abc|def|663997 2007-11-05T00:00:00 2002-07-03T16:00:48 WTB GROUP
abc|240 abc|def|874147 2005-05-05T00:00:00 2003-03-27T12:43:38 WTB GROUP
The expected output should be in the form of
c_id num_devices c_name year_mon_date
abc|1 20 bt 2020-01-01
abc|10 30 cts 2020-01-02
abc|21 50 bob 2020-01-03
Daily count of Active tickets with start and closed dates for a given date range (calendar)
My question is similar to the question linked above, but I need a solution in BigQuery.