CODEWITHSUNDEEP
sqlsql-server
VAAA
VAAA

Reputation: 15039

SQL conditional join or where statement

I have the following SQL Query

UPDATE ea
    SET ea.isholiday = 1
    FROM employee_attendance ea
    JOIN setup_holiday h ON h.day = DATEPART(dd,ea.timestamp) 
    AND h.month = DATEPART(mm,ea.timestamp)
WHERE ea.isactive = 1

I need to add other filter that is:

AND h.year = DATEPART(yyyy, ea.timestamp)

But, it not that easy, because I don't want to apply this filter if year = 0

Any clue?

Upvotes: 0

Views: 59

Answers (3)

ErikE
ErikE

Reputation: 50201

For SQL Server 2005 and up:

UPDATE ea
SET
   ea.isholiday = 1
FROM
   dbo.setup_holiday h
   CROSS APPLY (
      SELECT 0, 9999 WHERE h.year = 0
      UNION ALL SELECT h.year, h.year WHERE h.year <> 0
   ) Y (StartYear, EndYear)
   INNER JOIN dbo.employee_attendance ea
      ON h.day = DATEPART(dd, ea.timestamp) 
      AND h.month = DATEPART(mm, ea.timestamp)
      AND h.year BETWEEN Y.StartYear AND Y.EndYear
WHERE
   ea.isactive = 1
;

Notes:

  • Please use the schema prefix (such as dbo.) on tables and views. This prevents the engine from trying to find the object in more than one schema when the current user is not a database admin. I think that not doing this may also prevent using WITH SCHEMABINDING on your views, functions, and stored procedures, though I'm not sure at this moment.
  • I prefer this sort of table-type split logic rather than complex CASE and other expressions. To me, the intent is much more clear, and it's easier to reason about the results. Once you get how CROSS APPLY works, I think using it makes things much more elegant.
  • In any case, putting the h.year predicate in the ON clause is better than in the WHERE clause, since it's a condition for the join, and is not really about filtering.
  • If you were to design this system from scratch, I would suggest actually placing the StartYear and EndYear columns into the setup_holiday table in place of year, just so that you could follow this exact pattern without the CROSS APPLY. This also allows you to stop using the "magic value" (sometimes called a "sentinel value") of 0 for the year--it just works, and supports year ranges, besides--a fragility of the current system that only supports specifying each and every year or no year at all.
  • It's best practice to end your queries with a semicolon ; to future-proof your code and eliminate silly usage of semicolons before CTEs. Although the semicolon is not required for most statements in SQL Server 2014 and earlier, it will be required in a future version.
  • While it's entirely a matter of personal preference, how can you possibly read queries efficiently when you put at the same indentation level items of different importance (lining up FROM with JOIN and AND which are each of different significance)? And how can you tolerate having some of your ON conditions at the end of the JOIN line, and some on the next line? In my opinion, you're hurting your ultimate maximum query-scanning speed by choosing a format that doesn't leverage indentation properly and put the same things together, with important signal words at the beginning of lines.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269623

Just add the condition using or:

UPDATE ea
    SET ea.isholiday = 1
    FROM employee_attendance ea JOIN
         setup_holiday h
         ON h.day = DATEPART(dd,ea.timestamp) AND
            h.month = DATEPART(mm,ea.timestamp) AND
            (h.year = DATEPART(yyyy, ea.timestamp) or h.year = 0)
WHERE ea.isactive = 1

Upvotes: 1

Hogan
Hogan

Reputation: 70523

You use a case statement and a trick -- when you have the predicate you want to ignore you just put the right hand side (which will always be true) otherwise your predicate. Like this:

UPDATE ea
    SET ea.isholiday = 1
FROM employee_attendance ea
JOIN setup_holiday h ON h.day = DATEPART(dd,ea.timestamp) 
  AND h.month = DATEPART(mm,ea.timestamp)
WHERE ea.isactive = 1
  AND (CASE WHEN h.year = 0 THEN DATEPART(yyyy, ea.timestamp)
            ELSE h.year END) = DATEPART(yyyy, ea.timestamp)

Upvotes: 0

Related Questions