Alternating column value based on datetime column in T-SQL Query

Apologies for the terrible title, not really sure how to explain this without a sample:

I'm trying to return a column in a query that alternates between 0 and 1 as the hour of a datetime column over the result set changes. Does anybody have suggestions of a good way to do this?? OR how it could be done with CTEs

I started looking into using CTEs, but didn't get very far and am hoping there's an easier way. See below for a very basic example of what I'm hoping to achieve, with current output and the output I want. NOTE that I've created a column [band] that does what I want provided the times are all on the same day. Of course it breaks when then the dates cover more than one day.

declare @test as table(id int, dt datetime, comment varchar(50))

insert into @test values(1, '2011-01-01 07:00', 'one')
insert into @test values(2, '2011-01-01 07:30', 'two')
insert into @test values(3, '2011-01-02 07:50', 'three')
insert into @test values(4, '2011-01-03 08:00', 'four')
insert into @test values(5, '2011-01-03 08:50', 'five')
insert into @test values(6, '2011-01-03 09:00', 'six')
insert into @test values(7, '2011-01-03 10:00', 'seven');

select *, DATEPART(HOUR, dt) % 2 as [band]
from @test

CURRENT OUTPUT

1   2011-01-01 07:00:00.000 one     1
2   2011-01-01 07:30:00.000 two     1
3   2011-01-02 07:50:00.000 three   1
4   2011-01-03 08:00:00.000 four    0
5   2011-01-03 08:50:00.000 five    0
6   开发者_StackOverflow中文版2011-01-03 09:00:00.000 six     1
7   2011-01-03 10:00:00.000 seven   0

REQUIRED OUTPUT

1   2011-01-01 07:00:00.000 one     1
2   2011-01-01 07:30:00.000 two     1
3   2011-01-02 07:50:00.000 three   0
4   2011-01-03 08:00:00.000 four    1
5   2011-01-03 08:50:00.000 five    1
6   2011-01-03 09:00:00.000 six     0
7   2011-01-03 10:00:00.000 seven   1

Note here that I want the [band] column to alternate as the datetime in the rows change to new hours. Whether they're the next hour in the same day or another hour in the following day.

How about?

SELECT *, 
    (dense_rank() over (order by Dateadd(hh,Datediff(hh,0,dt),0))) % 2 as [band]
FROM @test

I don't have sql at hand right now. But

1. The dateadd/datediff cleans the minutes off the hours
2. The dense_rank then orders them like rownumber but doesn't number duplicates
3. then the %2 does what you were doing in your original query.

As I understand the question:

You want to compare the datetime value of each record to the prior record, and if the hour or any value higher than an hour has changed, return 1, else return 0.

There is no great way to do this - you must iterate. In SQL, iteration means cursors:

DECLARE timeLoop CURSOR FOR
SELECT ID, DT 
FROM @test
ORDER BY ID

DECLARE @Id Int
DECLARE @Current DATETIME
DECLARE @Last DATETIME
DECLARE @Change bit
OPEN timeLoop

FETCH NEXT FROM timeLoop into @ID, @Current
SET @LAST = DATEADD(-1, DY, @Current)

WHILE @FETCH_STATUS = 0
BEGIN
    IF ABS(DATEDIFF(h, @Last, @Current) >= 1) SET @Change = 1
    ELSE SET @Change = 0

    SELECT @Id, @Dt, @Change
    SET @Last = @Current

    FETCH NEXT FROM timeLoop into @ID, @Current
END

CLOSE timeLoop
DEALLOCATE timeLoop

Apologies for any syntax errors above - I don't have sql server on this box and haven't validated the exact syntax, but that will get you going.

Actually I don't think you need a CTE at all

SELECT a.ID, a.DT, ISNULL(b.DT, '1/1/1970')
    CASE     WHEN ABS(DATEDIFF(h, a.dt, ISNULL(b.DT, '1/1/1970')) < 1 THEN 1 
            DEFAULT 1 
    END AS Change
FROM @Test a
LEFT JOIN @Test b
    ON a.ID = b.ID-1

JOin the table to itself on ID to ID-1 (and listen to the DBA's scream) on a left join, and the first row will join to null, each subsequent row will join to the prior row.