CODEWITHSUNDEEP
sql-serverstored-proceduresnull
FatBoySlim7
FatBoySlim7

Reputation: 232

Stored procedure NULL values SQL Server

I have a stored procedure that selects data out of a view and inserts it into a tempTable based on criteria. What I'm trying to do is make sure that if there are NULL values for added date, they get excluded.

Insert into tempIntake(Pop, PlanID, PopFull, ApptDate, 1stAppt, Followup, Rn, UserID)
select Pop, PlanID, PopFull, InterviewDate, 1stAppt, Followup, rn, @UserID 
from 
   (Select *, row_number() over (partition by PlanID order BY AddedDate ASC) as rn 
     from VInfo) t
where rn = 1 
and interviewdate >= @fromDate 
and interviewDate <= @toDate

How would I do that?

I'm basically trying to filter by the earlier ADDEDDATE but exclude the NULL dates that might appear.

I have this SP and I also have another stored Procedure that does ADDEDDATE DESC. But I don't know if this likes the fact that I only have one date. For teh ASC partition, it pulls a null value, and for DESC it pulls an actual date (there is only one date). I want to be able to use that date in both stored Procedures (unless there are multiple dates - that's when I'd like it do grab the earliest date and latest date)

Upvotes: 0

Views: 44

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

Unless I'm missing something, a simple where clause in the derived table should do the trick:

Insert into tempIntake(Pop, PlanID, PopFull, ApptDate, 1stAppt, Followup, Rn, UserID)
select Pop,PlanID, PopFull,InterviewDate,1stAppt,Followup, rn, @UserID 
from (
    Select *,row_number() over (partition by PlanID order BY AddedDate ASC) as rn 
    from VInfo
    where AddedDate is not null 
) t
where rn = 1 
and interviewdate >=@fromDate 
and interviewDate <=@toDate

Update

Following our conversation in the comments i think something like this is what you are looking for:

Insert into tempIntake(Pop, PlanID, PopFull, ApptDate, 1stAppt, Followup, Rn, UserID)
select Pop,PlanID, PopFull,InterviewDate,1stAppt,Followup, rn, @UserID 
from (
    Select *,row_number() over (partition by PlanID order BY AddedDate ASC) as rn 
    from VInfo
    where AddedDate is not null 
) t
where rn = 1 
and interviewdate >=@fromDate 
and interviewDate <=@toDate

union 

select Pop,PlanID, PopFull,InterviewDate,'2016-01-01',Followup, rn, @UserID 
from (
    Select *,row_number() over (partition by PlanID order BY AddedDate ASC) as rn 
    from VInfo t1
    where AddedDate is null 
    and not exists 
    (
        select 1
        from VInfo t
        where AddedDate is not null 
        and interviewdate >=@fromDate 
        and interviewDate <=@toDate
    )
) t
where rn = 1 
and interviewdate >=@fromDate 
and interviewDate <=@toDate

Upvotes: 2

Related Questions