CODEWITHSUNDEEP
t-sqlazure-sql-database
superblowncolon
superblowncolon

Reputation: 185

SQL Server Update With IF Statement

I have an Azure SQL database that includes an upload date and birth date. I've added a column called "AgeFlag" which I'd like to equal "Over 40" if upload date - birth date is >= 40, and "Under 40" otherwise. I think this means I need an update statement with an IF statement, but I am uncertain how to proceed:

UPDATE datasetitems SET ageflag = SELECT IF((datediff(year, d.timestamp, di.birthdate)>40,'Over 40','Under 40') FROM datasetitems di JOIN datasets d ON di.datasetid = d.datasetid);

Maybe this would be easier with a temporary table to do the age calculation?

Upvotes: 0

Views: 55

Answers (3)

Nitish Kumar
Nitish Kumar

Reputation: 1

Use this

UPDATE datasetitems 
    SET ageflag = case when abs(datediff(year, d.timestamp, di.birthdate))>40 then 'Over 40' else'Under 40' end
        FROM datasets d join datasetitems di ON di.datasetid = d.datasetid);

Upvotes: 0

Dan Guzman
Dan Guzman

Reputation: 46233

A CASE expression rather than IF will do the job of your pseudo code. Here's an example, reversing the dates as @Seekwell74 noticed:

UPDATE datasetitems 
SET ageflag = CASE WHEN DATEDIFF(YEAR, di.birthdate, d.timestamp) > 40 THEN 'Over 40' ELSE 'Under 40' END 
FROM datasetitems di 
JOIN datasets d ON di.datasetid = d.datasetid;

However, your age calculation is wrong. DATEDIFF counts the number of year boundaries between the dates, not the interval in years. For example, DATEDIFF will result in 1 year between dates 2017-12-31 and 2018-01-01.

Below is another method to calculate a person's age:

UPDATE datasetitems 
SET ageflag = CASE WHEN (CAST(CONVERT(char(8), di.birthdate, 112) AS int) - CAST(CONVERT(char(8), d.timestamp, 112) AS int)) / 10000 > 40 THEN 'Over 40' ELSE 'Under 40' END 
FROM datasetitems di 
JOIN datasets d ON di.datasetid = d.datasetid;

Upvotes: 2

Seekwell74
Seekwell74

Reputation: 11

Perhaps use as case statement:

UPDATE di 
SET ageflag = 
case 
    when datediff(year, di.birthdate, d.timestamp) > 40 
    then 'Over 40' 
    else 'Under 40' 
end
FROM datasetitems di JOIN datasets d 
ON di.datasetid = d.datasetid);

Also, I think your date parameters for datediff are reversed?

Upvotes: 0

Related Questions