April 9, 2012 at 9:14 am
Hi,
I am not able to figure out the order of where to set my variables so I can update the IsCurr Column to show what was the Prior MRN value... I've added a column "SHOULD_BE" to show you what I'm trying to accomplish...
Thank you...
IF OBJECT_ID('tempdb..#GETID') IS NOT NULL drop table #GETID
CREATE TABLE #GETID
(
MRN VARCHAR(6)
, IsCurr VARCHAR(31)
, SHOULD_BE VARCHAR(31)
)
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('A', NULL)
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('A', 'A')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('B', 'A')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('B', 'B')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('C', 'B')
DECLARE @MRN AS VARCHAR(31)
DECLARE @P_MRN AS VARCHAR(31)
SET @P_MRN = 0
SET @MRN = 0
-- just clear out for multi runs...
UPDATE
#GETID
SET
IsCurr = NULL
--
UPDATE
E
SET
@MRN = E.MRN
, E.IsCurr = @P_MRN
, @P_MRN = @MRN
FROM
#GETID E
April 9, 2012 at 9:32 am
It is kind of hard to determine exactly what you are trying to do. You say Previous row but a table does not know anything about order. You need to find a column to order by. Then is becomes fairly simple to update like you are trying to do.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2012 at 9:38 am
Hi,
I inserted the data, as if it were already put in order... so the MRN is my ORDER BY...
With that, if you look at the Update Statement, I have a Variable with P, to represent previous rows value, @MRN is the current rows value...
So when I get to the Current Row, I can update the "SHOULD_BE" with the value of the Prior Varaible "P_MRN"...
Does that help???
April 9, 2012 at 9:56 am
Well sort of...but your order by column is not unique so which value of 'A' is first? This makes it rather challenging.
this is close...
IF OBJECT_ID('tempdb..#GETID') IS NOT NULL drop table #GETID
CREATE TABLE #GETID
(
MRN VARCHAR(6)
, IsCurr VARCHAR(31)
, SHOULD_BE VARCHAR(31)
)
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('A', NULL)
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('A', 'A')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('B', 'A')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('B', 'B')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('C', 'B')
select * from #GETID
update #GETID set IsCurr = x2.SHOULD_BE
from
(
select *, ROW_NUMBER() over(ORDER by MRN) as RowNum
from #GETID
) x
left join
(
select *, ROW_NUMBER() over(ORDER by MRN) as RowNum
from #GETID
) x2 on x2.RowNum = x.RowNum
join #GETID g on g.MRN = x.MRN
select * from #GETID
If you add a unique column that can give you an order by it is a lot easier. consider the following code, all I did was add an identity to your original table.
IF OBJECT_ID('tempdb..#GETID') IS NOT NULL drop table #GETID
CREATE TABLE #GETID
(
MyID int identity,
MRN VARCHAR(6)
, IsCurr VARCHAR(31)
, SHOULD_BE VARCHAR(31)
)
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('A', NULL)
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('A', 'A')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('B', 'A')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('B', 'B')
INSERT INTO #GETID (MRN, SHOULD_BE) VALUES ('C', 'B')
update #GETID set IsCurr = x2.SHOULD_BE
from
(
select *, ROW_NUMBER() over(ORDER by MyId) as RowNum
from #GETID
) x
left join
(
select *, ROW_NUMBER() over(ORDER by MyId) as RowNum
from #GETID
) x2 on x2.RowNum = x.RowNum
join #GETID g on g.MyID = x.MyID
select * from #GETID
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2012 at 10:05 am
Ahhh,
I have an ID, but I wasn't using it because it's not in sequence, so I didn't think to just add an Identity into my Temp Table...
Thanks for the help!!!
John
April 9, 2012 at 10:23 am
Hi agian,
I modified your code a bit to get the result I needed... I didn't have to add the Identity row...
I guess to try and explain, when the value A, changes values, like to B, then the IsCurr is flagged (in my world this means this is now the current status for Customer B)
With my original Update Statement, I was trying to figure out the placement of my Variables to get who is my current cust, and who was my prior customer... if they didn't match, I knew I had a new customer and thus would set the IsCurr... I can't get the order right of where to set those values... ugh...
But thanks again so much!!!
update #GETID2 set IsCurr = x2.MRN -- CHANGED THIS
--SELECT *
from
(
select *, ROW_NUMBER() over(ORDER by MRN) as RowNum
from #GETID2
) x
left join
(
select *, ROW_NUMBER() over(ORDER by MRN) as RowNum
from #GETID2
) x2 on x2.RowNum = x.RowNum + 1 -- ADDED A + 1 HERE
join #GETID2 g on g.MRN = x2.MRN -- CHANGED X.MRN TO X2.MRN
April 9, 2012 at 11:09 am
You're welcome. So did you get it resolved now or did you still need some help?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 9, 2012 at 3:22 pm
I do...
I really would like to use that update statement I posted at first...
that column I have "Should_Be", is a what I want my out put to look like...
my question is, the variables I have in my update statement, what order do I put them in to capture, current rows value, and then to set the prior rows value, so that at some point they would not be equal
I've tried every combination I can think of and nothing is working????????
Thanks
i.e.
E.IsCurr = @P_MRN
, @MRN = E.MRN
, @P_MRN = @MRN
OR
E.IsCurr = @P_MRN
, @P_MRN = @MRN
, @MRN = E.MRN
OR
@P_MRN = @MRN
, @MRN = E.MRN
, E.IsCurr = @P_MRN
OR
@MRN = E.MRN
, @P_MRN = @MRN
, E.IsCurr = @P_MRN
April 9, 2012 at 3:50 pm
Hi,
I found my answer re-reading Jeff Modem articles...
http://qa.sqlservercentral.com/articles/T-SQL/68467/
Here is the code I was looking for....
UPDATE
E
SET
@MRN = E.IsCurr =
CASE
WHEN
@MRN = @P_MRN
THEN
@MRN
ELSE
@P_MRN
END
, @P_MRN = e.MRN
FROM
#GETID E
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply