Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Removing offsetting records from table

    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

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Anything wrong with:
    Code:
    select acct_no, svc_dtime, sum(qty) as qty, cpt_cd
    from #temp
    GROUP BY acct_no, svc_dtime, cpt_cd
    This solution is very simple and uses no cursors.

    Do you REALLY, REALLY need to have multiple lines for each unique acct_no / svc_dtime / cpt_cd combination in the result?
    Last edited by Wim; 01-26-11 at 05:03.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by Wim View Post
    Anything wrong with:
    Code:
    select acct_no, svc_dtime, sum(qty) as qty, cpt_cd
    from #temp
    GROUP BY acct_no, svc_dtime, cpt_cd
    I don't think the OP wanted the sum of quantity. Maybe this will do it? It's not as simple, but not too hard either
    Code:
    select t1.acct_no, t1.svc_dtime, t1.cpt_cd, t1.qty from (
       select rowid=row_number() over(partition by t0.acct_no, t0.svc_dtime, t0.cpt_cd order by t0.acct_no, t0.svc_dtime, t0.cpt_cd)
      ,t0.acct_no, t0.svc_dtime, t0.cpt_cd, t0.qty
       from #temp t0
       where t0.qty > 0) t1
       inner join (select rowid=row_number() over(partition by t.acct_no, t.svc_dtime, t.cpt_cd order by t.acct_no, t.svc_dtime, t.cpt_cd)
      ,t.acct_no, t.svc_dtime, t.cpt_cd, t.qty
       from #temp t
       where t.qty < 0
       ) t2 on t1.rowid = t2.rowid+1 and t1.acct_no=t2.acct_no and t1.svc_dtime=t2.svc_dtime and t1.cpt_cd=t2.cpt_cd
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Nice coding.

    I was intrigued by your code and have been studying it. I couldn't figure out how
    Code:
    t1.rowid = t2.rowid+1 and
    could work. Yet your code gave the expected result. So I added
    Code:
    UNION ALL
    select '12345' as acct_no,
    '10-01-2010' as svc_dtime,
    -1 as qty,
    '8000' as cpt_cd
    at the end of scabral7's initial post, to see what it would result in. I expected this result:
    Code:
    Acct_No svc_Dtime Qty cpt_cd
    12345 10-01-2010 1 8000
    67890 10-01-2010 1 8000
    67890 10-01-2010 1 8000
    But this was the result it returned:
    Code:
    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
    67890 10-01-2010 1 8000
    67890 10-01-2010 1 8000
    Besides 1 and -1, are other values for Qty possible?
    When possible, it should be taken into account that Qty 2 and -2, 3 and -3, ... will offset each other.
    And how about two occurrences of Qty 1, will that offset one occurrence of Qty -2 (in that case reverting to SUM would be a lot easier to program)?
    The problem definition isn't clear enough to answer those questions though.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, you are right, it was late last night for things like this
    Code:
    select t1.acct_no, t1.svc_dtime, t1.cpt_cd, t1.qty from (
       select
          rowid=row_number() over(
             partition by t0.acct_no, t0.svc_dtime, t0.cpt_cd
             order by t0.acct_no, t0.svc_dtime, t0.cpt_cd
             )
         ,t0.acct_no, t0.svc_dtime, t0.cpt_cd, t0.qty
          from #temp t0
          where t0.qty > 0
       ) t1 where not exists (select * from (
          select rowid=row_number()
             over(partition by t.acct_no, t.svc_dtime, t.cpt_cd
             order by t.acct_no, t.svc_dtime, t.cpt_cd
          ), t.*
          from #temp t
          where t.qty < 0
       ) t2 
          where t1.rowid = t2.rowid
          and t1.acct_no=t2.acct_no 
          and t1.svc_dtime=t2.svc_dtime 
          and t1.cpt_cd=t2.cpt_cd
    )
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I have added
    Code:
    UNION ALL
    select '67890' as acct_no,
    '10-01-2010' as svc_dtime,
    -1 as qty,
    '8000' as cpt_cd
    five times at the end of the INSERT statement in scabral7's initial post. The negative Qty's outnumber the positive QTy's, and should show in the result set. They don't.

    But never mind. Scabral7 hasn't responded on this thread. He doesn't seem to be interested any more in a solution for his problem; why should we?
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  7. #7
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Well, the task was to ensure that row-level "negation" is occurring. It didn't say that once negation exhausts itself the prevailing "anti-matter" should take its place.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •