SELECT DISTINCT DCNAME,PALLETAREASS ,
SUM(CASE WHEN ISNULL(PALLETSTATUS,0)= 'มีสินค้า' THEN 1 ELSE 0 END) AS 'have',
SUM(CASE WHEN ISNULL(PALLETSTATUS,0)= 'ว่างเปล่า' THEN 1 ELSE 0 END) AS 'havenot',
COUNT(PALLETSTATUS) AS 'TotalPl'
FROM DC
FULL OUTER JOIN QRCODE
ON DC.DCNAME = QRCODE.PALLETSTAY
GROUP BY DCNAME,PALLETAREASS,PALLETSTATUS ORDER BY DCNAME ASC
SELECT DCNPTNAME ,
SUM(CASE WHEN PALLETSTATUS ='มีสินค้า' THEN 1 ELSE 0 END) AS 'HAVE',
SUM(CASE WHEN PALLETSTATUS ='ว่างเปล่า' THEN 1 ELSE 0 END) AS 'HAVENOT',
COUNT(PALLETAREASS) AS 'PALLETTOTAL'
FROM
(SELECT DCNPTNAME FROM AREADCNPT) a
join (SELECT PALLETAREASS,PALLETSTATUS FROM QRCODE) b
on a.DCNPTNAME = b.PALLETAREASS
GROUP BY a.DCNPTNAME,b.PALLETAREASS
Load balance : Server 00
