Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

02/12/24 01:10:19 (13 months ago)

Author:

141515

Comment:

--

AdvancedReports

@@ -1 +1 @@
 = Напредни извештаи од базата (SQL и складирани процедури)
 
+===Извештај за квартално продадени автомобили и приход на едно застапништво (во овој случај БМВ М Кар), како и просечен месечен профит во тој период. 
+{{{
+select
+(
+select count(a.a_id) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 1 and 3)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as first_quartal_sold_cars,
+(
+select sum(a.price) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 1 and 3)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as first_quartal_profit,
+(
+select count(a.a_id) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 4 and 6)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as second_quartal_sold_cars,
+(
+select sum(a.price) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 4 and 6)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as second_quartal_profit,
+(
+select count(a.a_id) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 7 and 9)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as third_quartal_sold_cars,
+(
+select sum(a.price) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 7 and 9)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as third_quartal_profit,
+(
+select count(a.a_id) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 10 and 12)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as fourth_quartal_sold_cars,
+(
+select sum(a.price) from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where (extract(month from a.datum) between 10 and 12)
+and (extract(year from a.datum)=2023)
+and d.d_name='BMW M-Kar'
+) as fourth_quartal_profit,
+(
+select sum(a.price)/12 from dealership as d
+left join agreement as a on a.tax_nr=d.tax_nr
+where d.d_name='BMW M-Kar'
+) as average_monthly_profit;
+}}}
 
 === Извештај за тоа во колку договори учествувало секое претставништво, излистани името на претставништвото, бројот на договори во кои учествувало, како и целосната сума, но само доколку вкупната сума на сите договори заедно е поголема од 2000е.