CODEWITHSUNDEEP
sqlsql-server-2008
Shaggy
Shaggy

Reputation: 5800

Update table based on conditions in another table

I have a table ATMStatusHistory. I want an IF condition which states that if all the Resolved column values for a particular ticket are 1 then update a Ticket table to Closed = 1 for all the codes involved with that ticket.

Code    RawFeed   Resolved  Ticket
2341283 2350426   0         205656
2341460 2350603   1         205656
2341597 2350740   1         205656
2341689 2350832   1         205656
2341825 2350968   0         205656

I have written an IF condition but it's not working:

    If Not Exists (Select * From ATMStatusHistory Where Ticket = @OriginalTicket
         AND Resolved=0 And RawFeed = 0)
        {
Insert InTo TicketFollowUp(Ticket, FollowUpText, FollowUpBy, FollowUpOn) 
  Select Code, Comments, UpdatedBy, UpdatedOn 
  From Ticket Where Code = @OriginalTicket                  

          Update Ticket Set Closed=1, ClosedOn=GetDate(), UpdatedBy=0, 
           UpdatedOn=GetDate(), Comments=' Ticket Closed by System as all Faults cleared. ' 
           Where Code = @OriginalTicket         
        }

Upvotes: 0

Views: 111

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280570

Adding the new requirement of an insert statement:

IF NOT EXISTS 
(
  SELECT 1 FROM dbo.ATMStatusHistory 
    WHERE Ticket = @OriginalTicket
    AND Resolved = 0 AND RawFeed = 0
)
AND EXISTS
(
  -- want to be sure we only "close" a ticket once
  SELECT 1 FROM dbo.Ticket
    WHERE Ticket = @OriginalTicket
    AND Closed = 0
)
BEGIN
  INSERT dbo.TicketFollowUp(Ticket, FollowUpText, FollowUpBy, FollowUpOn) 
    SELECT Code, Comments, UpdatedBy, UpdatedOn
    FROM dbo.Ticket WHERE Code = @OriginalTicket;

  UPDATE dbo.Ticket
   SET Closed   = 1, 
      ClosedOn  = GETDATE(), 
      UpdatedBy = 0, 
      UpdatedOn = GETDATE(),
      Comments  = ' Ticket Closed by System as all Faults cleared. ' 
  WHERE Code = @OriginalTicket;
END

Upvotes: 3

Ivan Golović
Ivan Golović

Reputation: 8832

You can try this demo:

CREATE TABLE #ATMStatusHistory  (Code  INT,  RawFeed  
    INT, Resolved INT, Ticket INT)
INSERT #ATMStatusHistory VALUES
(2341283 ,2350426   ,0         ,205656),
(2341460 ,2350603   ,1         ,205656),
(2341597 ,2350740   ,1         ,205656),
(2341689 ,2350832   ,1         ,205656),
(2341825 ,2350968   ,0         ,205656)

CREATE TABLE #Ticket (Code INT, Closed INT, ClosedOn 
    DATETIME, UpdatedBy INT, UpdatedOn DATETIME, 
    Comments VARCHAR(1000))
INSERT #Ticket VALUES
(2341283 ,1, '20120101', 1, '20120101', 'test'),
(2341460 ,1, '20120101', 1, '20120101', 'test'),
(2341597 ,1, '20120101', 1, '20120101', 'test'),
(2341689 ,1, '20120101', 1, '20120101', 'test'),
(2341825 ,1, '20120101', 1, '20120101', 'test')

DECLARE @OriginalTicket INT = 205656



UPDATE  #Ticket
SET     Closed = 1,
        ClosedOn = GETDATE(), 
        UpdatedBy = 0,
        UpdatedOn = GETDATE(),
        Comments = 'Ticket Closed by System as all Faults cleared.'
FROM    #Ticket a
JOIN    #ATMStatusHistory b ON
        b.Code = a.Code
        AND 1 = ALL(
            SELECT  x.Resolved 
            FROM    #ATMStatusHistory x 
            WHERE   x.Ticket = @OriginalTicket
            AND     x.RawFeed <> 0      
        )

SELECT  *
FROM    #Ticket

DROP TABLE #ATMStatusHistory
DROP TABLE #Ticket

Upvotes: 1

Related Questions