June 5, 2015 at 1:17 pm
I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.
I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.
A small bit of the code:
declare @get_date datetime = convert(char(10),getdate(),101)
select top 10 pt.pt_id, pt.last_name
fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id
and a.pt_id = pt.pt_id
and a.from_file_ind = pt.from_file_ind
and a.pt_id_start_dtime = pt.pt_id_start_dtime)
wherea.from_file_ind in ('0A', '2A')
and a.hosp_svc <> 'SVH'
and a.fc not in ('1','2')
and a.prim_pyr_cd not in ('A56','A60','J56','S45','U40','U50')
and a.pt_bal_amt > 19.99
and left(pt.last_name,1) between 'A' and (case when @get_date <= '06/04/2015' then 'R' else 'Z' end)
and (a.last_ins_bl_dtime is not null or a.last_actl_pt_bl_dtime is not null)
Seems I should be able to not test the last name after the turnover date, but I can't figure out how.
Thanks all.
June 8, 2015 at 11:30 am
In some languages, CASE statements can return a BOOLEAN, but not in SQL. You're probably trying to return a BOOLEAN value from the CASE statement. Try the following instead:
/*
NOTE: I am using the strings "include" and "exclude" to be very clear about what I am doing, but you can use any values as long as all of the values returned by the case statement are compatible types.
*/
SELECT *
FROM smsmir.mir_pt pt
WHERE CASE
WHEN GETDATE() >= '2015-04-15' THEN 'include'
WHEN LEFT(pt.last_name, 1) BETWEEN 'A' AND 'R') THEN 'include'
ELSE 'exclude'
END = 'include'
This CASE expression returns a string and the WHERE clause requires a BOOLEAN, so we compare the returned string to the hard-coded string 'include' to get the BOOLEAN required by the WHERE clause.
Although, if this change is relatively permanent, you'd be better of editing your query to remove the criteria.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
June 8, 2015 at 11:47 am
You are right. I WAS trying to get a Boolean return from CASE before I came up with
(case when @get_date <= '06/04/2015' then 'R' else 'Z' end) I could always wait until the turnover date to change the code, but that's not as much fun as trying to figure something out.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply