Results 1 to 2 of 2

Thread: Follow-Up Query

  1. #1
    Join Date
    Apr 2016
    Posts
    1

    Unanswered: Follow-Up Query

    Hi Friends,

    I want to create a two tables in Sql
    Followup Master
    Followup Detail

    In Followup Master table i need to enter data in columns named EnqNo,Followup No,Followup Date,Followup Remarks
    same columns in Followup Detail table also but the difference is that in master table i need updated records to replace the old ones while in detail all the details should be there with the timestamp.

    Eg EnqNo Followup No Followup Date Followup Remarks
    110 F1 20/04/16 ABC
    111 F1 20/4/16 XYZ
    111 F2 21/04/16 XYZ
    112 F1 21/04/16 ABC
    112 F2 22/04/16 XYZ
    112 F3 23/04/16 XYZ


    So in case of 111 F2 is the latest one so it should be updated in place of F1 but in detail table both records should sit with timestamp.


    Pls help.


    Regards
    Aparna

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    You don't need two tables.
    Code:
    CREATE TABLE dbo.followups (
       enquiry_number   INT  NOT NULL
     , followup_date    DATE NOT NULL
     , followup_number  INT
     , followup_remarks VARCHAR(500)
     , CONSTRAINT pk_followups PRIMARY KEY (enquiry_number, followup_date)
    );
    GO
    
    CREATE VIEW dbo.latest_followups
      AS
    WITH cte AS (
      SELECT enquiry_number
           , followup_date
           , followup_number
           , followup_remarks
           , Row_Number() OVER (PARTITION BY enquiry_number ORDER BY followup_date DESC) AS sequencer
      FROM   dbo.followups
    )
    SELECT enquiry_number
         , followup_date
         , followup_number
         , followup_remarks
    FROM   cte
    WHERE  sequencer = 1 -- Latest one
    ;
    If you happen to be on SQL Server 2016, you may wish to investigate Temporal Tables (https://msdn.microsoft.com/en-us/library/dn935015.aspx)
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •