SELECT `SUPPLIER`, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) GROUP_5 GROUP BY CONTACT_NAME ORDER BY SUPPLIER;
/********************************************************************************/
SET @MCOUNT=0;
SELECT LEFT(VENDOR.NAME,20)
,ADDRESS_BOOK.NAME
,ADDRESS_BOOK.PHONE DIRECT_LINE
,VENDOR.PHONE_NO GENERAL_LINE
,ADDRESS_BOOK.EMAIL
,IF(IFNULL(Z3.COUNT_,0)>1,'*','') r
,VENDOR.COUNTER_ RANK
FROM
(SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
where po_date>=20171201
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
) Z
LEFT JOIN
(SELECT * FROM
(SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
where po_date>=20171201
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY SUPPLIER ORDER BY SUPPLIER
) Z)Z1
ON Z.SUPPLIER=Z1.SUPPLIER AND Z.PO_DATE=Z1.PO_DATE
LEFT JOIN
(SELECT SUPPLIER, PO_DATE, COUNT(1) COUNT_ FROM (
SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
where po_date>=20171201
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
) Z2 GROUP BY SUPPLIER) Z3
ON Z.SUPPLIER=Z3.SUPPLIER AND Z.PO_DATE=Z3.PO_DATE
LEFT JOIN
(SELECT
VENDOR.NAME
,VENDOR.PHONE_NO
,VENDOR.TOTAL_AMOUNT_2018
,RECORDID
,@MCOUNT:=@MCOUNT+1 COUNTER_
FROM VENDOR
ORDER BY TOTAL_AMOUNT_2018
DESC
)
VENDOR ON VENDOR.RECORDID=Z.SUPPLIER
LEFT JOIN ADDRESS_BOOK ON ADDRESS_BOOK.RECORDID=Z.CONTACT_NAME
where not isnull(ADDRESS_BOOK.NAME)
order by VENDOR.name
;
/*****************************************************************************/
SELECT LEFT(VENDOR.NAME,20),ADDRESS_BOOK.NAME,ADDRESS_BOOK.EMAIL,IF(IFNULL(Z3.COUNT_,0)>1,'*','') FROM
(SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
) Z
LEFT JOIN
(SELECT * FROM
(SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY SUPPLIER ORDER BY SUPPLIER
) Z)Z1
ON Z.SUPPLIER=Z1.SUPPLIER AND Z.PO_DATE=Z1.PO_DATE
LEFT JOIN
(SELECT SUPPLIER, PO_DATE, COUNT(1) COUNT_ FROM (
SELECT SUPPLIER, MAX(PO_DATE) PO_DATE,CONTACT_NAME
FROM (
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,CONTACT_NAME
from
(
select PURCHASE_ORDER, `SUPPLIER`, PO_DATE,
(@num:=if(@SUPPLIER = `SUPPLIER`, @num +1, if(@SUPPLIER := `SUPPLIER`, 1, 1))) row_number
,CONTACT_NAME
from PURCHASE_HEADER t
CROSS JOIN (select @num:=0, @SUPPLIER:=null) c
order by `SUPPLIER`, PO_DATE desc, PURCHASE_ORDER
) as x
where x.row_number <= 5
) Y GROUP BY CONTACT_NAME ORDER BY SUPPLIER
) Z2 GROUP BY SUPPLIER) Z3
ON Z.SUPPLIER=Z3.SUPPLIER AND Z.PO_DATE=Z3.PO_DATE
LEFT JOIN
VENDOR ON VENDOR.RECORDID=Z.SUPPLIER
LEFT JOIN ADDRESS_BOOK ON ADDRESS_BOOK.RECORDID=Z.CONTACT_NAME
;
