April 1, 2009 at 12:42 pm
/*** Create Variable Table @TableName***/
DECLARE @TableName TABLE
(
Component Char(25) ,
Revision Char(3) ,
Discription varchar(35) ,
Quantity numeric(15,5) ,
[Standard Costs] numeric(17,5) ,
[Last Cost] numeric(17,5) ,
[Buyer] Char(3) ,
[Company] varchar(35) UNIQUE ,
[Currency] Char(3) ,
[Latest Transactions] datetime
)
/*** Populate Variable Table Fields of @TableName***/
INSERT INTO @TableName (Component ,
Revision ,
Discription ,
Quantity ,
[Standard Costs] ,
[Last Cost] ,
[Buyer] ,
[Company] ,
[Currency] ,
[Latest Transactions]
)
SELECT MAX(inboms.fcomponent) AS Component ,
inboms.fcomprev AS Revision ,
inmastx.fdescript AS Discription ,
inboms.fqty AS Quantity ,
inmastx.fstdcost AS [Standard Costs] ,
MAX(inmastx.flastcost) AS [Last Cost] ,
inmastx.fbuyer AS [Buyer] ,
MAX(apmast.fccompany) AS [Company] ,
apmast.fccurid AS [Currency] ,
MAX(apmast.fdfactdate) AS [Latest Transactions]
FROM
(inboms
INNER JOIN inmastx ON (inmastx.fpartno=inboms.fcomponent)
INNER JOIN apvendx ON (apvendx.fbuyer=inmastx.fbuyer)
INNER JOIN apmast ON (apmast.fccompany=apvendx.fcompany)
/***Delete all data references from apvendx which was used only as a subjoin table for inmastx and apmast ***/
DELETE FROM @TableName
WHERE apvendx.fbuyer=inmastx.fbuyer
apmast.fccompany=apvendx.fcompany
GROUP BY
inboms.fcomponent ,
inboms.fcomprev ,
inmastx.fdescript ,
inboms.fqty ,
inmastx.fstdcost ,
inmastx.flastcost ,
inmastx.fbuyer ,
apmast.fccompany ,
apmast.fccurid ,
apmast.fdfactdate )
/*** Execute Variable Table @TableName and populate grid***/
SELECT * FROM @TableName
GROUP BY
fcomponent ,
fcomprev ,
fdescript ,
fqty ,
fstdcost ,
flastcost ,
fbuyer ,
fccompany ,
fccurid ,
fdfactdate
April 1, 2009 at 12:44 pm
Aha ?!?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply