SQL (mysql) statements

MySQL statements; can be used in mysql prompt or phpMyAdmin.
One of the MySQL versions this program is made for is pre-4.1 ( 4.0.15 no sub-selects possible)

DB

20080812: show invoices+cn with cost detail

SELECT inv.inv_id, inv_docseq, inv_amount, inv_bkamount AS booked, left(inv_description, 20), ixc_amount, cpl_code, ixc_remark, sup_name
FROM `ixc`, `inv`, `sup`
WHERE inv.inv_id = ixc.inv_id AND sup.sup_id=inv.sup_id

20080812: show totals / costplace: (e.g.this year ixc.ixc_year=”2008″)

SELECT cpl.cpl_code, sum(ixc.ixc_amount), YEAR(NOW()) AS year
FROM `ixc`, cpl
WHERE cpl.cpl_code=ixc.cpl_code AND ixc.ixc_year=YEAR(NOW())
GROUP BY cpl.cpl_code

20080812: total amount assignments / invoice+cn

SELECT ixc.inv_id, inv.inv_docseq, sum(ixc.ixc_amount)
FROM `ixc`, inv
WHERE inv.inv_id=ixc.inv_id AND ixc.ixc_year=YEAR(NOW())
GROUP BY ixc.inv_id
ORDER BY inv.inv_docseq

20080813: show empty invoices+cn this year, i.e. book amount is 0

SELECT *
FROM `inv`
WHERE `inv_bkamount` = 0 AND YEAR( inv_docdate ) = YEAR( NOW( ) )

20080813: show invoices+cn in foreign value, i.e. invoice amount <> book amount

SELECT *
FROM `inv`
WHERE `inv_bkamount` <> inv_amount AND YEAR( inv_docdate ) = YEAR( NOW( ) )

20080814: show Creditnotes of this year

SELECT *
FROM `inv`
WHERE inv_decr = "C" AND YEAR(inv_docdate) = YEAR(NOW())

20080822: show invoices that have no lines assigned

SELECT inv.inv_id, inv_docseq, inv_creda, ixc.ixc_id
FROM inv
LEFT JOIN ixc ON inv.inv_id = ixc.inv_id
WHERE ixc.inv_id IS NULL

20080822: sum of cost assignments for invoice ‘111′

SELECT SUM(ixc.ixc_amount)
FROM ixc
WHERE ixc.inv_id='111'

20080826: Cost assignments for a given month (e.g. this year last month)

SELECT cpl.cpl_code, MONTH(inv_docdate) AS mnth, YEAR(NOW()) AS year, SUM( ixc.ixc_amount )
FROM `ixc` , cpl, inv
WHERE cpl.cpl_code = ixc.cpl_code AND ixc.ixc_year = YEAR( NOW( ) ) AND MONTH(inv_docdate) = MONTH( NOW() ) -1 AND inv.inv_id = ixc.inv_id AND cpl.cpl_valid='T'
GROUP BY cpl.cpl_code

20080826: Cost assignments for a given month biggest amount first

SELECT cpl.cpl_code, MONTH(inv_docdate) AS mnth, YEAR(NOW()) AS year, SUM( ixc.ixc_amount ) AS costsubtot
FROM `ixc` , cpl, inv
WHERE cpl.cpl_code = ixc.cpl_code AND ixc.ixc_year = YEAR( NOW( ) ) AND MONTH(inv_docdate) = MONTH( NOW() ) -1 AND inv.inv_id = ixc.inv_id AND cpl.cpl_valid='T'
GROUP BY cpl.cpl_code
ORDER BY costsubtot DESC

20080827: Number of Invoices booked on supplier with sup_id

SELECT COUNT(inv.sup_id)
FROM inv
WHERE inv.sup_id='38'

TODO:
All invoices for supplier (sup_id)
All invoices related to costplace x


Comments

Comments are closed.