CODEWITHSUNDEEP
sqlsql-servercase
300
300

Reputation: 1041

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.

Upvotes: 1

Views: 1840

Answers (2)

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

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';

Upvotes: 1

granadaCoder
granadaCoder

Reputation: 27894

This?

 select
column1,
DATEPART(n, column_ts) as MyDatePart
,
[Column1OrBust] =
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 )  then column1
    else 0
end
, FirstName, LastName
from TestingTable

Upvotes: 0

Related Questions