I'm storing a bunch of data in a view (converted MS Access queries to views). Now what I'm trying to do is write a stored procedure to pull data based on when the data was added. Here is the query that I'm running
Select
Name, PlanID, ApptDate, 1stAppt, rn,
from
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
From vClientInfo)
So this pulls all my data okay. The issue that I have to address is, the client actually comes in for the 1st appt and 2nd appt - Basically I need to pull the data for LATEST date, as well as EARLIEST, and include a '2ndAppt' in my query. So 1stAppt and 2ndAppt hold a string value that I will later use...The 2 values might differ from 1stAppt to 2ndAppt - and only AddedOn date will tell me if there was a different (earliest for 1stAppt, and latest for 2ndAppt).
Select
Name, PlanID, ApptDate, 1stAppt, 2ndAppt rn,
From
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
From vClientInfp)
So I did this and I changed this...
(Select *, row_number() over (partition by PlanID Order BY AddedonDate *ASC*)
However I'm not sure if this is pulling correct data as I'm working with a lot of data and hard to verify. So basically I need to do a union so that both my queries pull in the correct data. Originally I was getting 1920 records, so with a union i should be getting just as many too i assume, however, the number is double so 3840, why?
Select
Name, PlanID, ApptDate, 1stAppt, 2ndappt, rn,
From
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate desc) as rn
From vClientInfo
Union All
Select
Name, PlanID, ApptDate, 1stAppt, 2ndAppt, rn,
From
(Select
*,
row_number() over (partition by PlanID Order BY AddedonDate asc) as rn
From vClientInfo