count(if ) in script returns incorrect value

[#1]

Hi Guys,

I modified an existing web script to calculate NetworkDays, All parts of this script seem to to work as my below screenshot can show.

Date = autogenerate date range based on below "Date1" and "Date2"
Date1 = start date
Date2 = end date
IHD = isHoliday
IWD = IsWorkDay

After this I put below formula in script to count the real working days.

//NetoWork:
//Inner join (DatesRanges)
//load
// Date1,
// Date2,
// count(distinct if(Date>=Date1 and Date<=Date2 and IWD and IHD<>0,Date)) as NetoWork1
//resident NetoWork1
// where 1=1
// and isnull(Date1)=0
// and isnull(Date2)=0
//group by Date1,Date2;
//drop table NetoWork1;

The problem is that this count return the days as 7 and it does not deduct the holidays, because then it result would be 5.
Can anybody please let me know where the problem my be, it's slowly driving me crazy.

The complete modified NetoWorkday script is attached, if you need more details please ask....

Thanks,

Michiel

[prieper]

Would be good to have an example to check on the data.
The DISTINCT-parameter does not make much sense with the above data, only if you have duplicates for Date.
Would also implicitely include the condition for the IWD.
When working with 64bit-systems the ISNULL-function should be replaced with LEN(TRIM(...))=0 (not relevant for this problem).

count(if(Date>=Date1 and Date<=Date2 and IWD=1 and IHD<>0,Date)) as NetoWork1

HTH
Peter

[#3]

Hi Peter,

All comments are very userfull, since I run the script from 64bit server.

Trying all suggestions now and post test results here when done.

Thank You,

Michiel

[#4]

It seems I was totaly off, the screenshot of the table I posted in my first post does not represent all records in this table. Therefore a count on it's contents would almost always return more TRUE cases than excepted based on the information I provided.

The real information in the table is for example:

my count statement in script is:

count

(distinct if(Date>=Date1 and Date<=Date2 and IWD=1 and RMACountry = CountryCode,Date)) as NetoWork1;

In case of AT country, where working day is false (0), it will also find the NLor other country record(s) and count it as true.

My question is how to separate on country level, I will also attach the whole project now.

Thanks

Michiel

[#5 · Accepted solution]

I managed to solve this, and decided to share my solution, I attached the Project files to this topic.

What is does it count days from start till end while it deducts weekend and holidays based on country.

Kr,

Michiel