Task 17(aero)

–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 utB

group

by year(b_datetime), month(b_datetime), day(b_datetime)

having

COUNT(distinct b_datetime) > 10

union

all

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 , sum(b_vol) from utB

group

by year(b_datetime), month(b_datetime)

having

COUNT(distinct b_datetime) > 10

union

all

select

ltrim(year(b_datetime)), sum(b_vol) from utB

group

by year(b_datetime)

having

COUNT(distinct b_datetime) > 10