CODEWITHSUNDEEP
sqlsql-serversyntax-error
pedro
pedro

Reputation: 1

Update column when datediff is greater then other column in the same table

I'm struggling with my school assignment, where I have to update a table column based on the difference between two dates. If the difference is lower or equal than column 'maturity' in the table, I had to set column 'Payment' to 'PaymentOK', if the difference is greater than 'maturity', the 'Payment' has to be set to 'Bilker'.

I've tried 

#IFNOTFieldExists(TableName=Invoices;FieldName=Payment;) 
Alter table Invoices ADD COLUMN Payment TEXT 
GO 
UPDATE Invoices SET Payment = CASE 
WHEN DATEDIFF (Day, Datum, ZaplateneDna) <= Maturity 
THEN 'PaymentOK' 
ELSE 'Bilker' 

END 
#ENDIF

Been trying IF THEN statements, UPDATE WHEN CASE statements but I'm still getting syntax errors. I'm a bit new in sql and I'm unable to combine UPDATE column1 IF DATEDIFF is <= or > then SET Column2 to (based on the <> sign).

Any ideas or advices please? Thank you in advance, much appreciated.

Upvotes: 0

Views: 598

Answers (1)

M.Ali
M.Ali

Reputation: 69554

UPDATE Invoices
SET Payment = CASE 
                  WHEN DATEDIFF(DAY, [Date], DateOfPayment) <= Maturity 
                  THEN 'PaymentOK'
                  ELSE 'Bilker' 
              END

Upvotes: 2

Related Questions