select qry showing changed row and prior to chg ro

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 2008 Forums

Transact-SQL (2008)

select qry showing changed row and prior to chg ro

Author

Topic

cplusplus
Aged Yak Warrior

567 Posts

Posted - 2013-08-24 : 12:05:49

Is it possible with in a query or do i need an sp with a cursor?

i want to show last 7 days changed rows along with how that record was prior to change.

I have a table(LBI_LW_POSITIONS) that is keeping last week total table copy of rows(total 9k rows)

And another table(LBI_LW_POS_CHG) that has just the last 7 days changed rows only "updated rows" and "newly created rows". approximately 20 rows.

the unique combination to identify a row is based on 3 columns.

company, position & effect_date.

Is it possible to show as pairs lastweek row prior to change and this weeks changed row.

select COMPANY,
[POSITION],
[DESCRIPTION],
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
DATE_STAMP,
AS_OF_DATE [LBI_LW_POSITIONS]

The below table has just the changed rows for last 7 days , updated rows and newly created rows. only there wont be more than 20 rows.

select COMPANY,
POSITION,
DESCRIPTION,
POSIT_STATUS,
EFFECT_DATE,
END_DATE,
JOB_CODE,
DEPARTMENT,
DEPT_NAME,
AS_OF_DATE,
DATE_STAMP,
from
LBI_LW_POS_CHG where as_of_date = Max(as_of_date);

Thank you very much for the helpful info.

lazerath
Constraint Violating Yak Guru

343 Posts

Posted - 2013-08-26 : 12:40:14

It isn't clear to me what you want. Can you provide sample data for each table and then show us what the result should look like?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-28 : 02:50:05

sounds like this to me


SELECT *
FROM
(
select p.COMPANY,
p.POSITION,
p.DESCRIPTION AS PREVDESC,
p.POSIT_STATUS AS PREVPOSTSTATUS,
p.EFFECT_DATE,
p.END_DATE AS PREVENDDATE,
p.JOB_CODE AS PREVJOBCODE,
p.DEPARTMENT AS PREVDEPT,
p.DEPT_NAME AS PREVDEPTNAME,
p.AS_OF_DATE AS PREVEFFDATE,
p.DATE_STAMP AS PREVDATESTAMP,
pc.DESCRIPTION AS CURRDESC,
pc.POSIT_STATUS AS CURRPOSITSTATUS,
pc.END_DATE AS CURRENDDATE,
pc.JOB_CODE AS CURRJOBCODE,
pc.DEPARTMENT AS CURRDEPT,
pc.DEPT_NAME AS CURRDEPTNAME,
pc.AS_OF_DATE AS CURREFFDATE,
pc.DATE_STAMP AS CURRDATESTAMP,
ROW_NUMBER() OVER (PARTITION BY p.COMPANY,p.POSITION,p.EFFECT_DATE ORDER BY p.AS_OF_DATE DESC) AS Seq
from LBI_LW_POS_CHG pc
INNER JOIN [LBI_LW_POSITIONS] p
ON p.COMPANY = pc.COMPANY
AND p.POSITION = pc.POSITION
AND p.EFFECT_DATE = pc.EFFECT_DATE
)t
WHERE Seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources