It's easy and flexible to include weekends in a Calendar table, usually with two flags one that says weekends, one that says holidays.  Sometimes Christmas falls on a Saturday,...

Hmm, I don't believe I used a CROSS TAB.  Yes, I used a CASE in a SUM, but not in a CROSS TAB fashion, from my understanding of a CROSS...

Good Point Sergiy.

Public holidays are also excluded.   , but right now Id just like to solve the weekends and then see if there is a solution for hoildays...

You're welcome!  Yeah, I'm sorry too, but I couldn't figure out exactly what values to return in what columns based on your original query, so I was kinda' forced to...

Also, be aware RCSI has a 14 byte per row added overhead.  If there isn't space available in the existing data pages, you can likely expect a lot of page splits...

Do...

Thank you for your guidance and reformatting this...it is helpful.

However the main calculation that I'm looking for is to  figure out how to add the days and exclude the...

I've added some date calc ctes before the query to isolate the date calcs from the main query (also makes it much easier to test them standalone for assorted dates). ...

No problem.  I don't have a real problem with CROSS APPLY, other than the performance hit.  I personally am not a big fan of UNPIVOT, I find the syntax kludgy...

You really don't need UNPIVOT or CROSS APPLY for this.  They do add some overhead.

SELECT
    Doc,
    SUM(CASE WHEN CHARINDEX('d', Op1+Op2+Op3+Op4) > 0 
        THEN 1 ELSE 0 END)...

I pointed out CHECKPOINT.

I have servers with hundreds of dbs.  If you just slam them down, the recovery...

You can add an EXEC AS.  Create a login that has only the specific authority it needs in the mgmt db to INSERT to the table; i.e., it doesn't need...

Agree completely, it should work, as long as the drive has the same letter and paths, just verify that the needed permissions are all back in place.

Scott - Thanks for the reply. So when you stop the Agent, what happens to the jobs that were supposed to run during the stoppage? That's mainly what...