June 3, 2010 at 6:28 pm
I am trying to find the column value previous row. i know there are couple of ways, but as the requirements are bit different here to find the previous record column value, i need your help.
the table structure is
CREATE TABLE [dbo].[tmp_tt_test1](
PK_ID [numeric](11, 0) NOT NULL,
ExistingCol [datetime] NULL,
NewCol [datetime] NULL,
TType [varchar](2) COLLATE Latin1_General_CI_AS NULL,
GroupID varchar(10) NULL,
[rowVer] [bigint] NULL
)
The sample data is attached in the spreadsheet. In the spreadsheet you'll find two sections, one is with source and other target. In Target section, NewCol is the one which gets populated.
Now the requirement is:
For everyrecord in the table where TType = 61, get the value of previous record from ExistingCol to NewCol where TType <> 61.
For example, where GroupID = 'A1', all the records with rowVer 2-5 will have column value of RowVer = 1.
In GroupID = 'A2', rowVer 2 will have value of RowVer 1 and rowVer 5 will have value of RowVer 4
In GroupID = 'A3', rowVer 3 will have value of RowVer 2
This above table only contains testing data. In reality total number of GroupID 1,087,616 and number of records falling in these groupID will be 9,811,511
Therefore, i am not sure how i can do this. I have written a cursor (please dont go the way of using a cursor but i was unable to think of other solution) which is taking too long.
Then i wrote a CTE but my CTE is not handling data with pattern in groupID 'A1'. Just on curiosity, please advice is it a good option to use CTE when data set is this much huge? Is is possible to run an Update Statement with CTE? if not, then i am thinking of storing CTE results into tmp table and run an update statement.
WITH expCTE (PK_ID, ExistingCol, NewCol, TType, GroupID, rowVer) AS
(SELECT PK_ID, ExistingCol, NewCol, TType, GroupID, rowVer
FROM NDHS.dbo.[tmp_tt_test111]
WHERE TType = '61'),
tt61CTE( PK_ID, ExistingCol, NewCol, TType, GroupID, rowVer) AS
(SELECT PK_ID, ExistingCol, NewCol, TType, GroupID, rowVer
FROM NDHS.dbo.[tmp_tt_test111] t
WHERE TType <> '61')
SELECT a.PK_ID, b.GroupID, b.ExistingCol, b.NewCol, max(b.PK_ID)
FROM expCTE a
inner join tt61CTE b on a.GroupID = b.GroupID
WHERE a.rowver = b.rowver + 1
GROUP BY a.PK_ID, b.GroupID, b.ExistingCol, b.NewCol
HAVING MAX(b.PK_ID) < a.PK_ID
June 3, 2010 at 6:55 pm
Anan,
With 380+ points, you should know how to post data here in a readily consumable format so that all of us volunteers can just cut-and-paste it, and start working. Seems you've forgotten, so please take a look at the first link in my signature and then post your sample data in the requested format. THEN, please tell what your expected results (based upon the sample data provided) should be.
Once you HELP US HELP YOU, I'm sure that there will be many more people willing to help you.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply