2 ChrisM@home

10x

select cast(CAST(getdate() as DATE) as datetime)

results of running Jeff Moden's script on my 2008R2 with additional cast/cast convertion

(1000000 row(s) affected)

--===== DATEADD/DATEDIFF =============================================

SQL Server Execution Times:

...

2 ChrisM@home

even more simple 🙂declare @subgroupsize int

set @subgroupsize=3

select dense_rank() over(order by act,grp desc) grp_no,1+(rn-1)/@subgroupsize grp_sub_no

,grp,member,act

from

(select * ,ROW_NUMBER() over(partition by act,grp order by (select 0))...

2 mic.con87

Sure

expression DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105') calculates latest Friday before or on a given reference date

The rest is clear

dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105', IncidentDate) /7*7, '19000105')) ...

;with

q1 as (select *

,case when exists(select * from #Broker b where b.Broker=td.Broker) then 1 else 0 end StartT

,case when exists(select * from #Portfolio p where p.Portfolio=td.Portfolio) then 1 else 0...

declare @Incidents table(IncidentDate datetime)

insert into @Incidents

select '20111216 18:59:00' union all

select '20111216 19:00:00' union all

select '20111218 19:00:00' union all

select '20111218 19:01:00'

SELECT IncidentDate

,case when IncidentDate between dateadd(hh,19,DATEADD(day, DATEDIFF(day, '19000105',...

Jeff Moden (12/19/2011)

---

Just thought I'd throw these in... they get rid of the behind-the-scenes operation of parenthetical grouping and remove one extra subtraction (for the month end calculation) for a...

declare @y int

declare @m int

select @y=2012,@m=2

select dateadd(month,(@y-1900)*12+@m,0)-1