Counting workdays using date dimension

Regular Visitor

I have a query.  Trying to count workdays between two dates in fact table.  in dimDate workdayFlag = 1 for true and 0 for false.






(select sum(workdayFlag) from dimDate where Cal_date between receipt_date and Promissed_date) as 'dayVariance'

from factPO


this seems to work but am wondering if this could be written by joining factPO and dimDate.  An inline query seems expensive if my result set is in the millions of records.



from factPO join dimDate on








1 Reply
best response confirmed by computermike (Regular Visitor)
This is how I would do it, most likely, because if you did it as a join, you would need to do a group by to eliminate the extra rows that would be added by the join (or not, if there were no work days between them).

You could also use a function/derived table in a join with a CROSS APPLY, but I don't think that would be better (especially since you only want the one value... if you needed 2 columns, then it would make sense.).

I would just consider an index on cal_date that includes workdayflag if you are having any performance issues...