Removing offsetting records from table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Removing offsetting records from table

Author

Topic

scabral7
Yak Posting Veteran

57 Posts

Posted - 2011-01-25 : 19:54:10

Hi,

i have the follwing table:

select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
into #temp
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd

Acct_No svc_Dtime Qty cpt_cd
12345 10-01-2010 1 8000
12345 10-01-2010 1 8000
12345 10-01-2010 1 8000 --remove(offset with -1)
12345 10-01-2010 1 8000 --remove(offset with -1)
12345 10-01-2010 -1 8000 --remove(offset with 1)
12345 10-01-2010 -1 8000 --remove(offset with 1)
67890 10-01-2010 1 8000
67890 10-01-2010 1 8000
67890 10-01-2010 1 8000 --remove(offset with -1)
67890 10-01-2010 1 8000 --remove(offset with -1)
67890 10-01-2010 -1 8000 --remove(offset with 1)
67890 10-01-2010 -1 8000 --remove(offset with 1)

i want to remove any offsetting records based on the Qty field for the same acct_no, svc_dtime and cpt_cd. For example in the above table, i would want to remove 2 negative 1 records and 2 positive 1 records for each acct and return the remaining fields:

Acct_No svc_Dtime Qty cpt_cd
12345 10-01-2010 1 8000
12345 10-01-2010 1 8000
67890 10-01-2010 1 8000
67890 10-01-2010 1 8000

Is there a way to do this without using cursors?

thanks
scott

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-02-15 : 15:22:52

Select acct_no,svc_dtime,cpt_cd,qty from (
Select *,ROW_NUMBER() over (partition by acct_no,svc_dtime,cpt_cd order by acct_no,svc_dtime,cpt_cd,qty desc)rowNo
from (
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL

select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd
UNION ALL
select '12345' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd
UNION ALL
select '67890' as acct_no,
'10-01-2010' as svc_dtime,
-1 as qty,
'8000' as cpt_cd)a
)Final Where rowNo<=2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources