Looping ? 1

[MFritts253]

I have a report where i need to show total Number of Contracts for a particular week.

Lets say week ending 6/10/2005
and for that week i can get the total Contracts i've all computed the total number of contracts opened that week which are still open at the end of that week. But what i need some help with is how do i get all of the contracts
still open 1 week to N weeks from that week ending but just for that week of 6/10

What i know, is that i need to use my dateadd function to compute the weeks from my Week Ending date, i know all my computations, but what i could use help with is i do not know how to Print out in Crystal which would look like this
Weeks from
6/10 Still Open
1 Weeks 1000
2 Weeks 950
3 Weeks 875
and so on ...
I am using Parameter to get the number of weeks the user wants to process.
I'm figuring it has something to do with whileprintingrecords but i am just not sure how to get it to loop for weeks and what section of the report to place it.

Sincerely,
Michael

 

[lbass]

I guess you are talking about weeks following the week that contracts are opened? Are there separate date fields for "closed" and "opened"?

-LB

 

[MFritts253]

lbass,
Yes i'm talking about weeks following the week that contracts are opened. and yes there are separate date fields for closed and opened.

Michael

 

[lbass]

To get this display, I think you would have to create multiple running totals or multiple conditional formulas to be summarized in the report footer. Your record selection formula would look like:

{table.opendate} in {?wkenddate}-6 to {?wkenddate}

Conditional formulas (faster than running totals) would look like:

//{@1 week}:
if isnull({table.closeddate}) or
{table.closeddate} > dateadd("ww",1,{?wkenddate}) then 1

//{@2 weeks}:
if isnull({table.closeddate}) or
{table.closeddate} > dateadd("ww",2,{?wkenddate}) then 1

Place these in the detail section and insert grand totals on each. Then suppress the detail section. You can position the summaries in the report footer in a vertical column with text boxes next to them. Then you would need to use suppression formulas on each summary and text box like:

dateadd("ww",2,{?wkenddate}) > dateadd("ww",{?numberofweeks},{?wkenddate})

This is quite a bit of work if you want to allow many weeks. Maybe there's a simpler solution I haven't thought of.

-LB

 

[MFritts253]

lbass,
Thanks for the help. What i have done to get weeks following week of i used a bit SQL in a stored procedure to
grab all of my contracts for whatever week and the following with a DateAdd function here is my SQL, it returns whatever week number from my original date.

Like this:
Select

( Select COUNT(a4.agrid)
from agreemnt2 a4
where a4.odate >='2005-05-08' and a4.odate <= '2005-05-14'
) AS TOTAL_AGREEMNTS,

COUNT(agreemnt2.agrid) AS TOTAL_STILLOPEN,

COUNT(CASE clo_class when 'EPO' then 'EPO' when 'PIF' then 'PIF' END)AS TOTAL_PAYOUT
FROM
agreemnt2
WHERE
agreemnt2.agrid IN
(
Select
a2.agrid
FROM
agreemnt2 a2
WHERE
a2.odate >='2005-03-27' and a2.odate <= '2005-04-02'
)
and
DATEADD(wk,2,agreemnt2.odate) < agreemnt2.clo_date

and this should return this row
TOTAL STILLOPEN PAYOUT
474 181 1

But what i am looking for is too either get my SQL to loop so that in the DATEADD at the end it goes from week 0 to N or do this in Crystal.

Cheers,
Michael

 

[synapsevampire]

It sounds like you want to group by the week number, you might use the datepart function if your SQL supports it.

btw, SQL shouldn't loop through data, that's generally a sign of bad SQL, a sign of a legacy procedural based coder.

-k