Find data that has not changed for 5 days

Question

Find data that has not changed for 5 days

JMI

SSChampion

Points: 11831
More actions
September 2, 2009 at 9:47 am

#95771

I have a requirement from a User
to build a report that shows data that did not change for 5 days.
In other words they want to capture "stale data".
Please see the attachment to view an extract from table
with sample "stale" data.
I can eyeball and find these sets but how to write a query that finds these pieces.
So far not really sure how to approach it.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

well finding something that is older than 5 days is easy...but you have to know which column to compare;

it took me much longer to build the CREATE TABLE and INSERT INTO statements, than it did to build the solution...in the future, please try to give us the data in that format.

based on the data, i'm guessing you want something like this:

Create Table #Example(

[COB] datetime,

[TimeStamp] datetime,

[Index] VARCHAR(30) ,

[CUSIP] VARCHAR(30),

[Spread] Decimal(19,8))

INSERT INTO #Example

SELECT '9/1/09 12:00 AM','9/2/09 10:29 AM','A_BF_A','EH169791',31.57549892 UNION ALL

SELECT '8/31/09 12:00 AM','9/2/09 9:55 AM','A_BF_A','EH169791',31.57549891 UNION ALL

SELECT '8/28/09 12:00 AM','9/2/09 9:55 AM','A_BF_A','EH169791',31.57549894 UNION ALL

SELECT '8/27/09 12:00 AM','9/2/09 9:55 AM','A_BF_A','EH169791',31.57549896 UNION ALL

SELECT '8/26/09 12:00 AM','9/1/09 10:54 PM','A_BF_A','EH169791',31.57549898

SELECT DateDiff(day,[COB],[TimeStamp]) As DaysOfDiff,* FROM #Example

select * from #Example Where DateDiff(day,[COB],[TimeStamp]) >=5

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JMI SSChampion Points: 11831 More actions · Answer 2

Sorry Lowell.

You are absolutely right I should have posted some SQL code

to re-produce my task.

Lowell,

I probably need to explain a bit what COB and TimeStamp columns are.

[COB] is Close-Of-Business date. Usually it's a working day and can't be holiday.

[TimeStamp] is actually a date when a record was last updated.

So what the user wants is to see for which [Index,CUSIP]

Spread value was staying the same for 5 consecutive [COB] days.

User also considers [Spread] value the same if

whole number and six decimals are identical.

You can use my code below to see what I mean.

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#s'))

DROP TABLE #s;

if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#r'))

DROP TABLE #r;

create table #s (Spread float,COB datetime,Instrument varchar(15),CUSIP varchar(15))

create table #r (Spread float,COB datetime,Instrument varchar(15),CUSIP varchar(15))

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589745,'9/1/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589733,'8/31/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589749,'8/28/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589713,'8/27/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589722,'8/26/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589727,'8/25/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589751,'8/24/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589754,'8/21/2009','A_BF_A','EH169791'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589751,'8/31/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589712,'8/28/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589715,'8/27/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589718,'8/26/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589726,'8/25/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589737,'8/24/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589752,'8/21/2009','C_GEN_BBB','13638ZAC'

insert #s (Spread ,COB,Instrument,CUSIP) select 125.26589759,'8/20/2009','C_GEN_BBB','13638ZAC'

insert #r (Spread ,COB,Instrument,CUSIP) select round(Spread,6),COB,Instrument,CUSIP from #s

--select * from #s

select * from #r

select MIN(COB),Instrument,CUSIP, Spread , count(Spread)

from #r

group by Instrument,CUSIP,Spread

having count(Spread ) >=5

I was able to capture "stale" Spread value by using

group by Instrument,CUSIP,Spread

having count(v) >=5

but it's not accurate.

What if count(Spread ) >=5 includes COB dates that are not consecutive?

5/15/2009

5/16/2009

8/27/2009

8/28/2009

8/31/2009

This is my task now.

Lowell SSC Guru Points: 323518 More actions · Answer 3

maybe with row number, if we can assume there is a data entry for every date.; from the data, i'm inferring that we are getting 5 or more business days, not 5 or more calendar days at this time...is that what you wanted?

if there is not a date, then we would have to join against a Calendar or Tally table to generate the between dates.

--try this:

select row_number() over(partition by v,Instrument,CUSIP order by v,Instrument,CUSIP,COB ) As RW,

#r.*

from #r

--and finally filtering that to get just 5 or more

select * from (

select row_number() over(partition by v,Instrument,CUSIP order by v,Instrument,CUSIP,COB ) As RW,

#r.*

from #r ) MyAlias

Where RW >=5

/*

--results

RW v COB Instrument CUSIP

5 125.265897 2009-08-31 00:00:00.000 A_BF_A EH169791

6 125.265897 2009-09-01 00:00:00.000 A_BF_A EH169791

5 125.265897 2009-08-28 00:00:00.000 C_GEN_BBB 13638ZAC

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

JMI SSChampion Points: 11831 More actions · Answer 4

Lowell,

Thank you so much for trying to help me.

The code is running on SQL Server 2000 machine.

row_number() will not work.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

riga1966 (9/2/2009)
I have a requirement from a User
to build a report that shows data that did not change for 5 days.
In other words they want to capture "stale data".
Please see the attachment to view an extract from table
with sample "stale" data.
I can eyeball and find these sets but how to write a query that finds these pieces.
So far not really sure how to approach it.

Riga... with 510 points, I'm sure you'rve seen it before and if you haven't, it's time you did. Please post data in a readily consumable format. To see how and why, please read the following article...

http://www.sqlservercentral.com/articles/Best+Practices/61537/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

JMI SSChampion Points: 11831 More actions · Answer 6

JMI

SSChampion

Points: 11831

September 2, 2009 at 1:51 pm

#1048419

.

JMI SSChampion Points: 11831 More actions · Answer 7

By the way IFCode shortcuts didn't work for me

with previous version of FireFox.

But now I will definitely start using it.

It works.

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

riga1966

I took the liberty of adding a few additional entries to your sample data, and assumed that you meant 5 consecutive business days. I did not develope code for holidays being non business days.

INSERT INTO #Example

SELECT '8/26/09 12:00 AM','9/1/09 12:00 PM','A_BF_A','SS169791',31.57549898 UNION ALL

SELECT '8/26/09 12:00 AM','9/2/09 12:00 AM','A_BF_A','TT169791',31.57549898 UNION ALL

SELECT '8/26/09 12:00 AM','9/2/09 12:00 PM','A_BF_A','UU169791',31.57549898 UNION ALL

SELECT '8/26/09 12:00 AM','9/2/09 10:54 PM','A_BF_A','VV169791',31.57549898 UNION ALL

SELECT '8/26/09 12:00 AM','9/3/09 10:54 PM','A_BF_A','WW169791',31.57549898 UNION ALL

SELECT '8/26/09 12:00 AM','9/3/09 10:54 PM','A_BF_A','XX169791',31.57549898

The T-SQL for selecting those entries which meet the criteria of not having been updated for 5 business days is:

SELECT DateDiff(day,[COB],[TimeStamp]) As DaysOfDiff, DATENAME(dw, [COB]) AS 'COB Day' -- only for illustrative purpose

,* FROM #Example

WHERE (DateDiff(day,[COB],[TimeStamp])>=5 AND DATENAME(dw, [COB]) = 'Monday')

OR (DateDiff(day,[COB],[TimeStamp])>=7 AND DATENAME(dw, [COB]) IN ('Tuesday','Wednesday','Thursday','Friday'))

Sorry but I just ran out of enough time to create a table of holidays so as to include those as non business days.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

JMI SSChampion Points: 11831 More actions · Answer 9

Guys,

I don't understand why you focused so much on this criteria:

... WHERE DateDiff(day,[COB],[TimeStamp])>=5

It's really not the right criteria for [Spread] value staying the same for more than 5 consecutive business days.

[TimeStamp] is useless.

---------

bitbucket,

---------

[COB] date is always a business day.

So we don't need to find it out.

I changed [Spread] value slightly in your example:

drop table #Example

Create Table #Example(

[COB] datetime,

[TimeStamp] datetime,

[Index] VARCHAR(30) ,

[CUSIP] VARCHAR(30),

[Spread] Decimal(19,8))

INSERT INTO #Example

SELECT '8/20/09 12:00 AM','9/1/09 12:00 PM','A_BF_A','SS169791',31.57 UNION ALL

SELECT '8/21/09 12:00 AM','9/2/09 12:00 AM','A_BF_A','TT169791',31.13 UNION ALL

SELECT '8/24/09 12:00 AM','9/2/09 12:00 PM','A_BF_A','UU169791',31.24 UNION ALL

SELECT '8/25/09 12:00 AM','9/2/09 10:54 PM','A_BF_A','VV169791',32.61 UNION ALL

SELECT '8/26/09 12:00 AM','9/3/09 10:54 PM','A_BF_A','WW169791',33.84 UNION ALL

SELECT '8/28/09 12:00 AM','9/3/09 10:54 PM','A_BF_A','XX169791',34.95

SELECT DateDiff(day,[COB],[TimeStamp]) As DaysOfDiff, DATENAME(dw, [COB]) AS 'COB Day' -- only for illustrative purpose

,* FROM #Example

WHERE (DateDiff(day,[COB],[TimeStamp])>=5 AND DATENAME(dw, [COB]) = 'Monday')

OR (DateDiff(day,[COB],[TimeStamp])>=7 AND DATENAME(dw, [COB]) IN ('Tuesday','Wednesday','Thursday','Friday'))

Do you see what happens?

Your code returns 5 records with different [Spread] values where difference between [COB] and [TimeStamp] > 5.

It has nothing to do with what I need to get.

What I need is to track [Spread] value and when it stays the same for 5 days,

capture it.

Difference between {COB] and [TimeStamp] is irrelevant.