You'll need to index your tables properly or your performance on this will be horrible, but I think this is basically what you're looking for:

select a.id,

a.CustomerNumber,

a.AccessedDate,

a.OperatorName,

nlh.computer_nm,

nlh.login_dt

from action...

with cteTest (RowNum, EmpID, LogTime)

as

(

select ROW_NUMBER() OVER(PARTITION BY EmployeeID ORDER BY LogTime ASC),

EmployeeID,

LogTime

from #Test

)

select cc.EmpID,

cc.LogTime

from cteTest cc

left join cteTest cp

on cp.EmpID = cc.EmpID

and cp.RowNum = cc.RowNum - 1

where DATEDIFF(ss, coalesce(cp.LogTime,...

This is slightly more efficient:

SELECT DATEADD(hh, DATEDIFF(hh,0,'2010-04-04 14:15:02'), 0)

loki1049 (6/15/2010)

---

How can you justify doing an upsert (update, if not exist, insert) with the use of @@rowcount. I am finding a lot of instances on the net where you...

scott.pletcher (6/11/2010)

---

When I get a chance, I will write code to calc the variance of *any* set of numbers.

At any rate, what solution did *you* provide? NONE. All...

scott.pletcher (6/11/2010)

---

>> but when you post junior level code (non-SARG-able queries anyone?) <<

Yes, I took the short-cut, because as *I* pointed out the code will result in full scan anyway.

When...

scott.pletcher (6/11/2010)

---

Depends. I was answering the request to select only rows with a gap of 2 or less...

I posted a...

Jeff Moden (6/10/2010)

---

I'll also add what I said before. The data is being inserted in the order of 09, 11, 01 and everyone is just getting lucky with the...

scott.pletcher (6/11/2010)

---

You could rephrase the query to...

Lynn Pettis (6/11/2010)

---

Another option:

DECLARE @filterStart DATETIME

DECLARE @filterEnd DATETIME

SET @filterStart = '10/06/2010'

SET @filterEnd = '10/07/2010'

SELECT ...

WHERE

DueDate BETWEEN @filterStart AND @filterEnd

...

I agree with Lynn, all that is needed to get the exact same records is:

DECLARE @filterEnd DATETIME

SET @filterEnd = '10/07/2010'

SELECT ...

WHERE CASE WHEN DueDate < @filterEnd

scott.pletcher (6/10/2010)

---

You've got it. ...

This is basically the same as Jason's but it's checking to make sure that the year is the same and the month is literally the next month. So depending...

create table #PayPeriod

(

payPeriodint,

periodDescvarchar(50),

startDatedatetime,

endDatedatetime

)

insert into #PayPeriod (payPeriod, periodDesc, startDate, endDate)

select 108, 'Period 12/20 - 01/02', '12/20/2009', '1/2/2010' union

select 109, 'Period 01/03 - 01/16', '1/3/2010', '1/16/2010' union

select 110, 'Period 01/17 - 01/30',...

I could be missing something here ... I'll be honest, I didn't read through everything.

But I'm not sure on this where the cte recursion comes into play on this ......