Writing script to pick up transactions within 14 days of last transaction

[#1]

Trying to write script to capture the following:

see call claims that occurred within 14 days or less of last claim for a particular unit.

Thank you,

Venus

[erichshiino]

Hi, Venus

Could you provide us with some data samples, table or a small application with you data model.

I'd like to understand it a little better before answering your question.

Rgds,

Erich

[#3]

Hi Erich,

Here's the sample.

I want to set up a script to monitor this unit and see if it has a new transaction within 14 days of the last one on 4/6/11.

Transaction Date	Unit#	Problem
20100810	100364927	LIGHTS INOP. FOUND LOOSE CONNECTION.CONNECT. JD
20101013	100364927	PUMP LEAKING RR/MP
20101014	100364927
20101014	100364927
20101014	100364927	TRIPS BREAKER OZONE SHORTED RR/MP
20101022	100364927	SPEAKER IN OP RR PART LES
20110217	100364927	FAULTY STEREO RR
20110406	100364927	TOPSIDE:Moisture inb TOPSIDE

Thanks for the help.

Venus

[erichshiino]

Hi, Venus.

This is an app a build based on your table. I created another unit to test how it would work.

I solved the problem with script and some set analysis.

I hope it will work now for you. Tell me if there is any doubt.

[#5]

Erich,

It's close. Sorry, I was not specific enough.

Script shoud compare the last two different transaction dates for a specific unit and provide a list with all transactions with 14 days difference between the two dates.

ID	Problem	Transaction Date	Unit#	ntDate	daysFromLast	maxnDate
7	FAULTY STEREO RR	20110217	100364927	17/02/2011	48	06/04/2011
8	TOPSIDE:Moisture inb TOPSIDE	20110406	100364927	06/04/2011	0	06/04/2011
		2/17/2011	4/6/2011
		last transaction	current transaction

Thanks again,

Venus

[erichshiino]

Hi, Venus.

I hope I solved it now

[#7]

Erich,

Thank you. Just one more question. Can this be done without the use of sequence numbers? From this set, the line in red is what should be captured.

Tag#	Service Date	days between failures	Unit#	Customer Number
455027	9/25/2009	0	ABC1234	123
559719	11/24/2010	425	ABC1234	123
559767	11/30/2010	6	ABC1234	123
585925	2/11/2011	73	ABC1234	123

Thank you,

Venus

[erichshiino]

Venus,

the sequency field I build on the script. I doesn't exist on the database using order by and peek()

You could use a similar expression and adapt it for a background condition.

Tell me if you have any doubts on the script