Get the records that have been updated

Question

Get the records that have been updated

Navigation

#1 by (1 votes)

1

good day. I have the following case: I need to get the newest records from a series of records. Let me explain, I have a table of Appointments Histories for a doctor, in this table are stored the hours of the appointments "Original Appointment Time" (Schedule for which the appointment was scheduled), "Appointment Time updated" (Schedule that the appointment was scheduled after an update), "Update Time" (Time when the system update was registered) and a Status of it (Scheduled, Canceled, Attempted, Retained)

From the table I place, I need to obtain the records marked in red.

I also have a Customer table that has the CLIENT ID as a relation to the Historical.

This is the code I'm using:

WITH Consulta as
    (
    SELECT
      Cons.CitaID,
      Cons.InterfaceID,
      Cons.DrName,
      Cons.DrLastName,
      Cons.Aviso,
      Cons.CreationDate,
      Cons.ClientID,
      Cons.SucursalID,
      Cons.StatusID,
      Cons.AppointmentDate,
      ConsHist.UpdateDate
    FROM doc.DrCita as Cons
    INNER JOIN doc.HistDrCita as ConsHist
    ON Cons.InterfaceID = ConsHist.InterfaceID
    AND Cons.AppointmentDate = ConsHist.AppointmentDate ),
    CitasMedico as (
    SELECT   
      doc.Client.ClientName,
      doc.Client.ClientLastName,
      doc.Client.PhoneNumber,
      Consulta.InterfaceID,
      Consulta.DrName,
      Consulta.DrLastName,
      FORMAT( Consulta.AppointmentDate, 'yyyy-MM-ddTHH:mm:ss') AppointmentDate, 
      doc.HistAppointment.StatusID,
      Consulta.Aviso,
      Sucursal.UnidadID,
      Sucursal.Name,
      FORMAT( Consulta.CreationDate, 'yyyy-MM-ddTHH:mm:ss') CreationDate,
      doc.CitaStatus.StatusName,
      FORMAT( Consulta.UpdateDate, 'yyyy-MM-ddTHH:mm:ss') UpdateDate
    FROM Consulta
    INNER JOIN doc.Client
      ON Consulta.ClientID = doc.Client.ClientID
    INNER JOIN doc.Sucursal AS CitSucursal
      ON Consulta.SucursalID = CitSucursal.SucursalID
    LEFT OUTER JOIN doc.AppointmentStatus
      ON Consulta.StatusID = doc.CitaStatus.StatusID
      )

Select * FROM CitasMedico

sql-server sql-server-2014

asked by VLT 05.01.2018 в 05:05

source

1 answer

How do I put the form in parallel to the paragraphs having bootstrap and own styles? can not be applied to given types

score 1 · Accepted Answer

You might consider using the RANK function.

p>

For example:

CREATE TABLE TABLA (
    ID INT,
    FECHA TIME
)

INSERT INTO TABLA VALUES 
(22, '11:30'), (22, '12:30'), (22, '16:00')
,(23, '14:30'), (23, '17:30')
,(24, '13:50'), (24, '15:00'), (24, '16:00'), (24, '19:00')
,(25, NULL)

SELECT
    ID,
    FECHA
FROM (
    SELECT
        ID,
        FECHA,
        RANK() OVER(PARTITION BY ID ORDER BY FECHA DESC) RANK
    FROM TABLA    
) X
WHERE X.RANK = 1

DEMO

For your query:

WITH Consulta as
(
    SELECT
        Cons.CitaID,
        Cons.InterfaceID,
        Cons.DrName,
        Cons.DrLastName,
        Cons.Aviso,
        Cons.CreationDate,
        Cons.ClientID,
        Cons.SucursalID,
        Cons.StatusID,
        Cons.AppointmentDate,
        ConsHist.UpdateDate
    FROM doc.DrCita as Cons
    INNER JOIN doc.HistDrCita as ConsHist
        ON Cons.InterfaceID = ConsHist.InterfaceID
            AND Cons.AppointmentDate = ConsHist.AppointmentDate 
),
CitasMedico as (
    SELECT   
        doc.Client.ClientName,
        doc.Client.ClientLastName,
        doc.Client.PhoneNumber,
        Consulta.InterfaceID,
        Consulta.DrName,
        Consulta.DrLastName,
        FORMAT( Consulta.AppointmentDate, 'yyyy-MM-ddTHH:mm:ss') AppointmentDate, 
        doc.HistAppointment.StatusID,
        Consulta.Aviso,
        Sucursal.UnidadID,
        Sucursal.Name,
        FORMAT( Consulta.CreationDate, 'yyyy-MM-ddTHH:mm:ss') CreationDate,
        doc.CitaStatus.StatusName,
        FORMAT( Consulta.UpdateDate, 'yyyy-MM-ddTHH:mm:ss') UpdateDate,
        RANK() OVER(PARTITION BY doc.Client.ClientID ORDER BY Consulta.UpdateDate DESC) RANK
    FROM Consulta
    INNER JOIN doc.Client
        ON Consulta.ClientID = doc.Client.ClientID
    INNER JOIN doc.Sucursal AS CitSucursal
        ON Consulta.SucursalID = CitSucursal.SucursalID
    LEFT OUTER JOIN doc.AppointmentStatus
        ON Consulta.StatusID = doc.CitaStatus.StatusID
)
Select * 
FROM CitasMedico
WHERE RANK = 1

Reference: