Đã hoàn thành

304797 SQL Query (Re Post)

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.

The queries I believe needed are below.

QUERY 1:

SELECT x_invoic.x_invoice_id, x_invoic.qty_ship, [url removed, login to view], [url removed, login to view],

invoices.date_fld

FROM (x_invoic INNER JOIN items ON x_invoic.item_code = [url removed, login to view])

INNER JOIN invoices ON ([url removed, login to view] = [url removed, login to view]) 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 [url removed, login to view] <> 'wholesale'

foreach query1 we need to loop through the results.

and run this subquery1

SELECT ITEM_UPG.*,ITEMS.LAST_COST

FROM ITEM_UPG, ITEMS

WHERE ITEM_UPG.ITEM_CODE = X_INVOIC.ITEM_CODE (FROM QUERY 1) AND [url removed, login to view] = ITEM_UPG.ITEM_CODE

THEN FOR EACH SUB QUERY1 we need loop thru the results

FINALLY WE NEED TO ELIMINATE DUPLICATES AND MERGE THEIR ITEM_UPG.UPG_PRIC field (this is the quantity

field so the total needs to be tabulated and displayed in one row).

Note: Some leather items we make use some of the same raw materials as define din the ITEM_UPG table.

Therefor you will have duplicates showing for hardware item and need to eliminate them and merge their

UPG_PRIC (QUANTITY FIELD) somehow to tabluate the total quantity.

FINAL DISPLAY RESULT SHOULD SHOW SUBQUERY1 AFTER DUPLICATES

HAVE BEEN REMOVED AND THE UPG_PRIC (QUANTITY FIELD) HAS BEEN TABULATED ACCORDINGLY.

ITEM_CODE | COST PER ITEM | TOTAL QUANTITY USED

BUCKLE1 | 12 | 24

BUCKLE3 | 11 | 12

LEATHER | 8 | 12.3

NOTE: I do not have the ability to provide access to our database or data. YOu will have to work with me on messenger or via email to have me test this as you work on it.

THIS MUST BE DONE TODAY. I AM ON MSN MESSENGER NOW WAITING TO WORK WITH THE PROGRAMMER CHOSEN.

Kĩ năng: Bất kì công việc gì, SQL

Xem nhiều hơn: sql database programmer, programmer sql, programmer needed cost, n query, define total cost, sql work needed, SQL Query, sql queries, sql duplicates, sql database report, SQL Access, report sql, query table, my sql work, make loop, LEATHER GOODS, data query, access to sql, sql query loop, sql final, query loop, access query table, calculate quantity, merge results, custom sql queries

Về Bên Thuê:
( 59 nhận xét ) Belleville, United States

ID dự án: #2050588

Được trao cho:

HECTORVer01

Will be done....Ready to start the work...

$30 USD trong 1 ngày
(0 Đánh Giá)
0.0