Hello,
I got these tables and this query, but, I just can’t fix it to get the data I need, any tip, advice or comment is very wellcome.
//This table has the item's initial stock at the store
CREATE TABLE `catinvin` (
`row_id` int(11) NOT NULL auto_increment,
`cubx` decimal(3,0) NOT NULL default '0', // Store Id
`itmid` varchar(9) NOT NULL default ' ', // Item Id
`itminiu` decimal(9,2) NOT NULL default '0.00', // Initial Stock
`itminii` decimal(9,2) NOT NULL default '0.00', // Initial Stock Price
`invifec` date default NULL, // When was captured Date
`invitim` varchar(10) NOT NULL default ' ', // When was captured time
`inviusr` varchar(9) NOT NULL default ' ', // When was captured user
PRIMARY KEY (`row_id`),
KEY `invitm` (`itmid`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1
// This table contains any item's operation at any date
CREATE TABLE `acmitms` (
`row_id` int(11) NOT NULL auto_increment,
`cubx` decimal(3,0) NOT NULL default '0', // Store Id
`aitmid` varchar(9) NOT NULL default ' ', // Item Id
`aitmdat` date default NULL, // Operation Date
`aitmcaru` decimal(9,2) NOT NULL default '0.00', // Units IN
`aitmcari` decimal(12,2) NOT NULL default '0.00', // Import
`aitmabou` decimal(9,2) NOT NULL default '0.00', // Units OUT
`aitmaboi` decimal(12,2) NOT NULL default '0.00', // Import
PRIMARY KEY (`row_id`),
KEY `itm_id` (`aitmid`),
KEY `itm_dat` (`aitmdat`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
// This is Item's catalog Table
CREATE TABLE `catitms` (
`row_id` int(11) unsigned NOT NULL auto_increment,
`pidx` varchar(9) NOT NULL default ' ', // Item ID
`ppart` varchar(14) NOT NULL default ' ', // SKU
`pdesc` varchar(150) NOT NULL default ' ', // Description
`pfabr` varchar(9) NOT NULL default ' ', // Manufacturer ID
`pprod` varchar(9) NOT NULL default ' ', // Product Type ID
PRIMARY KEY (`row_id`),
KEY `itm_id` (`pidx`),
KEY `itm_prod` (`pprod`),
KEY `itm_fab` (`pfabr`)
) ENGINE=InnoDB AUTO_INCREMENT=300400 DEFAULT CHARSET=latin1
I need to get this data using 2 dates :
|<<<<<Date 1|>>>>>>>> Date 2 <<<<<<<<<< |
SKU | Description | SIU | SII | CARU | CARI | ABOU | ABOI | SFU | SFI
Where :
SIU = itminiu + aitmcaru Any date before Date1
SII = (itminiu*itminii)+(aitmcaru*aitmcari) "" "" "" ""
CARU = SUM(aitmcaru) Any date between date1 and date2
CARI = SUM(aitmcarI) "" "" "" "" ""
ABOU = SUM(aitmabou) "" "" "" "" ""
ABOI = SUM(aitmaboi) "" "" "" "" ""
SFU = ( SIU + CARU - ABOU )
SFI = ( SII + CARI - aBOI )
So far I have done this and is working :
SELECT itmnp,itmdsc,
FORMAT( COALESCE( ( SELECT
( SELECT COALESCE( cinvi1.itminiu,0)
FROM catinvin AS cinvi1
WHERE cinvi1.itmid = xitmid AND cinvi1.cubx = 101 ) +
( SELECT COALESCE( SUM(m2.aitmcaru),0)
FROM acmitms AS m2
WHERE m2.aitmid = xitmid
AND m2.aitmdat < ‘2010-02-01’
AND m2.cubx = 101 ) -
( SELECT COALESCE( SUM(m3.aitmabou),0)
FROM acmitms AS m3
WHERE m3.aitmid = xitmid
AND m3.aitmdat < ‘2010-02-01’
AND m3.cubx = 101 ) ) , 0 ),2 ) AS sldiniuns,
FORMAT( COALESCE( ( SELECT
( SELECT COALESCE( cinvi2.itminiu * cinvi2.itminii,0)
FROM catinvin AS cinvi2
WHERE cinvi2.itmid = xitmid AND cinvi2.cubx = 101 ) +
( SELECT COALESCE( SUM(m4.aitmcari),0)
FROM acmitms AS m4
WHERE m4.aitmid = xitmid
AND m4.aitmdat < ‘2010-02-01’
AND m4.cubx = 101 ) -
( SELECT COALESCE( SUM(m5.aitmaboi),0)
FROM acmitms AS m5
WHERE m5.aitmid = xitmid
AND m5.aitmdat < ‘2010-02-01’
AND m5.cubx = 101 ) ), 0 ),2 ) AS sldiniimp,
FORMAT(tcaru,2),FORMAT(tcari,2),FORMAT(tabou,2),FORMAT(taboi,2),
FORMAT( ( SELECT COALESCE(sldiniuns+tcaru-tabou,0 ) ),2 ) AS sldfinuns,
FORMAT( ( SELECT COALESCE(sldiniimp+tcari-taboi,0 ) ),2 ) AS sldfinimp,
xitmid,AES_DECRYPT(bprd.pnm, ‘mypswrd’ ) AS prdnm,
AES_DECRYPT(bfab.fnm, ‘mypswrd’ ) AS fabnm,npalta,npaltb,itmsts,0 AS isld
FROM (
SELECT
SUM(m1.aitmcaru) AS tcaru,
SUM(m1.aitmcari) AS tcari,
SUM(m1.aitmabou) AS tabou,
SUM(m1.aitmaboi) AS taboi,
m1.aitmid AS xitmid,
AES_DECRYPT(itm1.ppart, ‘mypswrd’ ) AS itmnp,
AES_DECRYPT(itm1.pdesc, ‘mypswrd’ ) AS itmdsc,
citm.pprod AS itmprd,
citm.pfabr AS itmfab,
AES_DECRYPT(itm1.pnpalta, ‘mypswrd’ ) AS npalta,
AES_DECRYPT(itm1.pnpaltb, ‘mypswrd’ ) AS npaltb,
citm.pflgai AS itmsts
FROM acmitms AS m1
LEFT JOIN rscatsbrdg.catitms AS itm1 ON m1.aitmid = itm1.pidx
LEFT JOIN catitms AS citm ON itm1.pidx = citm.pidx
WHERE citm.pfabr = ‘bDkqMSyNp’
AND m1.aitmdat BETWEEN ‘2010-02-01’
AND ‘2010-02-25’
AND m1.cubx = 101
GROUP BY xitmid
ORDER BY itmnp ) AS t
LEFT JOIN rscatsbrdg.catprds AS bprd ON itmprd = bprd.pid
LEFT JOIN rscatsbrdg.catfabs AS bfab ON itmfab = bfab.fid
ORDER BY prdnm,itmnp ;
The “problem” is, I can get only data from items that exists on acmitms and I need to get all items at the catalogo having the same manufacturer ID, it doesnt matter if the have operations or not.
Thanks a lot in advanced.