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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s