July 24, 2008 at 1:38 pm
Hi, I'm new to SSIS and I need to get the last value on an aggregate, but this option isn't available...
This is the kind of data I have in my table:
[font="Courier New"]HIST_ID PROD_ID VALUE
1 A 5
2 A 8
3 A 2
4 B 4
5 B 5
6 B 1
[/font]
Since the HIST_ID is sequential, I'd like to get the last value for each PROD_ID. So it'd be 2 for PROD_ID A and 1 for PROD_ID B, but the Aggregate transformation doesn't have lastvalue, but min and max...
Is there a way to get the last value without scripts, just using the plain transformations?
Thanks!
July 24, 2008 at 2:53 pm
You should be able to query that recordset and get the max value.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 3:12 pm
This is a new environment for me. I came from a PowerCenter/Oracle environment where most of time we use a clean query (no function) and almost all the transformations are done by PowerCenter.
I've seen that some workmates put most transformations in the query (they used to do preocedures). Maybe I have to change my reference...
So I'm not familiar to recordset (among other SQLServer features). Could you show me an example?
July 25, 2008 at 5:27 am
You are probably better off simply dealing with it in the query you use to get your data.
[font="Courier New"]CREATE TABLE #tmpData (
HIST_ID INT NOT NULL IDENTITY(1,1)
, PROD_ID CHAR(1) NOT NULL
, VALUE INT NOT NULL)
INSERT #TmpData (PROD_ID,VALUE) VALUES ('A',5)
INSERT #TmpData (PROD_ID,VALUE) VALUES ('A',8)
INSERT #TmpData (PROD_ID,VALUE) VALUES ('A',2)
INSERT #TmpData (PROD_ID,VALUE) VALUES ('B',4)
INSERT #TmpData (PROD_ID,VALUE) VALUES ('B',5)
INSERT #TmpData (PROD_ID,VALUE) VALUES ('B',1)
; WITH LastVal (PROD_ID, VALUE)
AS (
SELECT PROD_ID, VALUE
FROM #tmpData T
INNER JOIN (SELECT MAX(X.HIST_ID) AS HIST_ID
FROM #tmpData X
GROUP BY PROD_ID) Y ON T.HIST_ID = Y.HIST_ID
)
SELECT
T.*, V.VALUE AS LastValue
FROM
#tmpData T
INNER JOIN LastVal V ON T.PROD_ID = V.PROD_ID[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply