Do a count by Day 2

[Sahubba]

Hello everyone,

I am having issues trying to figure out how to do a count of days the Provider seen the customer.

Example:

Start Date End Date Count By Days
1/26/2011 10:45 am 1/31/2011 7:00 pm = 6
2/3/2011 10:16 am 2/9/2011 12:01 am = 7
2/17/2011 8:21 am 2/17/2011 5:47 pm = 1

Please Help,
Thanks

 

[IanWaterman]

just use datediff()

@CountbyDays

datediff("d", startdate, enddate)+1

Ian

 

[Sahubba]

Hello IanWaterman,

I had to laugh at myself.....I should have known that....My excuses is...It is early!


Thank you so much it worked Great!

 

[Sahubba]

While testing the data I ran into a problem.

is there a way I can link @CountbyDays
to the Parameter?
Example

if the Parameter are set for 2/1/2011 to 2/28/2011
and a provider seen a customer starting at 1/20/2011 to 2/17/2011,
how I have it set up now it will exclude this customer.
I need to count those days that fits in the criteria, so the count should be 17 days instead of excluding the customer.

I hope this makes sense.
Thanks

 

[IanWaterman]

@CountbyDays

If startdate in [Paramstart to paramend] and
enddate in [Paramstart to paramend] then
datediff("d", startdate, enddate)+1
else 0

Ian

 

[Sahubba]

it didnt work it gave me the same data as before.

 

[jcrawford08]

Set the paramater as a date range, and then reference it in the formula and it should work.

if startdate in [parameter] then
datediff("d",startdate,enddate)+1 else
0


JC

Not in word only, but in deed also... 1Jn3:18

 

[lbass]

Try something like this:

datevar start;
datevar end;
if {table.start} < {?Start} then
start := {?Start} else
start := {table.start};
if
(
isnull({table.end}) or
{table.end} >= {?End}
) then
end := {?End} else
end := {table.end};
datediff("d",start,end)

-LB

 

[Sahubba]

Thanks LB it worked!