January 26, 2021 at 4:51 pm
Hello my dears,
Please help with the query to retrieve the data as my mamager want in this format :
ID,,cm_id, Date, .NextDate, status , DateDiff("n",Date,NextDate) AS DateDiff_mins, next_status where next_status is linked with next_date and also next_status equal with every changed in Status from pending to add tatachemnt .(for example).
Bst Wishes,
Hadrian
January 26, 2021 at 5:13 pm
Look at the LEAD() function--LAG() would also work.
When posting sample data, you should use the {;} insert/edit code sample button to insert the script directly in your question instead of posting an attachment. Your script should also include an INSERT statement for the sample data, a script to create a (temporary) table with the expected results and an INSERT statement for the expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 4, 2021 at 5:07 pm
There are no such creatures as a generic "date", generic "id", or generic "status"; I also seriously doubt that the status is ever 150 characters of Chinese. You define something as a datetime but you call it the date, etc. and you don't seem to know that, by definition, a table must have a key. I hope you know that the proprietary non-relational IDENTITY is a table property, and therefore by definition can never be a key. It's kind of like identifying an automobile in a parking garage by whatever parking space number it happened to fall in.
CREATE TABLE Tickets
(ticket_nbr CHAR(5) NOT NULL PRIMARY KEY
cm_id VARCHAR(10) NOT NULL,
ticket_date DATE NOT NULL,
foobar_status VARCHAR(50) NOT NULL
CHECK (foobar_status
IN ('Assign', 'add CC Recipient', 'Add Attachment', 'Add CC Recipient',
'Add Reminder', 'Evaluate and Validate');
Instead of posting loose text, why don't you post insertion statements? That would save us the trouble of doing your job for you.
INSERT INTO Tickets
VALUES
('00001', '3807', '2020-07-09', 'Assign'),
('00002', '3807', '2020-07-09', 'add CC Recipient'),
('00003', '3807', '2020-07-09', 'Add Attachment'),
('00004', '3807', '2020-07-09', 'Add Attachment'),
('00005', '3807', '2020-07-09', 'Add CC Recipient'),
('00006', '3807', '2020-07-09', 'Add CC Recipient'),
('00007', '3807', '2020-08-09', 'Add Reminder'),
('00008', '3807', '2020-08-09', 'Evaluate and Validate'),
('00009', '3807', '2020-08-09', 'Accept');
You also don't seem to understand that by definition a table has no ordering. There are no columns called "next_date". "next_vague_status", etc. in your vague narrative. The concept of a "link" does not exist in RDBMS; that refers to a one-way pointer chain in the old network databases.
I would suggest that you post the actual results you want to see, so we can figure out what you mean and turn it from your narrative into actual SQL code.
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply