Get a Freelance Job

I need this query completed right away. I am not able to provide access or give you a copy of any part of the database. I am online right now on MSN Messenger to work with you though on getting this done right away. Please only bid if you understand the project. If you have any questions message.

This project uses 5 queries.

1. Retrieve the Group Items
2. Check each individual item combination
3. Check inventory for each individual item combination
4. If IN STOCK is greater than zero run query4 if not run query5.

QUERY1:
SELECT * FROM ITEMS
WHERE CUSTCHAR3 LIKE ‘%ST%’ AND MATRIX_ITEM_TYPE = ’0′

*** FOREACH RESULT FOR OF QUERY1 RUN QUERY2: ***

QUERY2:
SELECT * FROM MATRIX_ITEMS
WHERE MATRIX_GROUP_CODE = ”)

*** FOREACH RESULT OF QUERY2 RUN QUERY 3. ***

QUERY3:
SELECT * FROM ITEMS
WHERE Q_ON_RESER < QTY_STK AND QUERY2.MATRIX_ITEMS.ITEM_CODE

IF QUERY3 RETURNS A RESULT UPDATE INSTOCK BY ’1′

** FINALLY IF IN STOCK IS GREATER THAN 0 RUN QUERY 4 OTHERWISE RUN QUERY 5 **

QUERY4:

INSERT INTO ITEM_EQ
(ACTIVE,EQ_CODE,ITEMNO,DESCRIPT,SELLPRIC_O,CREATED_BY,CREAT_DATE,WEB_ACTIVE,CATEGORY,CUSTCHAR3,MEAS_CODE)
SELECT ‘T’,Items.ITEMNO + ‘-2′,ITEMS.ITEMNO,ITEMS.DESCRIPT,’F',’SUP32′,GETDATE(),’T',CASE
WHEN Items.Category LIKE ‘BB%’ THEN ‘BUCKLES’
ELSE ‘TLS’
END
,’[ST]‘,’EA’
FROM ITEMS
WHERE CUSTCHAR3 LIKE ‘%ST%’ AND ACTIVE = ‘T’ AND WEB_ACTIVE = ‘T’
AND ITEMS.ITEMNO = ITEMS.ITEMNO (FROM QUERY1)
and not exists (select * from ITEM_EQ Where EQ_CODE = ITEMS.ITEMNO (FROM QUERY1) + ‘-2′);

QUERY5:

DELETE FROM ITEM_EQ
WHERE EXISTS
( select ITEMS.ITEMNO
from ITEMS
where ITEMS.ITEMNO = ITEMS.ITEMNO (FROM QUERY1) AND ITEMS.QTY_STK = 0 AND ITEMS.CUSTLOG4 = ‘F’ AND CUSTCHAR3 LIKE ‘%ST%’ AND ITEM_EQ.CREATED_BY = ‘SUP32′);

I currently make my own leather goods and use specific items to make them. I need an all SQL QUERY written that will tell me how much of each
specific item I used (Example, Snaps, Studs, Leather, etc…) to make one item. Then it needs to calculate that by the total sales for a
given date period and then show me a report totalling the usage of each item. I need this to be done in all SQL so I can use it in an existing
program that only supports custom sQL QUERIES.

1. SELECT x_invoic.x_invoice_id, x_invoic.qty_ship, items.descript, items.brand,
invoices.date_fld
FROM (x_invoic INNER JOIN items ON x_invoic.item_code = items.itemno)
INNER JOIN invoices ON (x_invoic.status = invoices.status) AND (x_invoic.order_no = invoices.doc_no)
WHERE invoices.status=’9′ AND items.matrix_item_type=’2′ AND invoices.date_fld >= ’2008-01-01′ AND invoices.date_fld <= ’2009-01-01′
AND items.brand=’solesu’AND x_invoic.dept <> ‘wholesale’

2. SELECT * FROM ITEM_UPG WHERE ITEM_CODE = ITEMS.ITEMNO (FROM STEP 1)

3. Total up the quantity used of each item (as found in the UPG_PRIC field from Step 2.)

4. Retrieve the last_cost field of each item retrieved in Step 2.

SELECT ITEMNO,LAST_COST FROM ITEMS WHERE ITEMNO = ITEM.UPG.UPG_CODE (from Step 2) and display it in a column.

FINAL RESULT EXAMPLE (FIELDS FROM ITEM_UPG):

ITEM_CODE | UPG_CODE | LAST_COST | QUANTITY TOTAL USED
BUCKLE | 24 | 2.56 | 34
SNAP-A | 12 | 2.24 | 23
LEATHER-A | 0.5 | 3.50 | 12.5