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
 To: Anders Altberg
  Where is Anders Altberg?
 Uppsala
 Sweden
 Anders Altberg
 Tags
Subject: RE: Typical Query
Thread ID: 365233 Message ID: 365311 # Views: 41 # Ratings: 0
Version: Visual FoxPro 9 SP2 Category: Databases, Tables and SQL Server
Date: Monday, December 24, 2012 6:31:31 AM         
   


> > 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

Thanx sir.

After ur adivce, Although I had gone for BOM in google and found different
topics from different author.

But hard enogh for me to understatnd. I still trying.



*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