If one value exists then remove another

Question

I'm stuck in my query on how to remove or rather skip a post if another one exists.

This is my table.

if L_ID column have value 821 AND 201 for the same P_ID then "remove" or don´t use 201 and sum() then time

This would make P_ID 80 and 946 only have 2 rows.

This is probably easier than I think but I'm stuck.

sum Time after the remove of 201 and then Group by p_id,Lid and date — Nils, Oct 22 '15 at 08:40
Hi, if one of these answers could help you to solve your problem, you should vote helpful answers up and mark the most helpful as accepted. Thx! — Shnugo, Oct 22 '15 at 14:40

Shnugo · Accepted Answer · 2015-10-22T09:19:27.107

Try it like this:

CREATE TABLE #YourTable(P_ID INT, L_ID INT, [Date] Date, [Time] DECIMAL(6,2));
INSERT INTO #YourTable VALUES
 (80,201,{d'2015-08-01'},24.0)
,(80,821,{d'2015-08-01'},24.0)
,(80,822,{d'2015-08-01'},32.0)
,(946,201,{d'2015-08-01'},16.0)
,(946,821,{d'2015-08-01'},16.0)
,(946,819,{d'2015-08-01'},6.65)
,(6758,201,{d'2015-08-01'},7.25)
,(6758,200,{d'2015-08-01'},7.25)
;

--Test output
SELECT * FROM #YourTable;

--Set the SUMs in those lines with L_ID=821
UPDATE #YourTable SET [Time]=(SELECT SUM(x.[Time]) 
                              FROM #YourTable AS x 
                              WHERE x.P_ID =#YourTable.[P_ID] 
                                    AND x.L_ID IN (821,201))
WHERE #YourTable.L_ID=821

--Delete the rows with L_ID=201 if there is one with 821 too
DELETE FROM #YourTable 
WHERE L_ID = 201 AND EXISTS(SELECT * FROM #YourTable AS x 
                            WHERE x.P_ID = #YourTable.P_ID AND x.L_ID =821 ) --The ID was wrong here, sorry...

--Test output
SELECT * FROM #YourTable;

--Clean up
DROP TABLE #YourTable;

Result:

P_ID    L_ID    Date        Time
80      821    2015-08-01   48.00
80      822    2015-08-01   32.00
946     821    2015-08-01   32.00
946     819    2015-08-01   6.65
6758    201    2015-08-01   7.25
6758    200    2015-08-01   7.25

score 0 · Answer 2 · answered Oct 22 '15 at 09:11

Try this code:

SELECT *,
   SUM(Time) OVER(PARTITION BY P_ID, L_ID, Date) AS 'Sum'
FROM Your_Table
WHERE L_ID <> 201
   AND P_ID NOT IN (
      SELECT E1.P_ID
      FROM Your_Table E1
         INNER JOIN Your_Table E2
            ON E1.P_ID = E2.P_ID
      WHERE E1.L_ID = 821 AND E2.L_ID = 201)

If one value exists then remove another

2 Answers2