April 19, 2004 at 11:50 am
I am drawing a blank on this one, maybe someone out there can help.
Below is my stored procedure...
CREATE PROCEDURE [dbo].[report__nonadmit_get]
@agency_list varchar(2000) = null,
@region_list varchar(2000) = null,
@branch_list varchar(2000) = null,
@lob_list varchar(2000) = null,
@from_date datetime =null,
@to_date datetime=null
AS
--Create Temp Agency table
create table #Agency (agency_id int, agency_code varchar(6), agency_name varchar(35))
if(@Agency_List is null)
begin
insert into #Agency
select agency_id, agency_code, [name]as agency_name from agency
end
else
begin
insert into #Agency
select agency_id, agency_code, [name]as agency_name from agency
inner join dbo.udf_ParseIDs(@Agency_List) as s on s.entity_id=agency.agency_id
end
--Create Temp Region Table
create table #region (region_id int, region_code varchar(6), region_name varchar(35),agency_id int)
if(@Region_List is null)
begin
insert into #region
select region_id, region_code, [name] as region_name, agency_id from region
end
else
begin
insert into #region
select region_id, region_code, [name] as region_name , agency_id from region
inner join dbo.udf_ParseIDs(@Region_List) as s on s.entity_id=region.region_id
end
--Create Temp Branch Table
create table #Branch (branch_id int, branch_code varchar(6),
region_id int,branch_name varchar(35))
if (@Branch_List is null)
begin
insert into #Branch
select branch_id, branch_code, region_id, [name]as branch_name from branch
end
else
begin
insert into #Branch
select branch_id, branch_code, region_id, [name]as branch_name from branch
inner join dbo.udf_ParseIDs(@Branch_List) as s on s.entity_id=branch.branch_id
end
-- Create Temp Line of Business (Lob) table
create table #LOB (Lob_code char(6), lob_description varchar(30))
if(@lob_list is null)
begin
insert into #LOB
select lob_code, [description] as lob_description from lob
end
else
begin
select lob_code, description as lob_description from lob
inner join dbo.udf_parsecodes(@lob_list) as s on s.entity_id=lob.lob_code
end
SELECT patient.patient_code, patient.last_name, patient.first_name, patient.middle_initial, service_period_status.status_date, GETDATE() AS run_dt,
company.name AS cname,
rtrim(LOB.LOB_code) + ' - ' + LOB.description as lname,
RTRIM(agency.agency_code) + ' - ' + agency.name AS aname,
case when patient.middle_initial is null then rtrim(patient.last_name) + ', ' + patient.first_name else
rtrim(patient.last_name) + ', ' + rtrim(patient.first_name) + ' ' + patient.middle_initial end as pname,
rtrim(branch.branch_code) + ' - ' +
branch.name AS bname, rtrim(region.region_code) + ' - ' + region.name AS rname,
rtrim(service_period_status.not_accepted_reason) + ' - ' + not_accepted_reason_dict.description as reason,
@from_date as dtFrom,
@to_date as dtTo
FROM patient RIGHT OUTER JOIN
LOB RIGHT OUTER JOIN
service_period ON LOB.LOB_code = service_period.lob RIGHT OUTER JOIN
service_period_status ON service_period.service_period_id = service_period_status.service_period_id ON
patient.patient_id = service_period.patient_id LEFT OUTER JOIN
branch LEFT OUTER JOIN
company RIGHT OUTER JOIN
agency ON company.company_id = agency.company_id RIGHT OUTER JOIN
region ON agency.agency_id = region.agency_id ON branch.region_id = region.region_id ON service_period.branch_id = branch.branch_id
left outer join not_accepted_reason_dict on service_period_status.not_accepted_reason =
not_accepted_reason_dict.code
inner join [#branch] on service_period.branch_id=#branch.branch_id or service_period.branch_id = NULL
inner join [#region] ON [#branch].region_id = [#region].region_id
inner join [#agency] ON [#region].agency_id = [#agency].agency_id
inner join [#lob] ON service_period.lob = [#lob].lob_code or service_period.lob = NULL
WHERE (service_period_status.service_status = 'N')
and ( service_period_status.status_date >=@from_date or @from_date is null)
and ( service_period_status.status_date <=@to_date or @to_date is null)
ORDER BY aname,rname,bname,lname,reason,pname
GO
You can see where it does inner joins on the temp tables agency, region, branch etc.....
My problem is that if the user selects ALL agencies, regions or branches... I really want to do a left outer join. That way even if there are NULL values in the main table for these ID's, the rows will be returned.
Any ideas?
April 19, 2004 at 12:03 pm
How about using an IF...ELSE with 2 different select statements?
April 19, 2004 at 12:29 pm
I was thinking about that but was wondering if there was anything I could do within 1 sql statement.
Thanks
April 21, 2004 at 6:43 am
What about building the SQL statement at run-time and using EXEC (@Sql).
April 21, 2004 at 8:55 am
It's a bit early in the morning for me to be thinking this hard but here goes...
Use a left outer join
then use a case to decide what data you want from the join based on null values in whatever columns - sorry the query was just to damn long to read the whole thing
somehting like this
left outer join table1
on table1.column1 = table2.column1
and case when (check for inner join flag) and table2.column1 is null then 1
when (check for NOT inner join flag) and table2.column1 is not null then 1
else 0
end = 1
which as I said may not make any sense whatsoever cause for me this is EARLY. So let me go back to sleep and look at this tomorrow and maybe I can give a better example.
Michael R. Schmidt
Developer
April 21, 2004 at 9:18 am
That is what I was shooting for, but I wasn't sure if I was able to use CASE statements in the JOINS.....
I will give it a try though.
Thanks.
April 24, 2004 at 9:12 am
Hi Carol,
I wonder if you solved the problem?
April 24, 2004 at 10:34 am
Actually, I have not had a chance to try it as of yet. I will do so this Monday and let you know.
THanks.
April 26, 2004 at 2:04 pm
Thanks to MikeyMikey's suggestion I got it to work..This is a simplied version.
I have an employee table and an employee_region table. Not every employee will have an employee_region row.
My problem was that I needed the ability to do a left outer join or an inner join depending on certain criteria.
This is my solution
declare @setit int ;
set @setit = 0;
--0 means just the records that have regions (21)
--1 means all employees (27)
select distinct employee.emp_id from employee
left outer join employee_region on
employee.emp_id = employee_region.emp_id
where case when (@setit = 0) and employee_region.emp_id is not null then 1
when (@setit = 1) and (employee_region.emp_id is null or employee_region.emp_id is not null)
then 1
else 0
end = 1
Yeah!!!
April 26, 2004 at 2:39 pm
Glad to be of assistance!
I just love SQL, now Microsoft, where is that natural join syntax I've been waiting for, I'd like to save some typing!
And wouldn't everyone just love it if SQL Server would optimize a simple IN clause into a more effecient join? Hello SQL team, are you listening?
Michael R. Schmidt
Developer
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply