Basic Cursor in MS SQL Server

问题:

I am looking for basic direction on Cursor use in MSSS.

Say there is a table, TABLE1, with 2 fields (ID, Date). The ID is not a unique key. The table records events by id, and some ids occur frequently, some infrequently.

For example:

ID  |  Date
1   |  2010-01-01
2   |  2010-02-01
3   |  2010-02-15
2   |  2010-02-15
4   |  2010-03-01

I would like to create a new table with the following fields: ID, Date, Number of times ID appears in 6 months previous to Date, Number of times ID appears in 6 months after Date.

Is there a best way to go about accomplishing this? Thanks kindly.

回答1:

This is one side (I think - not tested)

select t1.id, t1.date, count(*) as 'count'
from table t1 
join table t2 
  on t2.id = t1.id
 and DateDiff(mm,t1.date,t2.date) <= 6 
 and DateDiff(mm,t1.date,t2.date) >  0 
group by t1.id, t1.date

I think you can skip the > 0 and use case to count the positive and negative

sum(WHEN t1.date > t2.date then 0 else 1) as prior 
sum(WHEN t1.date < t2.date then 0 else 1) as next 

and DateDiff(mm,t1.date,t2.date) <= 6 
and DateDiff(mm,t2.date,t2.date) <= 6 

May have prior and next backwards