Generate a Calendar

[#1]

I need to create a calendar like the one below. The column "Date" is the date an employee punched a time clock. The "WeekEnding" date the week the time clock punch relates to - our week ends on Sunday. The "PayPeriodEnding" is date the person is paid for the time clock punch. We pay bi weekly on Fridays one week "late".

The first date in the calendar would be12/26/2012 and the last date would be the oldest date in a file of time (date) punches.

Thanks,
Stephen

tlkpCalendar

Day	Date	WeekEnding	PayPeriodEnding
Mon	12/26/2011	1/1/2012	1/13/2012
Tue	12/27/2011	1/1/2012	1/13/2012
Wed	12/28/2011	1/1/2012	1/13/2012
Thu	12/29/2011	1/1/2012	1/13/2012
Fri	12/30/2011	1/1/2012	1/13/2012
Sat	12/31/2011	1/1/2012	1/13/2012
Sun	1/1/2012	1/1/2012	1/13/2012
Mon	1/2/2012	1/8/2012	1/13/2012
Tue	1/3/2012	1/8/2012	1/13/2012
Wed	1/4/2012	1/8/2012	1/13/2012
Thu	1/5/2012	1/8/2012	1/13/2012
Fri	1/6/2012	1/8/2012	1/13/2012
Sat	1/7/2012	1/8/2012	1/13/2012
Sun	1/8/2012	1/8/2012	1/13/2012
Mon	1/9/2012	1/15/2012	1/27/2012
Tue	1/10/2012	1/15/2012	1/27/2012
Wed	1/11/2012	1/15/2012	1/27/2012
Thu	1/12/2012	1/15/2012	1/27/2012

[swuehl]

"The first date in the calendar would be12/26/2012 and the last date would be the oldest date in a file of time (date) punches."

Hm, I haven't really understood that requirement, I think your last date should be the youngest date in the file?

Otherwise you won't have much matches between your punch table and the calendar, right?

So I just tried to replicate the above table. I believe you should be able to easily adjust the number of dates (replace the fix number of records after the autogenerate with a difference of EndDate - StartDate+1. One of the dates could be retrieved from another table using a separate load with max(PunchDate) or min(PunchDate) or a punch table load ordered by PunchDate and Peek() function).

Here is what should give you above calendar:

Calendar:

LOAD *,

WeekEnd(Date) as WeekEnding,

WeekDay(Date) as Day,

date(floor(weekstart(Date)+18,14,MakeDate(2012,1,13))) as PayPeriodEnding;

LOAD

Date(makedate(2011,12,26)+recno()-1) as Date

autogenerate 100;

Hope this helps,

Stefan

[#3]

Hello Stefan,

WeekEnding is working but the PayPeriodEnding is now.

Stephen

[swuehl]

If I just copy the above snippet into a new document, I get values for PayPeriodEnding that matches your above table.

What do you mean with 'not working', do you get no values at all returned or values different from the above reference? If latter, what do you see?

Or maybe you have already modifed the script to adapt the range to your needs. If so, could you please post your script snippet or qvw?

[#5]

Stefan,

First, yes your code works perfectly. I must have pasted in something wrong.

Second, I modified the PayPeriodEnding to be:

date(floor(weekstart(Date)+25,14)-1) as PayPeriodEnding

this gives me the same results as your code without having to hard code the "MakeDate(2012,1,13)"

Stephen