January 27, 2005 at 10:56 am
Dear Forum,
I have some long data with eight rows for each person and an accounting period (see sample data at bottom of post).
I want to check that for each person, the distance between each of their eight accounting periods =3.
I have to sort the data to get the periods in order so have no field as yet that tells me the order.
All my ideas so far seem far too complicated.
Is there an elegant solution to this? DTS, Cursors, UDFs perhaps?
Thanks, Bill
ID | Period |
1 | 360 |
1 | 363 |
1 | 366 |
1 | 369 |
1 | 372 |
1 | 375 |
1 | 378 |
1 | 381 |
2 | 361 |
2 | 364 |
2 | 367 |
2 | 369 |
2 | 372 |
2 | 375 |
2 | 378 |
2 | 381 |
January 27, 2005 at 11:02 am
Related topic from yesterday:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=157768
January 27, 2005 at 11:42 am
Thanks PW,
This will do the job great.
Cheers, Bill
January 27, 2005 at 4:08 pm
For those not having Yukon, etc I wrote this query.
The query tells you if the person has the 8 accounting periods required and that the distances between the periods is 3.
select C.id as Person, count(*) as Distances, 'Missing Periods' as Msg
from (select A.id from Acct A inner join Acct B on A.id=B.id
where B.period - a.Period = 3) C
group by C.id
having count(*)<7
union all
select C.id , count(*), 'All 8 Periods'
from (select A.id from Acct A inner join Acct B on A.id=B.id
where B.period - a.Period = 3) C
group by C.id
having count(*)=7
order by C.id
Cheers,
Ben
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply