Thanks for the test data!  Sorry if I seemed difficult, but I help on up to dozens qs a day, and I just don't have to do data prep for...

By default, I think Oracle timestamp only has 6 digit precision for sub-seconds (FULL DISCLOSURE: I haven't been an Oracle DBA since Oracle 8).  Try cutting the sub-seconds from 7...

Hmm, decent start, but I can't query against it.

I need an actual INSERT statement, that works, like this:

CREATE TABLE #data ( customer int NOT NULL, myDateTime datetime NULL );

INSERT INTO...

Phil - Correct.  Devs want it all in dev.

Ken - AWESOME  "INSTEAD OF"  Why didn't I think of that.  Is a trigger to delete  ##  history table records...

Phil - Correct.  Devs want it all in dev.

Ken - AWESOME  "INSTEAD OF"  Why didn't I think of that.  Is a trigger to delete  ##  history table records WHEN...

That's from the ROLLUP.

The one with NULL, NULL is the global dayname total, so all Mondays, Tuesdays, etc., regardless of year, month.

The one with NULL, YEAR is supposed to be...

Well, you fixed it, but glad I helped in some way :-).

Is your msdb data or log file full?

Do you see any errors in the SQL error log that could be related to the issue?

Agree with Ken.  Use a trigger to only write the data you want in the table to begin with.

The only thing Ken left out was a:

SET NOCOUNT ON

at the start...

You didn't provide any sample data, so I can't test my code, but something like this should do what you need:

SELECT COUNT(*) AS DayCount

,YEAR(myDateTime) AS Year
,MONTH(myDateTime) AS...

When you create the proc, you would need to specify EXEC AS OWNER, making sure the OWNER is a high-level user.

When you just give permission to EXEC the proc, the...

Yes.  The format is fixed, with the dashes always in the same locations.

I suggest creating the following indexes to support the query.  If you'd like to adjust other indexes on the tables also, just let me know.

CREATE...

If you can, review the query plan to verify that those indexes are actually being used.  Without a query plan, we can't tell for sure.

I suggest creating the following indexes to support the query.  If you'd like to adjust other indexes on the tables also, just let me know.

CREATE...