300 300 - 2 months ago 8
SQL Question

How to use multiple DATEPART functions in CASE statement?

Table: I have a database table TestingTable in SQL Server 2012. This is how table looks like:

column_ts column1 FirstName LastName
2016-09-30 00:04:00.000 5 Martha Stuart
2016-09-30 00:24:00.000 51 Tom Riddle
2016-09-30 00:29:00.000 32 Harry Potter
2016-09-30 00:44:00.000 128 Anderson Smith
2016-09-30 00:48:00.000 23 Lisa Young
2016-09-30 01:04:00.000 88 Mad Max
2016-09-30 01:59:00.000 46 Sam King


DDL: You can create this table using:

CREATE TABLE TestingTable
(
column_ts datetime,
column1 int,
FirstName varchar(255),
LastName varchar(255)
);

INSERT INTO TestingTable
VALUES ('2016-09-30 00:04:00.000',5,'Martha','Stuart'),
('2016-09-30 00:24:00.000',51,'Tom','Riddle'),
('2016-09-30 00:29:00.000',32,'Harry','Potter'),
('2016-09-30 00:44:00.000',128,'Anderson','Smith'),
('2016-09-30 00:48:00.000',23,'Lisa','Young'),
('2016-09-30 01:04:00.000',88,'Mad','Max'),
('2016-09-30 01:04:00.000',46,'Sam','King');


I have a query that selects column1 only if column_ts has a minute value between 26-30 OR 56-00.

select ISNULL(column1,0) from TestingTable
where ((DATEPART(n, column_ts) > 25) AND DATEPART(n, column_ts) <= 30)
OR (((DATEPART(n, column_ts) > 55) AND DATEPART(n, column_ts) <= 59) OR DATEPART(n, column_ts) = 0);


Result will be:

(No column name)
32
46


My Goal: Now I want to use the above query as a sub query. When FirstName(will have unique values) is given, select the row having that name but select column1's value only if column_ts has a minute value between 26-30 OR 56-00. Else select column1 as zero.

What I tried: I think I'll have to use CASE but I read that we can't/should not use parenthesis in CASE. I tried something but getting syntax error for it:

select column_ts, column1 =
CASE column_ts
WHEN (((DATEPART(n, column_ts) > 25) AND DATEPART(n, column_ts) <= 30)
OR (((DATEPART(n, column_ts) > 55) AND DATEPART(n, column_ts) <= 59) OR DATEPART(n, column_ts) = 0)) THEN column_ts
ELSE 0
END, FirstName, LastName
from TestingTable
where FirstName = 'Tom';


Error: Above query gives syntax error:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '>'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'DATEPART'.


Please suggest me how I can correct the above query or write another query to achieve my goal.

Answer

Try with the below query.

   SELECT  column_ts,
   column1 = 
   CASE 
    WHEN (((DATEPART(n, column_ts) > 25) AND DATEPART(n, column_ts) <= 30) 
        OR (((DATEPART(n, column_ts) > 55) AND DATEPART(n, column_ts) <= 59) OR DATEPART(n, column_ts) = 0)) THEN column1 
    ELSE 0
END, FirstName, LastName 
FROM TestingTable
WHERE FirstName = 'Tom';