How to find total number of records assigned to user on a particular day

[sujithraalink]

How to find total number of records assigned to user on a particular day. Please help me to resolve this.

[Andorxor]

If it is a day in the past the only place you could get this information from is the table note,but only if all your entities have a stream.

SELECT
 DATE_FORMAT(created_at,'%d.%m.%Y') as Date,
 COUNT(id) as RecordsAsigned
FROM `note`
WHERE
 `data` like '%"assignedUserName":"Bob User"%'
GROUP BY DATE_FORMAT(created_at,'%d.%m.%Y')

If you want to record this data for the future you could create a entity with a date and a number in a 1:n relation with user,than create a custom formula function that looks if such a entity for a user and date exist ,if not it should create one else it should increase the count.Than put a formula on every entity that looks if the entity is new or if the assined user has changed and than calls your custom function with the assigned user as parameter.

[Andorxor]

This Sqlstring should be a bit better than the first one which needed a username.

SELECT
 CONCAT(`user`.first_name," ",`user`.last_name) as username,
  DATE_FORMAT(`note`.created_at,'%d.%m.%Y') as Date,
  COUNT(`note`.id) as RecordsAssined
FROM  
 `note` INNER JOIN `user` ON `note`.`data` LIKE CONCAT('%"assignedUserId":"',`user`.`id`,'"%')
GROUP BY CONCAT(`user`.first_name," ",`user`.last_name),DATE_FORMAT(`note`.created_at,'%d.%m.%Y')