Script: Getting rid of records depending on date

[#1]

Good afternoon,

I´ve got data like

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 15.06.2015

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 30.06.2015

The records cover several years. How can I get rid of the records that have a ValidToDate that is not the last day of the month? In the example above, I would keep only the second record.

Thanks!

[settu_periasamy]

Hi,

Try

if(validtodate=Date(MonthEnd(validtodate)),validtodate) as validtodate

[Anonymous]

you can either Group the data in script

load

PersonnelNr,

Name,

CostCenter,

LastDayofMonth

max(ValidToDate)

from

Group by

PersonnelNr,

Name,

CostCenter,

LastDayofMonth

or you just load the data with the where clause (works only if there is only 1 record per PersonnelNr for validtodate on the last day of month). if a Person can be more than onces in that list till end of month, you should do the group

TmpData:

load *

resident Data

where ValidToDate=monthend(ValidToDate)

drop table Data

ren table TmpData to Data

[#4]

Thanks but I want to actually eliminate the first record so as to avoid having to always use count distinct. Any suggestions?

[Anonymous]

using the following would eliminate the first record

TmpData:

load *

resident Data

where ValidToDate=date(monthend(ValidToDate))

drop table Data

rename table TmpData to Data

[settu_periasamy]

Then you can try the Rudolf Suggestion..

[rwunderlich]

A couple people have mentioned using:

where ValidToDate=monthend(ValidToDate)

which I think is on the right track. However MonthEnd returns a timestamp corresponding to 23:59:59 so I think the comparison should be:

where ValidToDate=floor(monthend(ValidToDate))

or perhaps:

where ValidToDate=date(floor(monthend(ValidToDate)))

-Rob

[#8]

Thanks Rudolf. I didn´t explain myself well, apologies. I could have records as per below and would want to keep records 2 and 3 but not 1:

Rec1

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 15.06.2015

Rec2

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 30.06.2015

ValidToDate: 30.06.2015

Rec3

PersonnelNr: 4711

Name: Kevin Costner

CostCenter: 9876

LastDayOfTheMonth: 31.05.2015

ValidToDate: 30.06.2015

[Anonymous · Accepted solution]

that's exactly what this script is doing (thks Rob for the correction!)

in your example the Rec 2 and 3 will be kept!

give it a try!@

TmpData:

load *

resident Data

where ValidToDate=floor(monthend(ValidToDate))

@robwunderöich

drop table Data

ren table TmpData to Data

[#10]

Thanks!