Lynn, have you tried using your tally table and substring([input],N,1) to reduce the [input] string to a series of individual characters?

You could put all your special codes to be...

An alternative is to just back up the starting date to cover your date...

Guys, I'm trying to figure out the disconnect here. I am running against a table of almost 41 million rows using the OPs table description. ...

After thinking about times being included, we don't need to waste cycles converting Check_Date from datetime to date. We just need to go a little bit more...

twin.devil (8/12/2016)

I agree it...

IF you can afford a nonclustered index on check_date, this will drastically reduce the i/o.

;with Eomonths (Eom) as (select top(500) EoMonth(DateAdd(MONTH,(N-1),'1/1/2016')) from tally)

select Eom as Check_date, Total

from Eomonths

cross apply (select...

Alan, just out of curiousity, would you be so kind as to test this solution and compare it to your final?

This would have been my knee-jerk...

I think Grant and Brandie Tarvin should get together and write fantasy novels about execution plans. 😛

Hey Grant, Is Miskatonic U. going to wind up in the Big...

One last thing. Questions often come up where a count of days is needed excluding special days. You might find it handy to add a column to...

select @Period = dbo.calcperiod(value) from sys_registry where system_code = 'gbl' and name = 'reportdate';

This is my point. The first solution does NOT use the scalar function in the...

Use of a variable in a query keeps it from running in parallel?

Why, Lynn?...

-- query to solve the problem follows

;with cte as (select AreaNo,(WeekNo-1)/4+1 as Week4, count(*) as TotalRows, sum(Amt) as TotalAmt

from #data

group by AreaNo,(WeekNo-1)/4+1

)

-- the added ROLLUP is going to produce...

The issue with using LIKE is that it will do a complete table or index scan checking every row, unless you are looking for a title or description that starts...

Hey Jeff, are you going to use your Hierarchies on Steroids approach? 🙂