Select Statement Help

Question

Post reply

Select Statement Help

reggiete

Old Hand

Points: 316

August 18, 2017 at 2:00 pm

#402430

Hello All,

I am needing help with creating a SQL query. I'm sure I am just over thinking it.
Below is a table I have with data. I am wanting to group the data below by ID and pull the date from the 1st attempt and 2nd attempt.

CustomerApplicationId	ApplicantName	CreateDateTime	MonthYear	CallResult	Myrow
1207512	Jane	17-Aug-17	17-Aug-17	Ignore	1
1207512	Jane	17-Aug-17	17-Aug-17	1st Attempt Failed	2
1207512	Jane	18-Aug-17	18-Aug-17	2nd Attempt Failed	3

Final View I am trying to achieve from the data above

CustomerApplicationId	ApplicantName	FirstCallDate	SecondCallDate	Final Status
1207512	Jane Doe	8/17/2017	8/18/2017	2nd Attempt Failed

First Call date = 1st attempt failed CreateDateTime
SecondCallDate = 2nd attempt failed createdatetime
Final Status = Max(createDatetime) Call result status

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jason A. Long SSC-Insane Points: 23790 More actions · Answer 1

Something along these lines should do the trick...
SELECT cl.CustomerApplicationId, ApplicantName = MIN(cl.ApplicantName), FirstCallDate = MIN(CASE WHEN cl.CallResult LIKE '1st%' THEN cl.CreatedDateTime END), SecondCallDate = MIN(CASE WHEN cl.CallResult LIKE '2nd%' THEN cl.CreatedDateTime END), FinalStatus = CAST(SUBSTRING(MAX(CAST(cl.CreatedDateTime AS BINARY(4)) + CAST(cl.CallResult AS VARBINARY(1000))), 5, 1000) AS VARCHAR(1000)) FROM dbo.CallLog cl GROUP BY cl.CustomerApplicationId;

reggiete Old Hand Points: 316 More actions · Answer 2

reggiete

Old Hand

Points: 316

August 18, 2017 at 7:10 pm

#1955893

Thank you@

jcelko212 32090 SSCrazy Eights Points: 9304 More actions · Answer 3

reggiete - Friday, August 18, 2017 2:00 PM
>> Below is a table I have with data. I am wanting to group the data below by ID and pull the date from the 1st attempt and 2nd attempt. <<
In the future, would you mind following forum rules and post DDL and clear specs along with sample data. Now we have to transcribe your pictures into DDL in order to help you. It would also help if you knew the correct format for dates; ANSI/ISO standard SQL is based on the ISO 8601 standard and only allows "yyyy-mm-dd" for displays. Did you know that a table should always have a key?
I'm also curious as to why success is not possible with your system. You seem to either fail or ignore your calls; that doesn't make sense to make to me. You also don't seem to know the difference between the value in an attribute. Since SQL is based on logical modeling, rather than physical sequences, your "my row" just looks plain wrong. It sounds like you describing the physical layout on a piece of paper for log sheets. That's not how logical model works. Based on your pictures, I would've thought it would look more like this:
CREATE TABLE Call_Log -- your table did not even have a name!
(customer_application_id CHAR(7) NOT NULL,
applicant_name VARCHAR(20) NOT NULL,
call_timestamp DATETIME2(0) DEFAULT CURRENT_TIMESTAMP NOT NULL,
call _status VARCHAR(15) NOT NULL
CHECK (call _status IN ('ignore', 'failed', .., 'success'), -- is that one possible ?
PRIMARY KEY (customer_application_id, applicant_name, call_timestamp)
);
INSERT INTO Call_Log
VALUES
('1207512', 'Jane Doe', '2017-08-17 00:00:00' , 'ignore'),
('1207512', 'Jane Doe', '2017-08-17 00:01:00' , 'fail'),
('1207512', 'Jane Doe', '2017-08-17 00:02:00' , 'fail');
>> Final View I am trying to achieve from the data above <<
Again, we have to guess. If you just want the final status, then something like this will work:
WITH Last_Call
AS
(SELECT customer_application_id, applicant_name, call_timestamp, call _status,
MAX(call_timestamp) OVER (PARTITION BY customer_application_id, applicant_name)
AS final
FROM Call_Log
SELECT *
FROM Last_Call
WHERE final_call_timestamp = call_timestamp;
When you can post clear specs, we can give better answers.

Please post DDL and follow ANSI/ISO standards when asking for help.