locked
Dataset to excludes related items RRS feed

  • Question

  • We have a dataset/table of customer APPOINTMENTS.   Users can tag a future appointment as "cancelled" and then either create (or not create) another future appointments for a customer (so customers have can have a number of appointment rows).  I want my dataset to only show 'cancelled' appointments that do not have any related future appointments.

    I can use ....   SELECT <columns> FROM  APPOINTMENTS WHERE  APPOINTMENT_STATUS ='cancelled'   ..... but this does NOT exclude customers who have future appointments.

    Can this be done in 'query designer'?.... I'm not sure how.  Any advice would be helpful.  Thanks.

    Wednesday, November 7, 2012 8:59 PM

Answers

  • You should be able to do it in a TSQL Query:

    WITH X AS
    (SELECT
      UserID
      ,UserName
      ,AppointmentDate
      ,AppointmentStatus
      ,Row_Number() OVER(PARTITION BY UserID ORDER BY AppointmentDate DESC) AS RN
    FROM  Appointments
    WHERE  FieldX = Value)
    
    SELECT * 
    FROM X
    WHERE 
      RN = 1 
      AND AppointmentStatus = 'Cancelled'
    You add a row number to the original query partitioning by the user's unique identifier and sorting in reverse order by appointment date. This means that the most recent appointment will have RN = 1. Wrap this in a with providing an alias (I used X) then do a select of all of the fields from the original query that you want and set the WHERE clause to get the last appointment (RN=1) if its status is 'Cancelled'.


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    • Proposed as answer by Charlie Liao Friday, November 9, 2012 3:25 AM
    • Marked as answer by Charlie Liao Thursday, November 15, 2012 9:17 AM
    Wednesday, November 7, 2012 10:47 PM
  • You will need to edit as text. you can then type the TSQL directly.


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    • Marked as answer by stoneb1965 Thursday, November 22, 2012 12:12 PM
    Monday, November 19, 2012 3:46 PM

All replies

  • You should be able to do it in a TSQL Query:

    WITH X AS
    (SELECT
      UserID
      ,UserName
      ,AppointmentDate
      ,AppointmentStatus
      ,Row_Number() OVER(PARTITION BY UserID ORDER BY AppointmentDate DESC) AS RN
    FROM  Appointments
    WHERE  FieldX = Value)
    
    SELECT * 
    FROM X
    WHERE 
      RN = 1 
      AND AppointmentStatus = 'Cancelled'
    You add a row number to the original query partitioning by the user's unique identifier and sorting in reverse order by appointment date. This means that the most recent appointment will have RN = 1. Wrap this in a with providing an alias (I used X) then do a select of all of the fields from the original query that you want and set the WHERE clause to get the last appointment (RN=1) if its status is 'Cancelled'.


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    • Proposed as answer by Charlie Liao Friday, November 9, 2012 3:25 AM
    • Marked as answer by Charlie Liao Thursday, November 15, 2012 9:17 AM
    Wednesday, November 7, 2012 10:47 PM
  • Thanks Tim....From what I can see Over() command is not supported in Query Designer.  Do you know how I can get around that?  Thanks.

    Sunday, November 18, 2012 10:13 AM
  • You will need to edit as text. you can then type the TSQL directly.


    Please Mark posts as answers or helpful so that others can more easily find the answers they seek.

    • Marked as answer by stoneb1965 Thursday, November 22, 2012 12:12 PM
    Monday, November 19, 2012 3:46 PM