counting days not used 2

[cmpgeek]

I know there is a simple way to do this that i must have forgotten somewhere along the line; but here I am...

what I am trying to do is count of the number of Thursdays that were blocked out for a Dr to work - even though they may not have worked on all of them.

My mind keeps saying to do an outer join; but the problem with that is that the DayOoWeek field I need to count is in the same table as the case information...

I tried bringing in a sec case information table w/ an alias, but it is still going to be driven by a casenumber - which I wont have if the Dr did not work on any particular Thursday...

I am wondering if maybe I need a main report that looks at nothing more than the date range and feeds that to the subreport. That way the dates would show up regardless, and i would be able to get a total of them to use later. (I know there is a post here somewhere that will show me how to use fields from subreport in formulas for the main report if this is the case).

am I thinking in circles and ultimately making this too hard?

if someone can help, I would be very grateful!

thanks

 

[Reebo99]

Create a formula called something like @Thurday:

if {dayofweek}="thursday" 1 else 0

you can then sum this field.
Reebo
Scotland (Going mad in the mist!)

 

[cmpgeek]

that does not work either as the report is only going to pull the Thursdays that Dr "Smith" did cases. If he is sick and cancels his caseload for 1 thursday - there will be no cases listed on that day; so the report will not pull it.

i am not sure there is an answer to this one... i have been racking my brain for hours.... i may just have to count up the total for each DOW during the date range and make up a formula for each... (I was trying to avoid that)...

thanks for your help!

 

[Reebo99]

There is another solution. Is there no way you can stop the Dr from deleting the item from the data source, and force him/her to mark it as "cancelled"? This would mean the data is still there to report on. Reebo
Scotland (Going mad in the mist!)

 

[cmpgeek]

that example might have caused some confusion; they may cancel a whole days worth of cases, or they might just have nothing scheduled on that day... as far as the report sees it; the day does not have any information that fits the criteria...

the cancelled cases do stay in the system; but we do not want to see those for this report...

 

[Reebo99]

Ahh...So, in summary, you want the report to show how many Thursdays were in a date range, even if there are no data items for certain Thursdays?

Example:
01/01/2003 > 31/01/2003 = 5 Thurdays
if no data appears for Thurs 2nd Jan 2003, still show 5 thursdays

Reebo
Scotland (Going mad in the mist!)

 

[cmpgeek]

YES! please tell me it is possible...

 

[synapsevampire]

Take a wee bit to code, but here's the steps:

-Determine the first Thursady in your date range (use day() function, I'll leave this to you as I'm in a hurry)

-Create 3 formulas to count it up and loop to iterate through the date range:

Report Header:
whileprintingrecords;
global numbervar MyCount:= 0;

whileprintingrecords;
datevar startdate := currentdate - 1000;
global numbervar MyCount;
while startdate < currentdate - 1 do(
MyCount := MyCount+1;
startdate := startdate+7
)

Report footer
whileprintingrecords;
global numbervar MyCount:= 0;
If MyCount > 0 then
MyCount +1
else
MyCount

-k

 

[Reebo99]

I can give you something which will work, but it might be slow and I can't guarantee it's the best way of doing things, but it sounds like your going slightly madder than I am.

create a formula (in basic syntax), something like :

dim Counter as Number
dim TempDate as Date

Tempdate = {FromDateRange)

Do until TempDate = {ToDateRange}
If dayofweek(Tempdate) = 5 then counter = counter+1
TempDate = Dateadd(&quot;d&quot;,1,TempDate)
loop

Formula = Counter

This should give you the number of Thursdays within a date range. Hope it works (fingers crossed, I haven't tested it).
Reebo
Scotland (Going mad in the mist!)

 

[Reebo99]

WAIT!

make sure that the fields you using are all Date fields. You can have problems when comparing a Date field to a DateTime field.

To convert to date, it's as simple as Date({datetimefield}).

Sorry about not mentioning that, Also make sure you save your report before making this change as it has been known that you get into a continuous loop which may require a ctrl-alt-del to stop. Reebo
Scotland (Going mad in the mist!)

 

[cmpgeek]

well synapsevampire's seems to still only be counting the days on which cases were done; but i may have put something in the wrong place - i dont think i did; but i am going to do it again...

Reebo... i switchedd to basic syntax and entered what you sent; but in the line:

If dayofweek(Tempdate) = 5 then counter = counter+1

it keeps telling me i need to put LOOP after
COUNTER

and then does not recoginize the rest of the line
= COUNTER +1

i will continue to try both ways - hopefully a light bulb will come on over my head soon...

thanks - yall are a huge help

 

[Reebo99]

It shouldn't matter, but then again programming is a git at times, change the formula to:

dim Counter as Number
dim TempDate as Date

Tempdate = {FromDateRange)

Do until TempDate = {ToDateRange}
If dayofweek(Tempdate) = 5 then
counter = counter+1
End if
TempDate = Dateadd(&quot;d&quot;,1,TempDate)
loop

Formula = Counter

Enjoy!
Reebo
Scotland (Going mad in the mist!)

 

[chelseatech]

from the CR help (and sorry it's a function that is only available in CR8/8.5 and 9)

DateDiff (&quot;ww&quot;, #10/5/1999#, #10/29/1999#, crWednesday)

Counts the number of wednesdays between two dates. Use your fields and crThursday for your example. Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Reebo99]

Chelseatech, you star! great solution, one line formula, no looping, quick and simple. A star well earnt! Reebo
Scotland (Going mad in the mist!)

 

[synapsevampire]

Very nice, *Chelsea, one to remember!