Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 Tags
Subject: Typical Query
Thread ID: 365233 Message ID: 365233 # Views: 52 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 22, 2012 4:09:42 PM         
   


Dear Expert

BackEnd : MS-SQL2005 Express
Following is Sample Codes for Examples which is finaly to be executed in SQL server.

*!* I have Following Tables
*!*	Item Mst  	: UitemID, cITemName, cType
*!*	Formula MST : nQntyMake, uItemMake, nQntyNeeded, uItemNeeded
*!*	Trn Table 	: dTrndate, uItemId, nQnty, cTrnType, uRowID
*!*	Formual_mst Contrains the Assembly details of a Product to be made.

CREATE CURSOR crs_mst_Item (uItemID c(36), cITemName c(50), cType c(10))

INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('XXXXX', 'MY BRAND GEL PEN', 'FINAL PROD')
INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('11111', 'BARREL', 'RAW MATERIAL')
INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('22222', 'CAP',    'RAW MATERIAL')
INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('33333', 'REFILL', 'RAW MATERIAL')

INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('CCCCC', 'MY BRAND TORCH-LIGHT', 'FINAL PROD')
INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('AAAAA', 'TORCH BAREL',         'RAW MATERIAL')
INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('BBBBB', 'BATTERY',         'RAW MATERIAL')
INSERT INTO crs_mst_item (UitemID, cITemName, cType) VALUES ('KKKKK', 'BULB',  	         'RAW MATERIAL')


CREATE CURSOR CRS_MST_FORMULA (nQntyMake n(10), uItemMake c(36), nQntyNeeded n(10), uItemNeeded c(36))

INSERT INTO CRS_MST_FORMULA (nQntyMake, uItemMake, nQntyNeeded, uItemNeeded) VALUES (1, 'XXXXX', 1, '11111')
INSERT INTO CRS_MST_FORMULA (nQntyMake, uItemMake, nQntyNeeded, uItemNeeded) VALUES (1, 'XXXXX', 2, '22222')
INSERT INTO CRS_MST_FORMULA (nQntyMake, uItemMake, nQntyNeeded, uItemNeeded) VALUES (1, 'XXXXX', 1, '33333')

INSERT INTO CRS_MST_FORMULA (nQntyMake, uItemMake, nQntyNeeded, uItemNeeded) VALUES (1, 'CCCCC', 1, 'AAAAAA')
INSERT INTO CRS_MST_FORMULA (nQntyMake, uItemMake, nQntyNeeded, uItemNeeded) VALUES (1, 'CCCCC', 2, 'BBBBBB')
INSERT INTO CRS_MST_FORMULA (nQntyMake, uItemMake, nQntyNeeded, uItemNeeded) VALUES (1, 'CCCCC', 1, 'KKKKKK')

INDEX on uItemMake tag iMake
INDEX on uItemNeeded TAG ineed

CREATE CURSOR crs_trn (dTrnDate d,UitemID c(36), nQnty n(10), cTrnType c(5) , uRowID c(36))
INSERT INTO CRS_TRN (dTrndate, uItemId, nQnty, cTrnType, uRowID) VALUES (DATE()-1, 'XXXXX', 50, 'ORDER', SYS(2015))
INSERT INTO CRS_TRN (dTrndate, uItemId, nQnty, cTrnType, uRowID) VALUES (DATE(),   'XXXXX', 25, 'ORDER', SYS(2015))
INSERT INTO CRS_TRN (dTrndate, uItemId, nQnty, cTrnType, uRowID) VALUES (DATE(),   'CCCCC', 75, 'ORDER', SYS(2015))
INSERT INTO CRS_TRN (dTrndate, uItemId, nQnty, cTrnType, uRowID) VALUES (DATE()+1, 'CCCCC', 10, 'ORDER', SYS(2015))

** Order List
SELECT dTrnDate, cItemName, nQnty FROM crs_trn JOIN crs_mst_item ON crs_mst_item.uitemID = crs_trn.uitemid

** Raw Material Need Requirement List  (not proper as i m novice)
SELECT dtrnDate, crs_Trn.UitemId, ;
       uITemNeeded, ;
       nQntyNeeded  ;
FROM crs_trn       ;
JOIN crs_mst_formula ON crs_mst_formula.uItemMake = uitemID ;
ORDER BY 1

wish to have result showing the breakup of Trn Trable entered with Formual Master.


Order_Item    		Order_Qty      Raw_Material        RM_Qnty

MY BRAND TORCH-LIGHT           75      TORCH-BAREL          75
MY BRAND TORCH-LIGHT           75      BATTERY             150
MY BRAND TORCH-LIGHT           75      BULB                 75


Hope , I had made a clear picture for my problem.

Please help.
TIA




*Exchange of $1 Create only $1, But Exchage of one Ideas Makes different two Ideas*

ENTIRE THREAD

Typical Query Posted by Binod Binani @ 12/22/2012 4:09:42 PM
RE: Typical Query Posted by Anders Altberg @ 12/22/2012 7:05:22 PM
RE: Typical Query Posted by Binod Binani @ 12/24/2012 6:31:31 AM