Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
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

Answered 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
     
     
    Sign In to Vote
    0

    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
    •  
     

All Replies

  • Monday, March 04, 2013 9:41 PM
    Moderator
     
     Answered Has Code
    Sign In to Vote
    0

    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
     
     
    Sign In to Vote
    1

    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,
     chat_end_timestamp DATETIME2(0),
     CHECK (chat_start_timestamp < chat_end_timestamp)
     survey_timestamp DATETIME2(0),
     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 
    AS
    SELECT *
     FROM 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