checking of previous record - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

checking of previous record

Author

Topic

ivra
Starting Member

18 Posts

Posted - 2009-03-09 : 05:19:00

Hi,

Can you please help me how can i populate column Credit by following this logic:

Credit should equal to Source if:

Year=2009 and if Source=booker then it should check if Person_ID of current record is the same as the previous record. If it is the same, it should pick up Source value of the preceeding record.

Person_ID Source Applied_Date Year Credit
0051765145G brochure 10/5/08 2008
0051765145G booker 7/5/09 2009 brochure
0051765145G booker 7/5/09 2009 booker

Example table above shows the Credit Column of:
First row is blank because the Year is not 2009.
Second row is "brochure" because year=2009,Source="booker" and its person_ID is equal to person_ID of previous record.

Thanks in advance

matty
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 06:55:29

declare @sample TABLE
(
Person_ID varchar(50),
Source varchar(50),
Applied_Date datetime,
Year int
)
insert @sample
select '0051765145G', 'brochure','10/5/08', 2008
union all
select '0051765145G', 'booker', '7/5/09', 2009
union all
select '0051765145G', 'booker', '7/5/09', 2009

SELECT t.Person_ID,t.Source,t.Applied_Date,t.Year,
CASE WHEN t.Year= 2009 AND t.Source = 'booker' AND t.Person_ID = t1.Person_ID THEN t1.Source ELSE '' END AS Credit
FROM
(
SELECT * ,row_number() over (order by Applied_Date) AS Rownum from @sample
)t

LEFT JOIN
(
SELECT * ,row_number() over (order by Applied_Date) AS Rownum from @sample
)t1
ON t.rownum = t1.rownum + 1

ivra
Starting Member

18 Posts

Posted - 2009-03-09 : 07:56:07

thanks Matty, it works!

matty
Posting Yak Master

161 Posts

Posted - 2009-03-09 : 08:01:52

You're welcome.

ivra
Starting Member

18 Posts

Posted - 2009-03-09 : 10:28:05

hi Matty,

It is possible to copy to result set of your queries to another table?How?

Thanks

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-09 : 10:31:14

Add an INSERT statement before the SELECT...like below

INSERT INTO urtable (Person_id,Source,Applied_date,Year)
SELECT t.Person_ID,t.Source,t.Applied_Date,t.Year,
CASE WHEN t.Year= 2009 AND t.Source = 'booker' AND t.Person_ID = t1.Person_ID THEN t1.Source ELSE '' END AS Credit
FROM
(
SELECT * ,row_number() over (order by Applied_Date) AS Rownum from @sample
)t

LEFT JOIN
(
SELECT * ,row_number() over (order by Applied_Date) AS Rownum from @sample
)t1
ON t.rownum = t1.rownum + 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources