April 2, 2009 at 9:25 am
/*** Create Variable Table @TableVar ***/
/****Use Var Table in place of #TEMP. It requires less resources.***/
DECLARE @TableVar 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) ,
[Currency] Char(3) ,
[Latest Transactions] datetime
)
/*** Populate Variable Table Fields of @TableVar ***/
INSERT INTO @TableVar
SELECT DISTINCT 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
dbo.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)
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 @TableVar for Field Values***/
SELECT * FROM @TableVar
April 2, 2009 at 9:46 am
I'm not sure I've understood what you want, from the look of it, I think you need the last transaction for each component.
With this little information, what I've got is...
;WITH Components
AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY inboms.fcomponent ORDER BY apmast.fdfactdate DESC ) AS RowNumber, *
FROM dbo.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)
)
SELECT *
FROM Components
WHERE RowNumber = 1
BTW, you mentioned use of table variable instead of temp table. It may be it use less resources but it does not perform well on large tables as indexes and statistics cannot be created on table variables.
--Ramesh
April 2, 2009 at 10:50 am
You are correct. I do need the last transaction for each component. However which ever way I run this code it appears as though the CTE terminator (;) throws an error or two. Is there something I am missing regarding the code placement or is it something else?
Thank you for your time and input.
_____________________________________________________________
amicitia est nobility
April 2, 2009 at 11:10 am
What errors does it throw? Are you not using SQL Server 2005?
--Ramesh
April 2, 2009 at 12:04 pm
I am using SQL 2005.
Here are the errors:
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inboms.fcomponent" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inboms.fcomprev" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inmastx.fdescript" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inboms.fqty" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inmastx.fstdcost" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inmastx.flastcost" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "inmastx.fbuyer" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "apmast.fccompany" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "apmast.fccurid" could not be bound.
Msg 4104, Level 16, State 1, Line 21
The multi-part identifier "apmast.fdfactdate" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inboms.fcomponent" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inboms.fcomprev" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inmastx.fdescript" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inboms.fqty" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inmastx.fstdcost" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inmastx.flastcost" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "inmastx.fbuyer" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "apmast.fccompany" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "apmast.fccurid" could not be bound.
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "apmast.fdfactdate" could not be bound.
--Blair
____________________________________________________
amicitia est nobility
April 2, 2009 at 8:29 pm
Here is an example of finding the last of anything in 2005. Runs quick.
declare @tbl table (dept varchar(10), name varchar(10), rowID int identity(1,1))
insert into @tbl
select 'Shipping','Amy' union all
select 'Shipping','Betty' union all
select 'Shipping','Candace' union all
select 'Purchasing','Denise'
select * from @tbl
-------------------------------------------------------------------------------------------
-- in the solution below, 'dept' is whatever set of columns you need to define your "group"
-- 'rowid' is whatever column defines the sequence to determine "last"
-------------------------------------------------------------------------------------------
;with deptlist as (select distinct dept from @tbl)
select ca.*
from deptlist d
cross apply (select top 1 * from @tbl t where t.dept = d.dept order by rowID desc) ca
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 3, 2009 at 7:04 am
Your code worked perfectly when isolated outside of the Variable Table.
Thank you again for your time and input.
Blair
_______________________________________________________________
amicitia est nobility
April 3, 2009 at 7:08 am
Very nice. Thank you for you time, effort and input.
Blair
___________________________________________________________________
amicitia est nobility
April 3, 2009 at 7:32 am
You're very welcome. I will pass the credit for time and effort to the bright people who made the optimizer smart enough to produce elegant executions plans from such simple code.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply