April 19, 2011 at 4:47 am
Gurus,
Am looking for a query to do the following
My table has following data
ID Amount Date Flag
22 12300 12-3-2009
23 2123 16-4-2009
12 1232 20-4-2009
43 23434 22-6-2009
44 23423 23-7-2009
In the above, I need to update the flag to 'Y' if subsequent records are from subsequent month. i.e
For record 23, since difference of month is 1, the flag should be 'Y'
Whereas for 12, it is 'N' as difference of previous date is not 1.
I need to update the flag if dates of consecutive records have month difference asa 1
April 19, 2011 at 6:10 am
maybe start with something like this
declare @table table(ID int,Amount int,myDate smalldatetime,flag char(1))
insert into @table
select 22,12300,'2009-03-12',''
union
select 23,2123,'2009-04-16',''
union
select 12,1232,'2009-04-20',''
union
select 43,23434,'2009-06-22',''
union
select 44,23423,'2009-07-23',''
;
with my_Cte(rownum,id,amount,mydate,flag)
as(
select
ROW_NUMBER() over (order by mydate),
* from @table d
)
select
c1.rownum as rownum_c1,
c1.id as id1,
c1.amount as amount1,
c1.mydate as mydate1,
c1.flag as flag1,
c2.rownum as rownum_c2,
c2.id as id2,
c2.amount as amount2,
c2.mydate as mydate2,
c2.flag as flag2
from my_Cte c1
inner join my_Cte c2 on c1.rownum +1 = c2.rownum
then you can do a datediff between mydate1 and mydate2 and update where necessary?
April 19, 2011 at 6:41 am
Assuming davidandrews13 has guessed correctly about how you determine the "subsequent" row, then his idea is sound. You can run the update statement straight from the CTE - e.g.
;WITH my_cte(rownum, uniqueID, amount, mydate, flag) AS (
SELECT Row_number() OVER (ORDER BY mydate), *
FROM @table)
UPDATE @table
SET flag = CASE WHEN DATEDIFF(MONTH,c1.mydate,c2.mydate) = 1
THEN 'Y'
ELSE 'N' END
FROM my_cte c1
INNER JOIN my_cte c2 ON c1.rownum + 1 = c2.rownum
WHERE c2.uniqueID = id
Change the WHERE clause to c1.uniqueID = id, if I have it the wrong way round.
April 19, 2011 at 6:43 am
For an update like this, the Running Totals algorithm described here[/url] by Jeff Moden can't be beaten. If you were looking to output the results in the manner you describe without updating a table, then a recursive CTE would be a good second choice.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
April 19, 2011 at 8:13 am
Thanks gurus... I could solve all the issuess..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply