
-- --------------------------------------------------------

--
-- Structure for view `usage_2021_by_month`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`raimonds`@`%` SQL SECURITY DEFINER VIEW `usage_2021_by_month`  AS  select `u`.`id` AS `id`,`u`.`company` AS `company`,`p`.`plan` AS `plan`,cast(max(`p`.`date_till`) as date) AS `date_till`,count(if((`d`.`created` between '2021-01-01' and '2021-02-01'),1,NULL)) AS `Jan`,count(if((`d`.`created` between '2021-02-01' and '2021-03-01'),1,NULL)) AS `Feb`,count(if((`d`.`created` between '2021-03-01' and '2021-04-01'),1,NULL)) AS `Mar`,count(if((`d`.`created` between '2021-04-01' and '2021-05-01'),1,NULL)) AS `Apr`,count(if((`d`.`created` between '2021-05-01' and '2021-06-01'),1,NULL)) AS `May`,count(if((`d`.`created` between '2021-06-01' and '2021-07-01'),1,NULL)) AS `Jun`,count(if((`d`.`created` between '2021-07-01' and '2021-08-01'),1,NULL)) AS `Jul`,count(if((`d`.`created` between '2021-08-01' and '2021-09-01'),1,NULL)) AS `Aug`,count(if((`d`.`created` between '2021-09-01' and '2021-10-01'),1,NULL)) AS `Sep`,count(if((`d`.`created` between '2021-10-01' and '2021-11-01'),1,NULL)) AS `Oct`,count(if((`d`.`created` between '2021-11-01' and '2021-12-01'),1,NULL)) AS `Nov`,count(if((`d`.`created` between '2021-12-01' and '2022-01-01'),1,NULL)) AS `Dec` from ((`users` `u` left join `periods` `p` on((`p`.`user_id` = `u`.`id`))) left join `active_days` `d` on((`d`.`period_id` = `p`.`id`))) where ((`u`.`is_academic` = 0) and (`u`.`is_developer` = 0) and (`u`.`id` > 10) and ((`p`.`plan` = 'pro') or (`p`.`plan` = 'lite'))) group by `u`.`id` order by isnull(`p`.`date_till`),cast(max(`p`.`date_till`) as date) ;
