May 14, 2013 at 3:29 am
Hi All,
I have a CASE statement that checks if the date column is NULL or NOT, if there is a data then ‘Yes’ in the ‘Enquired_Y_N’ column and if it is NULL then ‘NO’ in the ‘Enquired_Y_N’ column but the select statement below is assigning incorrect Yes and NO into the ‘Enquired_Y_N’ column.
SELECT
[Response]
, CASE
WHEN [p].[Response] = 13053 THEN 'Y'
WHEN [p].[Response] = 13052 THEN 'N'
WHEN [p].[ResponseDate] is not null THEN 'Y'
WHEN [p].[ResponseDate] is null THEN 'N'
END AS Enquired_Y_N
,CONSTITUENT_ID
,[ResponseDate]
FROM [dbo].[table1] p
Sample Data
Create table table1
(Response int
,CONSTITUENT_ID INT
,ResponseDate INT
,EventID Varchar (50))
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','1774051','NULL','LEG_BLA_2012');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','1774056','NULL','LEG_BRA_2013');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','1774081','NULL','LEG_UCK_2013');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','1774107','NULL','LEG_BRA_2013');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','1774114','NULL','LEG_EXET_2012');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13053','1039434','NULL','LB02');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13053','1039551','NULL','RSA');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13053','1041345','NULL','LB02');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','1829692','20130117','BB0002');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13053','1017871','NULL','PW03');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13053','1017871','NULL','KLT1');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13052','1774190','20121114','BB0001');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13052','1774190','20121113','BB0002');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('NULL','NULL','1774190','20121113','BB0003');
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES ('13052','1774190','20121113','BB0004');
Wanted Results below –
Create table Wanted_Results
(Response int
,CONSTITUENT_ID INT
,ResponseDate INT
,EventID Varchar (50))
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','N','1774051','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','N','1774056','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','N','1774081','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','N','1774107','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','N','1774114','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13053','N','1039434','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13053','N','1039551','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13053','N','1041345','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','Y','1829692','20130117');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13053','N','1017871','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13053','N','1017871','NULL');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13052','Y','1774190','20121114');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13052','Y','1774190','20121113');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('NULL','Y','1774190','20121113');
INSERT INTO Wanted_Results (Response,Enquired_Y_N,CONSTITUENT_ID,ResponseDate) VALUES ('13052','Y','1774190','20121113');
Any help will Appreciated!!
Thanks
May 14, 2013 at 6:38 am
In the sample data you have provided, you are actually attempting to insert a string into your column rather than a NULL value, which then fails because your Respons and CONSTITUENT_ID columns are actually integers.
The correct syntax would be:-
INSERT INTO table1 (Response,CONSTITUENT_ID,ResponseDate,EventID) VALUES (NULL,'1774051',NULL,'LEG_BLA_2012')
If the actual table you're trying to use this with uses character data, the insert won't fail, and also those values will not prove true against an IS NULL where clause.
In addition, from the result set you've provided, it looks as if you wish the ResponseDate column to be evaluated before the Reponse column. In a case statement, subsequent criteria are only evaluated if the first fails. Therefore, every row you have that contains the value 13053 in the Response column will return a 'Y' value, and every row that contains the value 13052 will return a 'N'; despite the fact that you want some rows back with 13052 to be populated with a 'Y' when the ResponseDate is NOT NULL.
If you purely want to return data based on the ResponseDate column then simply change the statement to:-
SELECT
[Response]
, CASE
WHEN [p].[ResponseDate] is not null THEN 'Y'
ELSE 'N'
END AS Enquired_Y_N
,CONSTITUENT_ID
,[ResponseDate]
FROM [dbo].[table1] p
However, I suspect that you may want to still do something with the Response column otherwise you wouldn't have it in your case statement at all!
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 14, 2013 at 6:46 am
Like Matthew before mentioned, the subsequent criteria is only checked when the previous is failed. You could combine the criteria with an OR statement
SELECT
[Response]
, CASE
WHEN [p].[Response] = 13053 OR [p].[ResponseDate] is not null THEN 'Y'
WHEN [p].[Response] = 13052 OR [p].[ResponseDate] is null THEN 'N'
END AS Enquired_Y_N
,CONSTITUENT_ID
,[ResponseDate]
FROM [dbo].[table1] p
May 14, 2013 at 7:35 am
The only problem with this is that it doesn't produce the required dataset as there are rows with the value 'N' for the Response value of 15053.
So either the desired dataset contains a mistake, or the outcome of the case statement isn't obvious from the original incorrect statement.
Without knowing the desired logic, it's tricky to know what to provide as an accurate answer.
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
May 14, 2013 at 8:22 am
Thank you both Matthew Darwin and HanShi you sql code helped me out. I simply add the 'AND' to the case statement to ResponseDate and Response columns.
Thank!!
May 14, 2013 at 8:30 am
With the values in your case statement provided with that dataset, that will simply produce 'N' for every row!
Follow me on twitter @EvoDBACheck out my blog Natural Selection DBA[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply