Exclude Holidays and Weekends 1

[dadyswat]

I'm currently working on a report in V10 that needs to exclude the holidays and weekends. I've created a new table in SQL Server 2000 and holidays =1 all other days =0.

I used a formula this formula in testing and as long as there is only one holiday in the reporting period it seems to work, however, it creates duplicates in the report. If the holidays prtion is removed it works fine except for the holidays. I tried using a global variable with a counter but then the formula asks for a boolean datatype. At this point I'm totally lost as to why I get the duplicates. Running the SQL query in Query Analyser the data seems fine.

Local DateTimeVar d1 := {AX_0001_A.StartDate};
Local DateTimeVar d2 := {AX_0001_B.EndDate};
Local NumberVar d3:= if {Datestable.Dates} in[{AX_0001_A.StartDate} to {AX_0001_B.EndDate}]
and {Datestable.Holiday}=1 then d3:=1
else
d3:=0;



DateDiff ("d", d1, d2) -
DateDiff ("ww", d1, d2, crSaturday) -
DateDiff ("ww", d1, d2, crSunday) - d3

 

[synapsevampire]

I don't understand your premise here.

If the View is returning multiple rows, then the dates table will ONLY return one row per when joined. You might consider applyinf the date selection against the dates table itself.

Anyway, the approach I outlined is standard fare, hopefully you can figure out your row inflation or suppress out what you don't need.

-k

 

[dpatrickcollins]

dadyswat, I would suggest table linking to define the join. If I recall, certain versions of CR will not support different operators for joins between the same table pair. (Sorry, it's 1:00 a.m. here and I'm too tired to verify). CR10 supports it for sure, however.