There was this cron job that I made that saves daily on-hand quantity of our items/goods, and my boss just needed one of our principal’s items trade prices with VAT be computed with that data for the month of July this year.

On a specific day, I noticed there were duplicate rows saved in the table, so I had to remove them or fetch it and save them to another table.

{code type=sql}

INSERT INTO `distinct_table` SELECT brand,item_code,
item_desc,qoh FROM getz_stockstatus
WHERE EXTRACT(month FROM date_inserted) =’7′
AND EXTRACT(DAY FROM date_inserted) =’2′ AND
EXTRACT(year FROM date_inserted) = ’2008′
{/code}

and then combine them as one query using this code:
{code type=sql}
(SELECT EXTRACT(
MONTH FROM date_inserted ) AS tmonth, EXTRACT(
DAY FROM date_inserted ) AS tday, EXTRACT( YEAR
FROM date_inserted ) AS tyear,brand,
item_code, item_desc, SUM( qoh ) AS total_quantity_on_hand
FROM getz_stockstatus
WHERE EXTRACT(
MONTH FROM date_inserted ) = ’7′
AND EXTRACT( YEAR
FROM date_inserted ) = ’2008′
AND EXTRACT(
DAY FROM date_inserted ) <> ’2′
AND brand
IN (< brand_names > )
GROUP BY EXTRACT(
MONTH FROM date_inserted ) , EXTRACT(
DAY FROM date_inserted ) , EXTRACT( YEAR
FROM date_inserted ) , brand, item_code, item_desc
ORDER BY brand, item_code, EXTRACT(
MONTH FROM date_inserted ) , EXTRACT(
DAY FROM date_inserted ))
UNION ALL
(SELECT  EXTRACT(
MONTH FROM date_inserted ) AS tmonth, EXTRACT(
DAY FROM date_inserted ) AS tday, EXTRACT( YEAR
FROM date_inserted ) AS tyear, brand,
item_code, item_desc, SUM( qoh ) AS total_quantity_on_hand
FROM distinct_table
WHERE brand
IN (
< brand_names >
)
GROUP BY EXTRACT(
MONTH FROM date_inserted ), EXTRACT(
DAY FROM date_inserted ), EXTRACT( YEAR
FROM date_inserted ) , brand, item_code, item_desc)
ORDER BY brand,item_code,item_desc,tmonth,tday,tyear
{/code}

Now, to compute for their total trade prices with VAT included, I had to make a table where it contains price lists for each of the items of our principal.

{code type=sql}
(SELECT EXTRACT(
MONTH FROM date_inserted ) AS tmonth, EXTRACT(
DAY FROM date_inserted ) AS tday, EXTRACT( YEAR
FROM date_inserted ) AS tyear, brand, item_code, getz_stockstatus.item_desc, SUM( qoh ) AS total_quantity_on_hand,
(SUM(qoh)*trade_price_with_vat) AS total_trade_price
FROM getz_stockstatus
INNER JOIN getz_tradeprices ON
getz_tradeprices.item_number = getz_stockstatus.item_code
WHERE EXTRACT(
MONTH FROM date_inserted ) = ’7′
AND EXTRACT( YEAR
FROM date_inserted ) = ’2008′
AND EXTRACT(
DAY FROM date_inserted ) <> ’2′
AND brand
IN (

)
GROUP BY EXTRACT(
MONTH FROM date_inserted ) , EXTRACT(
DAY FROM date_inserted ) , EXTRACT( YEAR
FROM date_inserted ) , brand, item_code, item_desc
ORDER BY brand, item_code, EXTRACT(
MONTH FROM date_inserted ) , EXTRACT(
DAY FROM date_inserted ))
UNION ALL
(SELECT EXTRACT(
MONTH FROM date_inserted ) AS tmonth, EXTRACT(
DAY FROM date_inserted ) AS tday, EXTRACT( YEAR
FROM date_inserted ) AS tyear, brand, item_code, distinct_table.item_desc, SUM( qoh ) AS total_quantity_on_hand,
(SUM(qoh)*trade_price_with_vat) AS total_trade_price
FROM distinct_table
INNER JOIN getz_tradeprices ON
getz_tradeprices.item_number = getz_stockstatus.item_code
WHERE brand
IN (

)
GROUP BY EXTRACT(
MONTH FROM date_inserted ), EXTRACT(
DAY FROM date_inserted ), EXTRACT( YEAR
FROM date_inserted ) , brand, item_code, item_desc)
ORDER BY brand,item_code,item_desc,tmonth,tday,tyear
{/code}