Selecting a number of weeks

[ddrake]

Hi there,
Kinda new to this...wonderful forum btw!

Currently using Crystal 9 with Remedy (5.1) and SQL 2000.

What I need to do is create reports showing only the last 6 full weeks of data (based on the Resolved Time (DateTime field)

Any help would be greatly appreciated!!!

Thanks!
Dave

 

[mbarron]

The following will give you the last 6 full weeks:
datediff("w",{your.date.field} ,currentdate)in 1 to 6

If you want to include this week:
datediff(&quot;w&quot;,{your.date.field} ,currentdate) < 6 Mike

 

[synapsevampire]

Create 2 formulas to be referecned in the record selection formula:

//startdate
dateadd(&quot;ww&quot;,-6,currentdate-dayofweek(currentdate))

//enddate
currentdate-(dayofweek(currentdate)-1)

In the record selection formula use:

{Resolved Time.DateTime field} >= @startdate
and
{Resolved Time.DateTime field} < @enddate

Replacing {Resolved Time.DateTime field} with your actual field name.

-k

 

[synapsevampire]

mbarron: That doesn't supply full weeks does it? Rather the last 6 weeks or partial of the most recent.

-k

 

[mbarron]

I do have an error. I should have the &quot;WW&quot; instead of just &quot;W&quot;

My formula should actually look like this (the &quot;,2&quot; in the second formula references the first day of the week- if you want your week to start on Monday):

datediff(&quot;ww&quot;,{@date} ,currentdate)in 1 to 6


datediff(&quot;ww&quot;,{@date} ,currentdate,2)in 1 to 6
Mike

 

[ddrake]

Perfect! Thank you!!

 

[synapsevampire]

I still don't see how this could determine the full weeks aspect, it will return the last 6 weeks, although it can now alter which day the week starts on, it still appears to return a partial week depending upon when it's run.

Perhaps ddrake didn't mean full weeks, in which case it works.

-k

 

[mbarron]

I just ran a report where I added 5 to my date so I could get a &quot;future date&quot;

This field:
datediff(&quot;ww&quot;,{@date} ,currentdate) shows 0 or the current week for dates ranging from 3/16 (Sunday) to 3/22 (Saturday) which is a full week. (unless we're talking about different kinds of full weeks).

If I add the &quot;,2&quot; the 0 week is 3/17 to 3/23 (Mon to Sun)

Using this formula and grouping on it.
datediff(&quot;ww&quot;,{@date} ,currentdate)

My results were:

weeks
back     Max date        Min Date 
0      03/22/2003       03/16/2003
1      03/15/2003       03/09/2003
2      03/08/2003       03/02/2003
3      03/01/2003       02/23/2003

Mike

 

[synapsevampire]

Sorry, I wasn't thinking it through... datediff of 1 IS last week...

-k