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

--
-- Structure for view `usage_by_week`
--

CREATE ALGORITHM=UNDEFINED DEFINER=`raimonds`@`%` SQL SECURITY DEFINER VIEW `usage_by_week`  AS  select `client_license_checks`.`user_id` AS `user_id`,`client_license_checks`.`company` AS `company`,`client_license_checks`.`location` AS `location`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-01'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w1`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-02'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w2`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-03'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w3`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-04'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w4`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-05'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w5`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-06'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w6`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-07'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w7`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-08'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w8`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-09'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w9`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-10'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w10`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-11'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w11`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-12'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w12`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-13'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w13`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-14'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w14`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-15'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w15`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-16'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w16`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-17'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w17`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-18'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w18`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-19'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w19`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-20'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w20`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-21'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w21`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-22'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w22`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-23'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w23`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-24'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w24`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-25'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w25`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-26'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w26`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-27'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w27`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-28'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w28`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-29'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w29`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-30'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w30`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-31'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w31`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-32'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w32`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-33'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w33`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-34'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w34`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-35'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w35`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-36'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w36`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-37'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w37`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-38'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w38`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-39'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w39`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-40'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w40`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-41'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w41`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-42'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w42`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-43'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w43`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-44'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w44`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-45'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w45`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-46'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w46`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-47'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w47`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-48'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w48`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-49'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w49`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-50'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w50`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-51'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w51`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2019-52'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `w52`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2020-01'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `z1`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2020-02'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `z2`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2020-03'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `z3`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2020-04'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `z4`,count(distinct if((date_format(`client_license_checks`.`local_time`,'%x-%v') like '2020-05'),date_format(`client_license_checks`.`local_time`,'%d'),NULL)) AS `z5` from `client_license_checks` where 1 group by `client_license_checks`.`user_id` order by `client_license_checks`.`user_id` ;
