SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 select qry showing changed row and prior to chg ro
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cplusplus
Aged Yak Warrior

542 Posts

Posted - 08/24/2013 :  12:05:49  Show Profile  Reply with Quote
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.

Edited by - cplusplus on 08/24/2013 15:28:18

lazerath
Constraint Violating Yak Guru

USA
343 Posts

Posted - 08/26/2013 :  12:40:14  Show Profile  Reply with Quote
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?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 08/28/2013 :  02:50:05  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000