Week Numbers for Local Date Range (if that makes sense)

[JHear6]

In Crystal Reports XI - Is there a way to use this formula;
DatePart("ww", ({CustomerSurvey.SurveyCompleteDate}),crWednesday) or
DatePart("ww", ({CustomerSurvey.CreatedDateTime}),crWednesday)
only use the current date range of the report, which in this case is year to date?
I'm trying to chart the year to date success of our survery's.
I would like to show the number of surveys sent and completed as two different values by week number for the year to date on a chart with two lines.
Like;
week 40 - 41 sent and 9 completed
week 41 - 49 sent and 14 completed
week 42 - 45 sent and 10 completed
|
|
50----------------S
|-------------------------S
40------S
|
30-
|
20-
|-----------------C
10-------C---------------C
|______|_____|_____|_____|_____ (if this helps)
Week-40----41----42----43

Some Fields in the database;
CustomerSurvey.SurveyCompleteDate (which is also DateTime)
CustomerSurvey.CreatedDateTime
CustomerSurvey.SurveyStatus = "Completed" or "Open"
CustomerSurvey.SurveyID

Also open to better ways meet my goal here if I'm going down the wrong path.

Thank,
Jeff

 

[hilfy]

This is an interesting challenge - if I understand you correctly, your surveys are counted twice - one in the week when they were sent and then again in the week where they were completed. Is that correct? Or do you only want to count completions if they were completed in the same week as when they were sent? This approach to take will depend on the answer to this.

Also, what type of database are you connecting to?

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[JHear6]

Yes, we want to report on the same record twice, when it was created and when it was completed.
We want to chart them on a graph by week number.
There are different issues in the report when I use either the Created date or the Completed date week number, so I was thinking that maybe I would chart them on an independant week number easier. Or maybe there is a whole better way to approach what we are trying to show.
We are using SQL 2008 R2.

Thank for your help,
Jeff

 

[hilfy]

How good are your SQL Skills? I can think of a way to do this using a Command (SQL Select statement) that calculates the week in the query - it will be a union of a query that pulls the week for the create date and a query that pulls the week for the complete date. So, each survey record will appear twice - once for the create and once for the complete. You need to have the two records for every survey in order to get the format that you're looking for.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[JHear6]

I guess that means that you don't think I can do this in Crystal?
I was hoping to stay with Crystal so I could work in one tool and end up with the chart.
I'm OK with SQL. What was your thought?