Looping Through Data
-
Hello, I'm new to SQL.This is what I'm trying to do.
If Caller and Call Type is same within the table and date difference within those records are > 5 days then call is considered closed "Same Day"
If Caller and Call Type is same within the table and date difference within those records are < 5 days then call is considered not closed "Not Same Day"I attached the excel. Appreciate your help.
- irehman - Tuesday, December 18, 2018 7:52 AM
Hello, I'm new to SQL.This is what I'm trying to do.
If Caller and Call Type is same within the table and date difference within those records are > 5 days then call is considered closed "Same Day"
If Caller and Call Type is same within the table and date difference within those records are < 5 days then call is considered not closed "Not Same Day"I attached the excel. Appreciate your help.
Those requirements seem totally backwards. And it's a total stretch of the imagination for me to envision up to 5 days being considered to be the "same day".
This seems like homework. I don't have a problem helping someone learn something new but you've not even shown that you've tried and you're the one going for the grade. 😉
Also, you'll find that a lot of people won't open a spreadsheet. Please see the first link under "Helpful Links" in my signature line below for one method to post "readily consumable data" to help us help you.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
p.s. I will give you a hint, though. If you have SQL Server 2012 or above, check out the LEAD and LAG keywords.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@Jeff Moden
There is a business logic behind 5 days. I made up the data, because I did not want to share actual data set. - irehman - Tuesday, December 18, 2018 8:22 AM
The business logic appears to be backwards, though. The requirements in your original post are ...
If Caller and Call Type is same within the table and date difference within those records are > 5 days then call is considered closed "Same Day"
If Caller and Call Type is same within the table and date difference within those records are < 5 days then call is considered not closed "Not Same Day"With respect to the underlined areas in your requirements, are you sure you want more than 5 days to be registers as "Same Day" and less than 5 days be "Not Same Day?
You're also missing what you want done right at 5 days.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - irehman - Tuesday, December 18, 2018 10:45 AM
You're welcome but you've not explained what you want to do when the difference is exactly 5 days. Your requirements of <5 and >5 don't cover =5.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Also, can you post the data in the readily consumable format that I pointed out in one of my previous posts above? I really don't have the time to mess around with someone's spreadsheet.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@Jeff Moden
Should be <=5 and >=5Create table test
(
Caller varchar(20) null,
DT date null,
Call Type varchar(100) null
)Insert into Test ('John','12/1/2018','Connection');
Insert into Test ('John','12/10/2018','Connection');
Insert into Test ('Jerry','12/2/2018','Printer');
Insert into Test ('Jerry','12/3/2018','Printer');
Insert into Test ('Jerry','12/3/2018','Monitor');
Insert into Test ('Sam','12/15/2018','Keyboard');
Insert into Test ('Sam','12/17/2018','Printer');
Insert into Test ('Smith','12/10/2018','Misc');
Insert into Test ('Roy','12/9/2018','Mouse');
Insert into Test ('Roy','12/9/2018','Mouse');Thanks
- irehman - Tuesday, December 18, 2018 12:58 PM
It can't be both <= 5 and >= 5 because that would require two opposing statuses if the difference is equal to 5. Only one or the other can have the = sign.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
@Jeff Moden
Yes, I miss-typed <= 5 and > 5 - irehman - Tuesday, December 18, 2018 7:58 PM
K thanks.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - irehman - Tuesday, December 18, 2018 7:52 AM
Hello, I'm new to SQL.This is what I'm trying to do.
If Caller and Call Type is same within the table and date difference within those records are > 5 days then call is considered closed "Same Day"
If Caller and Call Type is same within the table and date difference within those records are < 5 days then call is considered not closed "Not Same Day"I attached the excel. Appreciate your help.
>> Hello, I'm new to SQL. <<
At the front of virtually any SQL forum you will find some netiquette rules. Pictures and spreadsheets should never be posted; we can't compile them, figure out datatypes, figure out keys or anything else that we need. This is why for the past 30 years, we have asked for DDL.
Nobody will open attachments from a stranger --- I hope you don't do that!
You also apparently don't know that the only data display format allowed in ANSI-ISO standard SQL is yyyy-mm-dd, and not your ambiguous local dialect.Rows are also not records. In order to have a key, rows are unique because they contain a key. But you got duplicate rows! That might be fine for spreadsheet, but not for SQL
By definition, not by option, a table must have a key. Your sample data is not a table! Can you really, really do need to get a book on basic SQL and RDBMS. You are missing fundamentals
CREATE TABLE Call_Log
(caller_name VARCHAR(20) NOT NULL,
call_timestamp DATETIME2(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (caller_name, call_timestamp),
call_type CHAR(10) NOT NULL
CHECK (call_type IN ('Connection', 'Printer', 'Monitor', 'Keyboard', 'Misc', 'Mouse'))
);Please notice how using a timestamp will keep each row distinct. Also, look at the default clause, to guarantee that it's always in the row. Look at the check constraint on the call type codes. One of the things you need to learn about SQL is that most of the work is done in the DDL! Once you've got the schema, it will pretty much take care of itself. But only if it is properly designed.
Here is a rewrite of your sample data:
INSERT INTO Call_Log
VALUES
('John', '2018-12-01 00:00:00', 'Connection'),
('John', '2018-12-10 00:00:00', 'Connection'),
('Jerry', '2018-12-02 00:00:00', 'Printer'),
('Jerry', '2018-12-03 00:11:02', 'Printer'),
('Jerry', '2018-12-03 00:00:00', 'Monitor'),
('Sam', '2018-12-15 00:00:00', 'Keyboard'),
('Sam', '2018-12-17 00:00:00', 'Printer'),
('Smith', '2018-12-10 00:00:00', 'Misc'),
('Roy', '2018-12-09 00:00:00', 'Mouse');
('Roy', '2018-12-09 00:00:10', 'Mouse');>> If Caller and Call Type is same within the table and date difference within those rows are > 5 days then call is considered closed "Same Day" <<
CAST (call_timestamp AS DATE) will pull the date out of the timestamp.
LEAD() and LAG() will give you the next or prior row in the table, ordered by timestamp..
DATEDIFF() will do the temporal math. Now give it a try and post what you got; were not doing your homework for you.Please post DDL and follow ANSI/ISO standards when asking for help.
-
@Joe Celko
Joe it's not a homework, do you want entire 2M+ rows of data? I was trying to create the solution in Power BI but DAX is not as flexible as SQL. -
@Joe Celko
I tried LEAD and LAG, but both giving me the same date. See the image, I highlighted one of the record set.
SELECT
CONCAT(TPA_ID, EMPE_KEY) CONCAT_TPA_EMPE
,PARTICIPANT_INTERACTION_DATE_ENTERED
,PARTICIPANT_INTERACTION_TYPE as TYPE
,CAST (PARTICIPANT_INTERACTION_DATE_ENTERED AS DATE) CASTDATE
,LEAD(PARTICIPANT_INTERACTION_DATE_ENTERED, 1,0) OVER (ORDER BY PARTICIPANT_INTERACTION_DATE_ENTERED) AS LeadDate
,LAG(PARTICIPANT_INTERACTION_DATE_ENTERED, 1,0) OVER (ORDER BY PARTICIPANT_INTERACTION_DATE_ENTERED) AS LagDate
FROM TABLENAME
Group by CONCAT(TPA_ID, EMPE_KEY), PARTICIPANT_INTERACTION_TYPE, PARTICIPANT_INTERACTION_DATE_ENTERED
Order by 1,2,3
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply