Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 To: Binod Binani
  Where is Binod Binani?
 Kolkata
 India
 Binod Binani
 Tags
Subject: RE: Typical Query
Thread ID: 365233 Message ID: 365244 # Views: 53 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Saturday, December 22, 2012 7:05:22 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*

The whole subject of 'bom' bill-of-materials analysis in SQL databases has received a lot of attention. Check the Internet for
BOM BILL OF MATERIALS SQL

-Anders

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