how to remove next and prev record if amount = 0

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 2012 Forums

Transact-SQL (2012)

how to remove next and prev record if amount = 0

Author

Topic

cojimarmiami
Starting Member

6 Posts

Posted - 2014-06-02 : 14:09:22

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-02 : 14:22:18

Do you have a column that can be used to determine order?

cojimarmiami
Starting Member

6 Posts

Posted - 2014-06-02 : 14:32:50

the one I used to order that data was SHCOMP

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-02 : 14:47:41

Sets don't have an order, so there no concept of "Next" or "Previous" without being able to order the set. So, without the ability to order your data set, it's pretty hard to get a consistent result.

cojimarmiami
Starting Member

6 Posts

Posted - 2014-06-02 : 14:52:23

we can put that data into another temp table adding a column with consecutive numbers

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-06-02 : 15:07:03

If you change the order so that a positive value come before a negative one (SHAMT = 60), then you can use the LAG and LEAD functions. If that is not an option, then things get more difficult.

CREATE TABLE #Temp(
ID INT IDENTITY(1,1),
[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 #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, 'T'), -- Reversed
(234, 'cust3', 'desc2',-60, 'F'), -- Reversed
(234, 'cust3', 'desc2',30, 'F'),
(234, 'cust3', 'desc2',-30, 'T'),
(234, 'cust3', 'desc2',30, 'F')


SELECT 
	*
	,CASE 
		WHEN
		( 
			SHAMT > 0
			AND SHAMT + NextVal = 0 
		)
		OR
		(
			SHAMT < 0
			AND SHAMT + PreviousVal = 0 
		)
		THEN 'Remove'
	ELSE NULL END
FROM
	(
		SELECT 
			*
			,LEAD(SHAMT, 1, 0) OVER (ORDER BY ID) AS NextVal
			,LAG(SHAMT, 1, 0) OVER (ORDER BY ID) AS PreviousVal
		FROM 
			#Temp
	) AS T

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources