SQL Case statement based on dates

Question

I need to create a query that includes a CASE statement that basically says

CASE if getdate() > datedue by 31 days
    then status = 'Blocked
END

Does anybody know how to check if todays date is greater than say, 25/10/2012 by 31 days?

EDIT

Select co.OrderID, cu.FName + '  ' + cu.SName as 'Name', 
cu.Address1 + ', ' + cu.Address2 + ', ' + cu.Address3 as 'Dispatch Address', 
cu.PostCode, 
ma.MaterialName as 'Item',
mi.Price as 'Item Price',
co.DateOrdered as 'Order Date',
pm.DueDate,
pm.Overdue,

HERE I NEED TO WRITE A CASE STATEMENT TO INSERT INTO A LOCKEDACCOUNT TABLE
so for example CASE WHEN DATEDIFF(dd, GETDATE(), pm.DueDate) >= 31 THEN INSERT INTO LOCKEDACCOUNT (id, status, datelocked, customerid) VALUES (.....)
END

from Customers cu

Solution 1

Like this:

SELECT 
  CASE 
    WHEN DATEDIFF(dd, GETDATE(), @duedate) >= 1 THEN 'blocked' 
    ELSE 'Not' 
  END AS Status;

SQL Fiddle Demo

Note that: If you didn't specify an ELSE clause, the default will be NULL.

Update: You can insert into a table with CASE expression like so:

INSERT INTO Statuses VALUES
(CASE 
   WHEN DATEDIFF(dd, GETDATE(), CAST('20121025' AS DATE)) >= 31 THEN 'Blocked' 
   ELSE 'Not' 
 END);

Updated SQL Fiddle Demo

Solution 2

select CASE 
   WHEN DATEDIFF(dd,@duedate,getdate()) >= 31 then 'Blocked' else 'NO' end

Comments

[Annoscia]

could an insert be created to depending on if the duedate is > 31 days? so for example when ABS(DATEDIFF(dd, getdate(), datedue)) >= 31 THEN INSERT.......

[Mahmoud Gamal]

@Annoscia - Do you want to Update or insert?

[Annoscia]

the update works now thank you, but i think i may receive more marks on my assignment if i use an insert based on the CASE being > 31 days

[Mahmoud Gamal]

@Annoscia - Can you edit your question and show me the table structure and explain what are you trying to do in details, may be I can help you.

[Mahmoud Gamal]

@Annoscia, So you need to insert into the table LOCKEDACCOUNT the values from that you select from customers table? If so, the table LOCKEDACCOUNT contains id, status, datelocked, customerid, but these are not what you are selecting from the select clause?

[Annoscia]

im adding the values in myself so INSERT INTO LOCKEDACCOUNT (id, status, datelocked, customerid) VALUES ('blocked', getdate(), 2), i just need to know if the way im thinkin of doing it would work?

[Mahmoud Gamal]

@Annoscia - So you are adding the values for id, datelocked, customerid by yourself. But what about the value for the status field? You are selecting it with the case expression like in my answer, but based on the pm.duedate right? If so, do you select only one value to insert only one value and which value do you select? Or do you inserting mutliple values from customers into the table LOCKEDACCOUNT ? Because the two cases had different syntaxes.

[Annoscia]

yes i need to insert the values by myself but only if the duedate is > than 31 days. so instead of using the update, i need to say, select the columns as above, then check to see if duedate is > than 31 days, if it is then insert the values in to the lockedAccount table, if not then do nothing.

[Mahmoud Gamal]

@Annoscia OK, Fine. But the customers table contains a lot of values in duedate column, which value of these values do you want to compare it in the case expression. Do you want to compare the latest duedate or what?

[Annoscia]

hi mahmoud i think i have miss understood a few things, im sticking with the update case statement but many thanks for all of your help

[Mahmoud Gamal]

@Annoscia, OK, but if something goes wrong don't hesitate to ask any time. Or you can post a new question explaining the new issue.