how to remove next and previous record is amount = 0?

Question

how to remove next and previous record is amount = 0?

otto840513

SSC Rookie

Points: 40
More actions
June 2, 2014 at 12:06 pm

#396848

having below data. SHCOMP (Customer ID), CBLNAM ( Customer name), SHDESC (service description), SHAMT (service charge), SHTYPE (F-> From T-> To)
CREATE TABLE #Temp(
[SHCOMP] [numeric](7, 0) NOT NULL,
[SHDESC] [char](35) NOT NULL,
[SHTYPE] [char](1) NOT NULL,
[SHAMT] [numeric](9, 2) NOT NULL,
[CBLNAM] [char](30) NOT NULL,
)
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F
123 cust1 desc1 -45 T
123 cust1 desc1 45 F
123 cust1 desc1 -45 T
123 cust1 desc1 45 F
123 cust1 desc1 -35 T
234 cust3 desc2 -60 F
234 cust3 desc2 60 T
234 cust3 desc2 30 F
234 cust3 desc2 -30 T
234 cust3 desc2 30 F
I need to remove records that have the same ID, Name, Description and Amount summary = 0, For example
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F
123 cust1 desc1 -35 T
234 cust3 desc2 -60 F remove
234 cust3 desc2 60 T remove
234 cust3 desc2 30 F remove
234 cust3 desc2 -30 T remove
234 cust3 desc2 30 F
Results
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F
123 cust1 desc1 -35 T
234 cust3 desc2 30 F
here is what I did but didn't work because sum is <> from 0 so is leaving all records
select SHCUST, SHDESC, ABS(SHAMT) SHAMT
into #t1
FROM #Temp
group by SHCUST, SHDESC, ABS(SHAMT)
having SUM(SHAMT)=0
order by SHCUST
delete S from #Temp S
join #t1 T on T.SHCUST = S.SHCUST and T.SHDESC = S.SHDESC
Please let me know if you need more explanation
Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 1

This should get you started. In order to produce a complete answer, a order must be specified for the set, as it is, it is not possible. A hint for a complete solution would be LEAD() and LAG().

😎

USE tempdb;

GO

CREATE TABLE #Temp(

[SHCOMP] [numeric](7, 0) NOT NULL,

[SHDESC] [char](35) NOT NULL,

[SHTYPE] [char](1) NOT NULL,

[SHAMT] [numeric](9, 2) NOT NULL,

[CBLNAM] [char](30) NOT NULL,

);

INSERT INTO #Temp

(

SHCOMP

,CBLNAM

,SHDESC

,SHAMT

,SHTYPE

)

VALUES

(123,'cust1','desc1', 45 ,'F')

,(123,'cust1','desc1', -45 ,'T')

,(123,'cust1','desc1', 45 ,'F')

,(123,'cust1','desc1', -45 ,'T')

,(123,'cust1','desc1', 45 ,'F')

,(123,'cust1','desc1', -35 ,'T')

,(234,'cust3','desc2', -60 ,'F')

,(234,'cust3','desc2', 60 ,'T')

,(234,'cust3','desc2', 30 ,'F')

,(234,'cust3','desc2', -30 ,'T')

,(234,'cust3','desc2', 30 ,'F');

SELECT

ROW_NUMBER() OVER

(

PARTITION BY

SHCOMP

,CBLNAM

,SHDESC

,ABS(SHAMT)

ORDER BY

(SELECT NULL)

) AS IRID

,DENSE_RANK() OVER

(

ORDER BY

SHCOMP

,CBLNAM

,SHDESC

,ABS(SHAMT)

) AS IDRNK

,SUM(SHAMT) OVER

(

PARTITION BY

SHCOMP

,CBLNAM

,SHDESC

,ABS(SHAMT)

ORDER BY

SHCOMP

,CBLNAM

,SHDESC

,ABS(SHAMT)

ROWS BETWEEN UNBOUNDED PRECEDING

AND CURRENT ROW

) AS SHBLNC

,SHCOMP

,CBLNAM

,SHDESC

,SHAMT

,SHTYPE

FROM #Temp;

DROP TABLE #Temp;

Results

IRID IDRNK SHBLNC SHCOMP CBLNAM SHDESC SHAMT SHTYPE

----- ------ ------- ------- ------- ------- ------- ------

1 1 -35.00 123 cust1 desc1 -35.00 T

1 2 45.00 123 cust1 desc1 45.00 F

2 2 0.00 123 cust1 desc1 -45.00 T

3 2 45.00 123 cust1 desc1 45.00 F

4 2 0.00 123 cust1 desc1 -45.00 T

5 2 45.00 123 cust1 desc1 45.00 F

1 3 30.00 234 cust3 desc2 30.00 F

2 3 0.00 234 cust3 desc2 -30.00 T

3 3 30.00 234 cust3 desc2 30.00 F

1 4 -60.00 234 cust3 desc2 -60.00 F

2 4 0.00 234 cust3 desc2 60.00 T

Edit: added comment.

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 2

Unless this was an accidental duplication of data, there's no way that I'd do this. You're destroying historical data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182912 More actions · Answer 3

Jeff Moden (6/2/2014)
Unless this was an accidental duplication of data, there's no way that I'd do this. You're destroying historical data.

Second that but then again that's pondering on the why not how.

Any requirement implying an order of a set, such as next/previous, must have a order directive. This set has none.

😎

Jeff Moden SSC Guru Points: 1004376 More actions · Answer 4

Eirikur Eiriksson (6/3/2014)
Jeff Moden (6/2/2014)
Unless this was an accidental duplication of data, there's no way that I'd do this. You're destroying historical data.
Second that but then again that's pondering on the why not how.
Any requirement implying an order of a set, such as next/previous, must have a order directive. This set has none.
😎

The "WHY" would be the "Business Reason" and without that, I fear that someone is getting ready to make a huge mistake. I'm trying to protect that person from making such a mistake.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Jeff Moden (6/3/2014)
Eirikur Eiriksson (6/3/2014)
Jeff Moden (6/2/2014)
Unless this was an accidental duplication of data, there's no way that I'd do this. You're destroying historical data.
Second that but then again that's pondering on the why not how.
Any requirement implying an order of a set, such as next/previous, must have a order directive. This set has none.
😎
The "WHY" would be the "Business Reason" and without that, I fear that someone is getting ready to make a huge mistake. I'm trying to protect that person from making such a mistake.

Assuming the OP is describing a step within a process which generates output rather than directly cropping a fact table, then this might do:

SELECT SHCOMP, CBLNAM, SHDESC, SHAMT, SHTYPE

FROM ( -- d

SELECT SHCOMP, CBLNAM, SHDESC, SHAMT, SHTYPE,

sm = SUM(SHAMT) OVER (PARTITION BY SHCOMP, CBLNAM, SHDESC, ABS(SHAMT))

FROM #Temp

) d

WHERE SHAMT = sm

GROUP BY SHCOMP, CBLNAM, SHDESC, SHAMT, SHTYPE

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden