Counting workdays using date dimension

[computermike]

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

PO_Item,

Receipt_date,

Promissed_date,

(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.

select 

from factPO join dimDate on

[#2 · Accepted solution]

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...