how can i pull the surveys that were submitted at least five minutes before the end of the chat. Below is detail requirement. please urgent
Monday, March 04, 2013 9:20 PM
Can I pull the Survey(AnswerValue) that were submitted at least five minutes before the end of the chat#.
How can I calculate the surveys that where submitted before five minutes? below is the detail example
It is too urgent..................
Thanks In Advance
- Edited by SravsSql Monday, March 04, 2013 10:22 PM
Monday, March 04, 2013 9:41 PMModerator
I am not certain which column you are using to evaluate as when an answer was submitted, but the following query uses DATEDIFF to return only rows where the difference between the SurveyDateCreated and ChatCreateClosed columns are less than 5:
CREATE TABLE #Chat( ChatNum BIGINT, ChatCreatDate TIME, ChatClosedDate TIME, SurveyDateCreated TIME, QuestionLabel VARCHAR(450), AnswerValue VARCHAR(450)); GO INSERT #Chat VALUES(857490798, '12:02:11 AM','12:25:45 AM','12:15:11 AM','How would you rate the overall experience? 5-point scale where 5 is "Excellent" and 1 is "Very Poor."', 5), (857492096, '12:27:47 AM','12:28:33 AM','12:34:33 AM','Knowledgeable', 5), (857492096, '12:27:47 AM','12:28:33 AM','12:34:33 AM','Overall', 5), (857494526, '12:30:33 AM','12:36:32 AM','12:34:33 AM','Did we help resolve your issue?', 1), (857494526, '12:30:33 AM','12:36:32 AM','12:34:33 AM','Efficient', 5 ), (857494526, '12:30:33 AM','12:36:32 AM','12:34:33 AM','Friendly', 5), (857494526, '12:30:33 AM','12:36:32 AM','12:34:33 AM','Knowledgeable', 5), (857497770, '12:17:22 AM','12:48:49 AM','12:45:12 AM','Overall', 5), (857500172, '12:48:59 AM','12:56:04 AM','12:55:02 AM','Communicative', 2) SELECT * FROM #Chat WHERE DATEDIFF(MINUTE, SurveyDateCreated, ChatClosedDate) < 5; GO
David Dye My Blog
- Marked As Answer by SravsSql Monday, March 04, 2013 10:21 PM
Monday, March 04, 2013 11:16 PM
Where is the DDL? What is the name of the table? What are the keys and constriants? Why do you have that silly AM/PM on what might be a TIME column? Where is the DATE that should be with it? Why did you use # in a column name and mess up the rest of the dasta element names (hatred of ISO-11179 and other standards, perhaps?). Here is a guess with correct names and data types.CREATE TABLE Chat_Surveys
(chat_nbr CHAR (9) NOT NULL PRIMARY KEY,
chat_start_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
CHECK (chat_start_timestamp < chat_end_timestamp)
CHECK (chat_end_timestamp < survey_timestamp)
question_txt VARCHAR(200) NOT NULL,
answer_code CHAR(10) NOT NULL);
The nulls in the timestamps are for chats that are still active.
CREATE VIEW Fast_Chat_Surveys
WHERE DATEDIFF(MINUTE, survey_timestamp, chat_end_timestamp) <= 5;
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL