Changes between Version 12 and Version 13 of AdvancedReports

Timestamp:

02/25/23 15:19:18 (21 months ago)

Author:

201065

Comment:

--

AdvancedReports

@@ -3 +3 @@
 ===  Извештај за најдобрите магационери во однос на нивната највредна креирана фактура. Се прикажуваат информации за вкупната сума во фактурата која ја наплатиле и дополнително се прикажува фактурираниот артикл кој имал најголема вкупна цена во истата таа фактура===
 {{{#!sql
-select u.firstname, u.lastname, u.username, u.email, max(ti.totalPrice) as topInvoiceSum, 
-(select a.articlename from articles a
-        left join invoicedarticles i2 on i2.articleid=a.articleid
-        where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleName,
-(select i2.price  from articles a
-        left join invoicedarticles i2 on i2.articleid=a.articleid
-        where i2.price*i2.quantity=max(ba.totalPrice)) as topArticlePrice,
-(select i2.quantity from articles a
-        left join invoicedarticles i2 on i2.articleid=a.articleid
-        where i2.price*i2.quantity=max(ba.totalPrice)) as topArticleQuantity,
-max(ba.totalPrice) as topArticleTotalPrice from workers w 
-                left join users u on u.userid=w.userid
-                left join 
-                        (
-                                select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from invoices i
-                                left join invoicedarticles ia on ia.invoiceid=i.invoiceid
-                                group by i.invoiceid, i.workeruserid
-                        ) ti on ti.workeruserid=u.userid
-                left join
-                        (
-                                select ia2.invoiceid, a.articlename, ia2.price as price, ia2.quantity as quantity, ia2.price*ia2.quantity as totalPrice from invoicedarticles ia2
-                                left join articles a on a.articleid=ia2.articleid
-                        ) ba on ba.invoiceid=ti.invoiceid
-                group by u.userid
-                order by topInvoiceSum desc
-                limit 5
+select u.firstname, u.lastname, u.username, u.email, coalesce(max(ti.totalPrice), 0) as topInvoiceSum,
+(select a.articlename from project.articles a
+    left join project.invoicedarticles i2 on i2.articleid=a.articleid
+    where i2.price*i2.quantity=max(ba.totalPrice)
+    limit 1) as topArticleName,
+(select i2.price  from project.articles a
+    left join project.invoicedarticles i2 on i2.articleid=a.articleid
+    where i2.price*i2.quantity=max(ba.totalPrice)
+    limit 1) as topArticlePrice,
+(select i2.quantity from project.articles a
+    left join project.invoicedarticles i2 on i2.articleid=a.articleid
+    where i2.price*i2.quantity=max(ba.totalPrice)
+    limit 1) as topArticleQuantity,
+max(ba.totalPrice) as topArticleTotalPrice from project.workers w
+        left join project.users u on u.userid=w.userid
+        left join
+            (
+                select i.invoiceid, i.workeruserid , sum(ia.price*ia.quantity) as totalPrice from project.invoices i
+                left join project.invoicedarticles ia on ia.invoiceid=i.invoiceid
+                group by i.invoiceid, i.workeruserid
+            ) ti on ti.workeruserid=u.userid
+        left join
+            (
+                select ia2.invoiceid, a.articlename, ia2.price as price, ia2.quantity as quantity, ia2.price*ia2.quantity as totalPrice from project.invoicedarticles ia2
+                left join project.articles a on a.articleid=ia2.articleid
+            ) ba on ba.invoiceid=ti.invoiceid
+        group by u.userid
+        order by topInvoiceSum desc
+        limit 5
 }}}