Weeks and Periods

[f0rg3tfu1]

Hello all,

Our accounting department seperates our weeks by Periods. The first period of the year begins (for example) Jan 4. The periods go Monday-Sunday.

Period 1 = Jan 4 - Jan 11
Period 2 = Jan 12 - Jan 18

I have grouped some Timesheet data by period... the only problem is that the corresponding dates are not in the tables I am using.

Does anyone know the syntax for the formula I should use to return the Ending date of each period??

Example Output:
Period Ending Jan 11, 2004 BLAHBLAHBLAHBLAH
Period Ending Jan 18, 2004 BLAHBLAHBLAHBLAH

Crystal 10 with a SQL server backend.

Thanks for your help... I appreciate it!!!

 

[synapsevampire]

You might use a formula to check if the date is a Sunday and if not add the appropriate number of days, as in:

if dayofweek({table.date}) <> 7 then
dateadd("d",7-dayofweek({table.date},{table.date})
else
{table.date}

The data warehouse technique is to use a periods table, then you can left outer it to your data.

I have a FAQ here on creating a Period Table using SQL Server complete with the SQL:

faq767-4532

I'd try to help more but you've posted nothing about how these periods are being created, so I assumed that you want to display the ending date for some period formula when the data returned doesn't have the ending date in it.

And what if you don't have any data for a given period, what do you want to do then?

-k

 

[f0rg3tfu1]

I think it is quite a bit simpler than what I may have relayed originally.

Period one... Ending date is Jan 11
Period two... Ending date is Jan 18
Period three.. Ending date is Jan 25
and so on.

Instead of showing "Period 1" on the report, I want to show the ending date. Only problem is, the corresponding dates that correlate with the period is contained in another table that I do not have access to at the moment.

What I was thinking was hardcoding in a formula:

Period one = Jan 4
Period two = Jan 11

Only problem is that I didnt feel like typing this in 52 times.

I hope this makes sense... this is a one time report for this year so I wont have to worry about year changes.

Thanks for the tips!