Напредни извештаи од базата (SQL и складирани процедури)
Извештај за најдобрите магационери во однос на нивната највредна креирана фактура. Се прикажуваат информации за вкупната сума во фактурата која ја наплатиле и дополнително се прикажува фактурираниот артикл кој имал најголема вкупна цена во истата таа фактура
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) and i2.invoiceid = min(ti.invoiceid) ) 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) and i2.invoiceid = min(ti.invoiceid) ) 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) and i2.invoiceid = min(ti.invoiceid) ) 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
Извештај за најнепрофитабилната категорија на артикли. Се прикажуваат информации за името на категоријата, нејзиниот профит и информации за вкупниот и просечниот прилив и одлив по артикл
select c.categoryname, prihod.pri-odliv.odl as profit, prihod.pri as priliv, odliv.odl as odliv, (select avg(i2.quantity*i2.price) from invoicedarticles i2) as avgPrihod, (select avg(o2.quantity*o2.price) from orderedarticles o2) as avgOdliv from categories c left join ( select abtc.categoryid, coalesce(sum(i.quantity*i.price), 0) as pri from articles a left join article_belongs_to_category abtc on abtc.articleid=a.articleid left join invoicedarticles i on i.articleid=a.articleid group by abtc.categoryid ) prihod on prihod.categoryid=c.categoryid left join ( select abtc.categoryid, coalesce(sum(o.quantity*o.price), 0) as odl from articles a left join article_belongs_to_category abtc on abtc.articleid=a.articleid left join orderedarticles o on o.articleid=a.articleid group by abtc.categoryid )odliv on odliv.categoryid=c.categoryid where prihod.pri-odliv.odl=(select min(prihod.pri-odliv.odl) from categories c left join ( select abtc.categoryid, coalesce(sum(i.quantity*i.price), 0) as pri from articles a left join article_belongs_to_category abtc on abtc.articleid=a.articleid left join invoicedarticles i on i.articleid=a.articleid group by abtc.categoryid ) prihod on prihod.categoryid=c.categoryid left join ( select abtc.categoryid, coalesce(sum(o.quantity*o.price), 0) as odl from articles a left join article_belongs_to_category abtc on abtc.articleid=a.articleid left join orderedarticles o on o.articleid=a.articleid group by abtc.categoryid )odliv on odliv.categoryid=c.categoryid)
Предвидување дали одреден артикл во одреден магацин треба да се нарача во однос на неговота достапност и потрошувачка во последниот месец дена
select a.articlename, l.locationname, (case when q1.dostapnost+coalesce(q2.incoming, 0)<coalesce(q3.sold, 0) then 'Yes' else 'No' end ) as defict from articles a left join storedarticles s on s.articleid=a.articleid left join locations l on l.locationid=s.locationid left join ( select articleid, locationid, coalesce(sum(quantity), 0) as dostapnost from storedarticles s group by s.sarticleid, s.locationid ) q1 on q1.articleid=a.articleid and q1.locationid=l.locationid left join ( select articleid, locationid, coalesce(sum(quantity), 0) as incoming from orderedarticles o where o.articlestatus='ORDERED' or o.articlestatus='DELIVERED' group by o.articleid, o.locationid ) q2 on q2.articleid=a.articleid and q2.locationid=l.locationid left join ( select i.articleid, l.locationid, coalesce(sum(quantity), 0) as sold from invoicedarticles i left join invoices inv on inv.invoiceid=i.invoiceid and inv.datecreate between now()-interval '1 month' and now() left join workers w on w.userid=inv.workeruserid left join locations l on l.locationid=w.locationid group by i.articleid, l.locationid )q3 on q3.articleid=a.articleid and q3.locationid=l.locationid order by a.articlename
Извештај за профитот на сите магацини според набавени артикли и продадени артикли во период од една година
select q1.locationname, prihod-odliv as profit from ( select l.locationid, l.locationname, coalesce(sum(i.price*i.quantity), 0) as prihod from locations l left join storedarticles s on s.locationid=l.locationid left join invoicedarticles i on i.articleid=s.articleid left join invoices i2 on i2.invoiceid=i.invoiceid and i2.datecreate between now()-interval '1 year' and now() group by l.locationid ) as q1 full outer join ( select l.locationid, l.locationname, coalesce(sum(o.price*o.quantity), 0) as odliv from locations l left join orderedarticles o on o.locationid=l.locationid left join orders o2 on o2.orderid=o.orderid and o2.datecreated between now()-interval '1 year' and now() group by l.locationid ) q2 on q2.locationid=q1.locationid
Извештај за најпопуларен артикл по категорија
select c2.categoryname, (select a.articlename from articles a left join invoicedarticles i on i.articleid=a.articleid left join article_belongs_to_category abtc on abtc.articleid=a.articleid where abtc.categoryid = c2.categoryid group by a.articleid having coalesce(sum(i.quantity),0)=max(q1.vkolicina) limit 1), max(q1.vkolicina) as vkolicinia from categories c2 left join( select c.categoryid, c.categoryname, articlename, (coalesce(sum(i.quantity),0)) as vkolicina from categories c left join article_belongs_to_category abtc on abtc.categoryid=c.categoryid left join articles a on a.articleid=abtc.articleid left join invoicedarticles i on i.articleid=a.articleid group by c.categoryid, a.articleid ) q1 on q1.categoryid=c2.categoryid group by c2.categoryid
Извештај за набавувачите со најголема заработка
select s.supplierinfo, u.firstname, u.lastname, u.username, coalesce(sum(o2.quantity), 0) as koldostavena, coalesce(sum(o2.quantity*o2.price)) as zarabotkasupp from suppliers s left join users u on u.userid=s.userid left join orders o on o.supplieruserid=s.userid and (o.status='DELIVERED' or o.status='PROCESSED') left join orderedarticles o2 on o2.orderid=o.orderid and (o2.articlestatus='DELIVERED' or o2.articlestatus='PROCESSED') group by s.userid, u.userid order by zarabotkasupp desc
Last modified
20 months ago
Last modified on 02/28/23 13:43:37
Note:
See TracWiki
for help on using the wiki.