–Group the all paintings by days, months and years separately.
–Format the identifier of each group as follows: "yyyy" for a year, "yyyy-mm" for a month and "yyyy-mm-dd" for a day.
–Get only those groups that include more than 10 paintings at distinct moments of time (b_datetime).
–Result set: group identifier, the total quantity of a paint used within a group.
–period vol
–2003-01-01 8679
–2003-01 8679
–2003 9070
–One should take into account the period of time, and not the number painting works, as within one time period the square(s) can be painted several times.
–The mistake leads to counting the groups in which there are more than 10 paintings, though the number of various moments of time is less than 10.
select
ltrim(str(year(b_datetime))) + ‘-‘ + case when month(b_datetime) > 9 then ltrim(str(month(b_datetime))) else ‘0’ + ltrim(str(month(b_datetime))) end + ‘-‘ + case when day(b_datetime) > 9 then ltrim(str(day(b_datetime))) else ‘0’ + ltrim(str(day(b_datetime))) end ,sum(b_vol) from utBgroup
by year(b_datetime), month(b_datetime), day(b_datetime)having
COUNT(distinct b_datetime) > 10union
allselect
ltrim(str(year(b_datetime))) + ‘-‘ + case when month(b_datetime) > 9 then ltrim(str(month(b_datetime))) else ‘0’ + ltrim(str(month(b_datetime))) end , sum(b_vol) from utBgroup
by year(b_datetime), month(b_datetime)having
COUNT(distinct b_datetime) > 10union
allselect
ltrim(year(b_datetime)), sum(b_vol) from utBgroup
by year(b_datetime)having
COUNT(distinct b_datetime) > 10