-1

I have a UNION ALL query that I'm getting incorrect results for. I'm supposed to get about 1100 hundred records. Please see query...

select 
    Pop, planID, PopFull, ApptDate, intake1,FollowUP2,FollowupCode, rn, '5133'
from 
    (Select *, row_number() over (partition by planID order BY AddedDate asc) as rn from Vinfo) t
where 
    rn = 1 and ApptDate >='12/1/2014' and ApptDate <='12/31/2015'

Union All

select  
    Pop, planID, PopFull, ApptDate, intake1, FollowUP2, FollowupCode, rn, '5133' 
from 
    (Select *,row_number() over (partition by PlanID order BY AddedDate  DESC) as rn from Vinfo) t
where 
    rn = 1 and ApptDate >='12/1/2014' and ApptDate <='12/31/2015'

So what I'm trying to do is SELECT all the info and in the first SELECT statement, I'm trying to get a VALUE for INTAKE when ADDEDDATE is the lowest (earliest).

I'm doing the UNION all because in the second SELECT statement, I'm trying to get a value for FOLLOWup when ADDEDDATE is the oldest (most recent).

The values for INTAKE AND FOLLOWUP might be different, but doesnt have to be. I'm trying to track the difference.

However when I run this query, I get double the records. Is there a way for me to run this query so that I can get the correct number of records (1100) and get value for INTAKE and if there's a change in value for FOLLOWUP I will see that in the same row?

Instead of seeing double of everything. Basically the way it's running now is if PLANID is 1023 and Intake for EARLIEST date is B then it will show me B for FollowUP as well. in Addition if, FollowUP for LATEST date is C then it will show me C for Intake and FollowUP in the row below it.

EDIT:

select Pop,planID,PopFull,ApptDate, intake1,FollowUP2,FollowupCode, '5133'
from (select *,
row_number() over (partition by planID order BY AddedDate asc) as rn_first,
row_number() over (partition by PlanID order BY AddedDate  DESC) as rn_last
from VInfo
) t
where t.rn_first = 1 or rn_last = 1
and ApptDate >='12/1/2014' and ApptDate <='12/31/2015'

Ran this but doesn't give right results

FatBoySlim7
  • 232
  • 1
  • 2
  • 13
  • If your complaint about num of records, problem is not in `UNION ALL`. Union all simply returns 2+ resultsets put together. If your first query returns n-records and second m-records - look if your n and m are correct. `UNION` [w/o ALL] will not return duplicates – T.S. Mar 17 '16 at 18:21
  • if i run the first query (seperately), it gives me 1100 records, and if i run second query i get 1100. But when I run them with Union ALL it gives me 2400. When I run without 'ALL' i get like 2150 records because it will eliminate duplicates, but a lot of them are different. – FatBoySlim7 Mar 17 '16 at 18:22
  • Do you care about the actual row number, or are you using that function simply to find the intake/followup with the earliest/latest added date? – Aaron Dietz Mar 17 '16 at 18:45
  • I want to have 1100 records with a VALUE for INTAKE and Value for FOLLOWUP. But If there are not the same, I'd like to see the two different values in the same row. Say for earliest date INTAKE value is B and for latest FOLLOWUP value - the value is C - I'd like to see those in the same row. B for intake and C for Followup. – FatBoySlim7 Mar 17 '16 at 18:51
  • with the paranthesis its showing me 2 instaces of each PLAN ID. So because values are different for Intake at earliest date and Followup at latest date - i'm getting two rows for each planID – FatBoySlim7 Mar 18 '16 at 15:02

1 Answers1

1

You will obviously get duplicates for those rows which have only one instance of Pop, planID, PopFull, ApptDate, intake1, FollowUP2, FollowupCode combination - i.e. both ASC and DESC return one row and rn=1 for ASC and DESC sorting.

UNION ALL does allow duplicates from top and bottom rowsets. You may try UNION instead.

Also, as suggested before, you may count both ROW_NUMBERS in single select:

select *
from
(
  select v.*,
    row_number() over (partition by PlanID order BY AddedDate asc) as rn_first,
    row_number() over (partition by PlanID order BY AddedDate  DESC) as rn_last
  from Vinfo v
) t
where t.rn_first = 1 or rn_last = 1
Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39