Delete if exists - SQL Server Forums

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)

Delete if exists

Author

Topic

DieterC
Starting Member

1 Post

Posted - 2013-12-17 : 03:00:52

Hi guys,
I'm having trouble writing this query i'm looking for.

Current situation:

HstDate	      ID	ID2	CNK	Desc  Group	Price
----------------------------------------------------------------------
2013-12-01   38521	3	38521	Cat D		        0.622
2013-12-01   38521	2	706044	Ziek SXRhA	A	0.681
2013-12-01   38521	1	706044	Amb SXRaA	A	0.829
2013-12-01   38521	0	38521	Pub SA_Ar	A	20.850
2013-04-01   38521	3	38521	Cat D		        0.622
2013-04-01   38521	2	706044	Ziek SXRhA_	A	0.681
2013-04-01   38521	1	706044	Amb SXRaA_	A	0.829
2013-04-01   38521	0	38521	Pub SA_Ar	A       20.850

What i'm looking for is a query that deletes all rows from the most recent date, but only if there are NO differences according to the previous date.
For example if the first row was

2013-12-01   38521	3	38521	Cat D		        0.555

then the whole group on '2013-12-01' should stay.

Thanks in advance,
Dieter

sqlsam
Starting Member

1 Post

Posted - 2013-12-17 : 05:58:59

CREATE TABLE MEDICAL_PRICING
(
hstdate date,
id int,
id2 int,
cnk int,
[desc] nvarchar(20),
[group] nvarchar(20),
price dec(16,2)
)
--
--DUP
INSERT INTO MEDICAL_PRICING
VALUES('2013-12-01',38521,3,38521,'Cat', 'D',0.622)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-12-01',38521,3,38521,'Cat', 'D',0.622)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-12-01' ,38521, 2, 706044,'Ziek SXRhA' ,'A', 0.681)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-12-01' , 38521, 1, 706044, 'Amb SXRaA', 'A', 0.829)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-12-01' , 38521 ,0, 38521, 'Pub SA_Ar', 'A', 20.850)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-04-01' , 38521, 3 ,38521 ,'Cat' , 'D' , 0.622)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-04-01' , 38521, 2 ,706044, 'Ziek SXRhA_', 'A' ,0.681)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-04-01' , 38521, 1, 706044, 'Amb SXRaA_', 'A', 0.829)
--
INSERT INTO MEDICAL_PRICING
VALUES('2013-04-01' , 38521, 0 ,38521, 'Pub SA_Ar', 'A' , 20.850)
--
SELECT * FROM MEDICAL_PRICING
--
--FIND NO DIFFERENCES
SELECT COUNT(*)
FROM MEDICAL_PRICING A
INNER JOIN MEDICAL_PRICING B
ON A.HSTDATE = B.HSTDATE
WHERE
(A.HSTDATE IN (SELECT MAX(HSTDATE) FROM MEDICAL_PRICING)) AND
(A.ID2 = B.ID2 AND
A.CNK = B.CNK AND
A.[DESC] = B.[DESC] AND
A.[GROUP] = B.[GROUP])
--
SELECT * FROM MEDICAL_PRICING WHERE HSTDATE IN
(SELECT MAX(HSTDATE) FROM MEDICAL_PRICING)
ORDER BY ID2
--SELECT COUNT(*) AS DELETE_THIS_NUM_OF_RECS
SELECT *
FROM MEDICAL_PRICING A
WHERE A.HSTDATE IN (SELECT MAX(HSTDATE) FROM MEDICAL_PRICING)
--TOTAL SET OF ALL
AND
NOT
EXISTS (--SET WHOSE ATTRIBS ARE NOT EQUAL(DIFF EXIST)
SELECT 1 FROM MEDICAL_PRICING B
WHERE
B.ID2 <> A.ID2 AND
B.CNK <> A.CNK AND
B.[DESC] <> A.[DESC] AND
B.[GROUP] <> A.[GROUP]
)

sql is surreal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources