need help with T-sql

  • 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

     

    Attachments:
    You must be logged in to view attached files.
  • 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

  • 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 3 (of 3 total)

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