May 15, 2008 at 1:57 pm
I have a query with many joins (2 inner the rest outer). table A may be related to B or C or neither. my query currently has multiple ors on outer tables (like below). the outer join data looks correct. But I doubt it is correct since B and C are outer joins. I am stuck on moving the or logic from where to outer join and. I tried moving "where" logic to left outer join with "or and" which runs forever. I tried moving "where" logic to left outer join with "and / and" and the outer b table data is missing.
select *
from a
...
left outer join b
on a.aid = b.bid
left outer join c
on a.cid = c.cid
Where a.flg=1
AND b.b_status_cd > 1
And a.a_date >= @startDate
And a.a_date < @Enddate
OR ( b.b_date >= @startDate
And b.b_date < @Enddate)
OR ( c.c_date = @startDate
And c.date < @Enddate)
Could this data be correct?
any ideas on query redesign?
May 16, 2008 at 2:10 am
[font="Verdana"]This question can't be answered on the fly. So, if possible, post some real life data from all the tables with expected o/p.
Mahesh[/font]
MH-09-AM-8694
May 16, 2008 at 5:52 am
You have not really given a clear example - some sample data and an actual test query would help, but I think you just have your "OR" logic in the wrong place. It should be in the join clauses:
select *
from a
...
left outer join b on a.aid = b.bid
AND b.b_status_cd > 1 AND b.b_date >= @startDate
And b.b_date < @Enddate
left outer join c on a.cid = c.cid
AND c.c_date = @startDate
And c.date < @Enddate
Where a.flg=1
And a.a_date >= @startDate
And a.a_date < @Enddate
May 16, 2008 at 7:56 am
thanks. the data from query looks right when I use the outer join tables or in where. the bp alias has many for entries for this day. but when I try putting then bp restricition in outer join I get nulls for bp cols. this does not work?
Left Outer Join dbo.benefit_payment bp
On bp.benefit_payment_id = sd.benefit_payment_ID
AND bp.bp_status_cd > 1 -- remove requested checks
and ( bp.bp_check_dt >= @startDate
And bp.bp_check_dt < @Enddate)
but this does
--query data
service_detail_id OCCIID OCRECID OCPAGROUP OCPAPREFIX OCPAPOLNO OCPAREL OCPAIDNAME OCSYSID OCCLID OCCLGEN OCRECORDIND OCINCDT OCSETTLEMENTCODE OCSITE OCSTATUS OCPIID OCCPAYEE OCCBANK OCCBDC OCCCFOCLEAREDDT OCCCHKAMT OCCSTATUS OCPAIDDT OCCNO OCCHECKIND OCCLAIMANTDOB OCCREFAMT1 OCCREFDATE2 OCCREFAMT2 OCCISSUEDATE OCCCFOISSUEDATE OCRPREPORTSITE OCPASEX OCPICOV OCSTATECD OCMETYPE OCMELOC OCCSEPCHKAMT OCCFOUNITS OCCFOVALUEPERUNIT OCLTCFINALPARTIALPAY FILLER OCWOPCLMREF OCCLAIMYEAR OCDIAGCODE1 OCDIAGCODE2 OCDIAGCODE3 OCDIAGCODE4
----------------- ------ ------- --------- ---------- --------- ------- ---------- ------- ----------- ------- ----------- ------- ---------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ---------- -------- ------- ------ --------------- --------------------------------------- --------- -------- ---------- ---------- ------------- ---------- ----------- ---------- ------------ --------------- -------------- ------- ------- --------- -------- -------- --------------------------------------- ---------------------- ----------------- -------------------- ----------------------- ----------- ----------- ----------- ----------- ----------- -----------
44005954 BL IC 07 6520514 M ETHEL M 38436977 00 C 000630 000 02 02 15203 M 5716 415D35 080218 22588.00 03 080218 400202306 1 190720 00000000} 00 00000000} 080218 080218 F VA 22588.00 30 1 2008 342 438 402 250
44005944 BL IC 07 6520514 M ETHEL M 38777475 00 C 000630 000 02 02 15203 M 5716 415D35 080218 22588.00 03 080218 400202306 1 190720 00000000} 00 00000000} 080218 080218 F VA 22588.00 31 1 2008 342 438 402 250
44024169 BL IC 07 6624434 M SHERWI M 39620265 00 C 030114 000 02 02 15203 M 5716 415D35 080219 110.00 03 080219 400202989 1 260101 00000000} 00 00000000} 080219 080219 M IL 110.00 1 1 F 2008 3310 3310 NULL NULL
44024169 BL IC 07 6624434 M SHERWI M 39620265 00 C 050324 000 02 02 15203 M 5716 415D35 080219 110.00 03 080219 400202989 1 260101 00000000} 00 00000000} 080219 080219 M IL 110.00 1 1 F 2008 3310 3310 NULL NULL
Declare @StartDate datetime
, @EndDate datetime
Set @startdate = '2008-02-18 00:00:00.000'
-- set Enddate to today midnight
if @StartDate is null
Select @EndDate = Dateadd(d,Datediff(d,0,Getdate()),0)
, @StartDate = Dateadd(d,-1,@EndDate);
Else -- round to midnight
Select @EndDate = Dateadd(d,Datediff(d,0,@StartDate),1)
,@StartDate = Dateadd(d,Datediff(d,0,@StartDate),0);
-- Select @startdate, @Enddate
-- set subquery CTe
WITH First_EOB_CTE (rfb_ID, eob_eb_start_dt)
AS
(
Select rfb2.rfb_ID, Min(eob2.eb_start_dt)
From dbo.request_for_benefit rfb2
Inner Join dbo.episode_of_benefit eob2
on eob2.rfb_id = rfb2.rfb_id
-- Filter approved eob
Where eob2.eb_status_cd = 2
Group by rfb2.rfb_ID
),
Paid_SD_CTE (payment_request_id , sd_amount)
AS
(
Select pr2.payment_request_id ,Sum(sd2.sd_amount)
From dbo.service_detail sd2
Inner Join dbo.payment_request_detail prd2
on sd2.payment_request_detail_id = prd2.payment_request_detail_id
Inner Join dbo.payment_request pr2
on pr2.payment_request_id = prd2.payment_request_id
-- Filter covered, okay to pay and reported flg service details
Where sd2.sd_covered_flg = 1
and SD2.sd_ok_to_pay_flg = 1
-- check reported flag instead of earliest rpt date
-- earliest rpt date and rtd flag should be synch
and sd2.sd_reported_flg = 1
-- use same filter as main where
--and sd2.sd_ok_reason_cd > 1 --resulted in no totpaids?
Group By pr2.payment_request_id
),
Requested_Amt_CTE (payment_request_id , Req_amount)
AS
(
Select pr2.payment_request_id, sum(prd2.prd_requested_amount)
From dbo.payment_request_detail prd2
Inner join dbo.payment_request pr2
on pr2.payment_request_id = prd2.payment_request_id
Group By pr2.payment_request_id
),
SD_Adjustment_OrigCheckNo_CTE (reversed_service_detail_id , bp_check_no)
AS
(
Select --sd.service_detail_id,
sd1.reversed_service_detail_id
--sd2.service_detail_id,
, bp.bp_check_no
From dbo.service_detail sd1
Inner Join dbo.service_detail sd2
on sd1.service_detail_id = sd2.reversed_service_detail_id
inner join dbo.benefit_payment bp
on bp.benefit_payment_id = sd2.benefit_payment_id
-- check reported flag instead of earliest rpt date
-- earliest rpt date and rtd flag should be synch
--Where sd2.sd_reported_flg = 1
-- use same filter as main where
-- and sd2.sd_ok_reason_cd > 1
),
SD_FinalPay_CTE (service_detail_id , max_eb_status_cd)
AS
(
Select sd2.service_detail_id
, substring(max(
convert(char(30), eob2.eb_decision_dt, 121) +
cast(eob2.eb_status_cd as varchar(1))
), 31, 1) As max_eb_status_cd
From service_detail sd2
Inner join eob_service_detail_paid_day eob_sd
on sd2.service_detail_id = eob_sd.service_detail_id
Inner join episode_of_benefit eob
on eob_sd.episode_of_benefit_id = eob.episode_of_benefit_id
INner join request_for_benefit rfb
on eob.rfb_id = rfb.rfb_id
Inner join dbo.episode_of_benefit eob2
on rfb.rfb_id = eob2.rfb_id
where eob2.eb_status_cd = 3
-- check reported flag instead of earliest rpt date
-- earliest rpt date and rtd flag should be synch
and sd2.sd_reported_flg = 1
-- use same filter as main where
--and sd2.sd_ok_reason_cd > 1
group by sd2.service_detail_id
),
get_top2_comorbid_cte (rfb_id,icd9_code) -- get top two comorbid codes since one rfb can have many coms
As
(
Select rfb.rfb_id,o.icd9_code
From dbo.request_for_benefit rfb
CROSS APPLY
(Select top 2 rfb_id,icd9_code
From dbo.comorbid_condition com
where rfb.rfb_id = com.rfb_id) AS O
), -- Select * From get_top2_comorbid_cte
comorbid_cte (rfb_id, icd9_code3, icd9_code4) -- reorg data From two cols, to one row per rfb and two com code colums
as
(
Select com1.rfb_id, max(com1.icd9_code) as icd9_code3, min(com2.icd9_code) as icd9_code4
From get_top2_comorbid_cte com1
Inner join get_top2_comorbid_cte com2
On com1.rfb_id = com2.rfb_id
Group By com1.rfb_Id
),
Covered_SD_CTE (payment_request_id , sd_amount)
AS
(
Select pr2.payment_request_id ,Sum(sd.sd_amount)
From dbo.service_detail sd
Inner Join dbo.payment_request_detail prd2
on sd.payment_request_detail_id = prd2.payment_request_detail_id
Inner Join dbo.payment_request pr2
on pr2.payment_request_id = prd2.payment_request_id
-- filter covered and reported service details
Where sd.sd_covered_flg = 1
-- check reported flag instead of earliest rpt date
-- earliest rpt date and rtd flag should be synch
and sd.sd_reported_flg = 1
-- use same filter as main where
-- and sd.sd_ok_reason_cd > 1
Group By pr2.payment_request_id
)
Select --top 1000
sd.service_detail_id
, 'BL' AS OCCIID
, 'IC' AS OCRECID
, Replicate(' ',8) AS OCPAGROUP --(redefines OCCEXAMINER)
-- is this string logic sufficient or check len?
, left(ltrim(rtrim(ISNULL(Pol.policy_no,' '))),2) AS OCPAPREFIX --(part of OCPAID)
, Right(ltrim(rtrim(ISNULL(Pol.policy_no,' '))),7) AS OCPAPOLNO --(part of OCPAID)
, 'M' AS OCPAREL
, Left(ltrim(rtrim(isnull(lid.life_fname,' '))),6) AS OCPAIDNAME
, 'M' AS OCSYSID
, pr.payment_request_id AS OCCLID
, '00' AS OCCLGEN --(redefines OCCLGENX)
, 'C' AS OCRECORDIND
, convert(Varchar(6),FirstEOB.eob_eb_start_dt,12) AS OCINCDT
, Case When sd.part_service_type_code = 'INTR' Then '201'
Else '000' End AS OCSETTLEMENTCODE
, IsNull(ge.ge_txt,'02') AS OCSITE
--- , Case When ba.ba_amount > 0 Then '91' Else '02' End AS OCSTATUS
, Case When sd.benefit_adjustment_id > 0 Then '91' Else '02' End AS OCSTATUS
--,Left(ltrim(rtrim(Isnull(dbo.product_package.pp_reference))), 6) AS OCPIID
-- we dont have real plan codes loaded just get top 1 for now
,(Select TOP(1) pp.pp_reference
From dbo.policy p
Inner Join dbo.certificate c
On c.series_id = p.series_id And c.cert_no = p.cert_no
Inner Join dbo.product_package pp
On pp.product_package_id = c.product_package_id
Where --pp.product_package_type_cd = 1
NOT pp.pp_reference is null
and len(pp.pp_reference) = 5
Order By pp.pp_reference) AS OCPIID
, Case When pay.payee_type_cd = 1 Then 'P' -- 1 provider
When Pay.payee_type_cd = 2 Then 'M' --2 claimant
End AS OCCPAYEE
, Case When IsNull(ge.ge_txt,'02') = '01' Then '5727'
When IsNull(ge.ge_txt,'02')= '02' Then '5716'
When IsNull(ge.ge_txt,'02') = '10' Then '5321'
When IsNull(ge.ge_txt,'02') = '05' Then '7777'
End AS OCCBANK
-- ADD pol ext after conversion
--, Policy_ext.policy_ext_txt AS OCCBDC
, '415D35' AS OCCBDC -- hard code test data
, case when Isnull(sd.benefit_payment_id,0) > 0 then convert(char(6),bp.bp_check_dt,12)
when IsNull (sd.benefit_adjustment_id,0) > 0 then convert(char(6),ba.ba_date_entered,12)
END AS OCCCFOCLEAREDDT
, bp.bp_amount AS OCCCHKAMT
, Case When bp.bp_status_cd = 2 Then '03'
When bp.bp_status_cd =4 Then '93'
When bp.bp_status_cd = 6
or bp.bp_status_cd = 7 Then '95'
Else '00' End AS OCCSTATUS
, convert(Varchar(6),bp.bp_check_dt,12) AS OCPAIDDT --(redefined as OCBANKCLEAREDDATE; also ICCACTIONDT)
, bp.bp_check_no AS OCCNO
, '1' AS OCCHECKIND
, convert(Varchar(6),li.life_birth_dt,12) AS OCCLAIMANTDOB --(redefined as OCCREFDATE1
, '00000000}' AS OCCREFAMT1
, '00 ' AS OCCREFDATE2
, '00000000}' AS OCCREFAMT2
, convert(Varchar(6),bp.bp_check_dt,12) AS OCCISSUEDATE
, convert(Varchar(6),bp.bp_check_dt,12) AS OCCCFOISSUEDATE
, Replicate(' ',2) AS OCRPREPORTSITE -- convert to hex
, Case When lid.life_sex_cd = 1 Then 'M'
When lid.life_sex_cd = 2 Then 'F'
End AS OCPASEX
, Replicate(' ',2) AS OCPICOV
, pols.filing_state AS OCSTATECD
, Replicate(' ',4) AS OCMETYPE --(redefines OCHTAGTID)
, Replicate(' ',8) AS OCMELOC
, bp.bp_amount AS OCCSEPCHKAMT
, sd.sd_service_units AS OCCFOUNITS
, '1' AS OCCFOVALUEPERUNIT
, Case When cer.cert_status_rsn_cd IN (18, 51) OR FinalPay.max_eb_status_cd=3 Then 'F'
Else ' ' End AS OCLTCFINALPARTIALPAY
, Replicate(' ',23) AS FILLER
, Replicate(' ',11) AS OCWOPCLMREF
, left(convert(Varchar(8),bp.bp_check_dt,112),4) AS OCCLAIMYEAR
, rfb.pd_icd9_code AS OCDIAGCODE1
, rfb.de_icd9_code AS OCDIAGCODE2
-- limit comorbid to first two.
, comorbid.icd9_code3 AS OCDIAGCODE3
, comorbid.icd9_code4 AS OCDIAGCODE4
--Into cd_dev_cltcas_datamart.dbo.Conseco_248byte_WrapBack
From dbo.service_detail sd
Inner join dbo.payment_request_detail prd
On prd.payment_request_detail_id = sd.payment_request_detail_id
Inner Join dbo.payment_request pr
on pr.payment_request_id = prd.payment_request_id
Left Outer Join dbo.benefit_payment bp
On bp.benefit_payment_id = sd.benefit_payment_ID
Left Outer join dbo.payee pay
on pay.payee_id = bp.payee_id
Left outer Join dbo.service_provider sp
On sp.service_provider_id = prd.service_provider_id
left outer Join dbo.benefit_adjustment ba
On ba.benefit_adjustment_ID = sd.benefit_adjustment_id
Left outer Join dbo.ufn_policy_affiliation(null, null, null, null) polaff
on polaff.series_id = sd.series_id
and polaff.cert_no = sd.cert_no
-- THERE IS NO group ext data setup
Left Outer Join dbo.group_ext ge
on ge.grp_id = polaff.grp_id
Left Outer Join dbo.policy pol
On pol.series_id = sd.series_id
And pol.cert_no = sd.cert_no
Left Outer Join dbo.policy_series pols
On pol.series_id = pols.series_id
Left Outer Join dbo.request_for_benefit rfb
On pol.series_id = rfb.series_id
And pol.cert_no = rfb.cert_no
Left Outer Join dbo.certificate cer
on pol.series_id = cer.series_id
and pol.cert_no = cer.cert_no
--Left Outer join dbo.product_package pp
-- on pp.product_package_id = cer.product_package_id
-----------------------------------
-- Left Outer Join dbo.group_ext ge
-- On ge.grp_id = ig.grp_id
-- And ge.ge_type_cd = 13 ??
Left Outer Join dbo.life li
On pol.life_id = li.life_id
Left Outer Join dbo.life_demographics lid
On li.life_id = lid.life_id
And li.current_na_eff_dt = lid.life_na_eff_dt
Left Outer Join dbo.episode_of_benefit eob
On eob.rfb_id = rfb.rfb_id
-- Left Outer Join dbo.comorbid_condition com
-- On rfb.rfb_Id = com.rfb_id
Left Outer Join First_EOB_CTE as FirstEOB
on FirstEOB.rfb_id = rfb.rfb_id
Left Outer Join Paid_SD_CTE AmtPaid --, sd_amount)
on AmtPaid.payment_request_id = pr.payment_request_id
Left Outer Join Requested_Amt_CTE as ReqAmt --(payment_request_id , Req_amount)
on ReqAmt.payment_request_Id = pr.payment_request_id
Left Outer Join Covered_SD_CTE As CoverAmt --(payment_request_id , sd_amount)
On CoverAmt.payment_request_Id = pr.payment_request_id
Left Outer Join SD_Adjustment_OrigCheckNo_CTE as OrigCheckNo --(reversed_service_detail_id , bp_check_no)
On OrigCheckNo.reversed_service_detail_id = sd.service_detail_id
Left Outer Join SD_FinalPay_CTE as Finalpay -- (service_detail_id , max_eb_status_cd)
On FinalPay.service_detail_id = sd.service_detail_id
Left Outer Join comorbid_cte as comorbid --(rfb_id, icd9_code3, icd9_code4)
On rfb.rfb_id = comorbid.rfb_id
--1. Policy belongs to a CFO Company (Company Codes 01, 02, 05, or 10.)
--2. Policy has a claim paid during the date range of the data feed (based on bp_check_date) OR
--3. Policy has an adjustment processed during the date range (based on ba_entered_dt.)
Where-- ge.ge_txt IN ('01', '02', '05', '10') AND
-- use sd_ok_reason_cd > 1 instead of <> so sargable
--sd.sd_ok_reason_cd > 1
-- service detail lines processed and paid
sd.sd_reported_flg=1
AND bp.bp_status_cd > 1 -- remove requested checks
--and where sd sd_earliest_report_dt in range
and ( (sd.sd_earliest_report_dt >= @startDate
And sd.sd_earliest_report_dt < @Enddate)
OR ( bp.bp_check_dt >= @startDate
And bp.bp_check_dt < @Enddate)
OR ( ba.ba_date_entered >= @startDate
And ba.ba_date_entered < @Enddate))
May 16, 2008 at 8:05 am
Mmmm... we're really looking for table DDL in the form of CREATE TABLE statements and sample data in the form of INSERT statements. But anyway, looking at the snippet you posted originally: putting a condition in the outer table of an outer join (ie the right hand table of a left join) turns your outer join into an inner join. Therefore, either change LEFT OUTER to INNER, or put the condition in the join predicate instead. Which one you do will depend on the results you're looking for.
John
May 16, 2008 at 8:12 am
but this query returns nulls for bp date? when I know there are bp dates that match range?
sd_earliest_report_dt bp_check_dt
----------------------- -----------------------
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
Declare @StartDate datetime
, @EndDate datetime
--set @EndDate = '2008-02-04 00:00:00.000' -- 46
Set @startdate = '2008-02-18 00:00:00.000'
-- set Enddate to today midnight
if @StartDate is null
Select @EndDate = Dateadd(d,Datediff(d,0,Getdate()),0)
, @StartDate = Dateadd(d,-1,@EndDate);
Else -- round to midnight
Select @EndDate = Dateadd(d,Datediff(d,0,@StartDate),1)
,@StartDate = Dateadd(d,Datediff(d,0,@StartDate),0);
select sd.sd_earliest_report_dt, bp.bp_check_dt
From dbo.service_detail sd
Inner join dbo.payment_request_detail prd
On prd.payment_request_detail_id = sd.payment_request_detail_id
Inner Join dbo.payment_request pr
on pr.payment_request_id = prd.payment_request_id
Left Outer Join dbo.benefit_payment bp
On bp.benefit_payment_id = sd.benefit_payment_ID
AND bp.bp_status_cd > 1 -- remove requested checks
and ( bp.bp_check_dt >= @startDate
And bp.bp_check_dt < @Enddate)
-- OR ( ba.ba_date_entered >= @startDate
-- And ba.ba_date_entered < @Enddate)
Where-- ge.ge_txt IN ('01', '02', '05', '10') AND
-- use sd_ok_reason_cd > 1 instead of <> so sargable
--sd.sd_ok_reason_cd > 1
-- service detail lines processed and paid
sd.sd_reported_flg=1
--AND bp.bp_status_cd > 1 -- remove requested checks
--and where sd sd_earliest_report_dt in range
And (sd.sd_earliest_report_dt >= @startDate
And sd.sd_earliest_report_dt < @Enddate)
-- OR ( bp.bp_check_dt >= @startDate
-- And bp.bp_check_dt < @Enddate)
-- OR ( ba.ba_date_entered >= @startDate
-- And ba.ba_date_entered < @Enddate)
May 16, 2008 at 8:40 am
we cant change to inner because the sd table can be be related to bp or ba or none.
May 16, 2008 at 8:51 am
I tried putting all conditions in cte then left join. but bp results are nulls. there are bp dates that match the range.
So the best query uses left outer join with ors in where clause?
sd_earliest_report_dt bp_check_dt
----------------------- -----------------------
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
2008-02-18 00:00:00.000 NULL
Declare @StartDate datetime
, @EndDate datetime
--set @EndDate = '2008-02-04 00:00:00.000' -- 46
Set @startdate = '2008-02-18 00:00:00.000'
-- set Enddate to today midnight
if @StartDate is null
Select @EndDate = Dateadd(d,Datediff(d,0,Getdate()),0)
, @StartDate = Dateadd(d,-1,@EndDate);
Else -- round to midnight
Select @EndDate = Dateadd(d,Datediff(d,0,@StartDate),1)
,@StartDate = Dateadd(d,Datediff(d,0,@StartDate),0);
with benefit_payment_conditionsCTE (benefit_payment_id, payee_id, bp_status_cd, bp_amount, bp_check_no, bp_check_dt, bp_check_send_dt, bp_ap_reference, bp_creation_dt, bp_upto_dt, bp_check_cleared_dt, bank_id, bank_acct_no, bp_eft_flg, bp_route_no, bp_acct_number, bp_bank_acct_type_cd)
as
(
select benefit_payment_id, payee_id, bp_status_cd, bp_amount, bp_check_no, bp_check_dt, bp_check_send_dt, bp_ap_reference, bp_creation_dt, bp_upto_dt, bp_check_cleared_dt, bank_id, bank_acct_no, bp_eft_flg, bp_route_no, bp_acct_number, bp_bank_acct_type_cd
from dbo.benefit_payment bp
where bp.bp_status_cd > 1 -- remove requested checks
and ( bp.bp_check_dt >= @startDate
And bp.bp_check_dt < @Enddate)
)
select sd.sd_earliest_report_dt, bp.bp_check_dt
From dbo.service_detail sd
Inner join dbo.payment_request_detail prd
On prd.payment_request_detail_id = sd.payment_request_detail_id
Inner Join dbo.payment_request pr
on pr.payment_request_id = prd.payment_request_id
Left Outer Join benefit_payment_conditionsCTE bp
On bp.benefit_payment_id = sd.benefit_payment_ID
-- AND bp.bp_status_cd > 1 -- remove requested checks
-- and ( bp.bp_check_dt >= @startDate
-- And bp.bp_check_dt < @Enddate)
---- OR ( ba.ba_date_entered >= @startDate
-- And ba.ba_date_entered < @Enddate)
Where-- ge.ge_txt IN ('01', '02', '05', '10') AND
-- service detail lines processed and paid
sd.sd_reported_flg=1
-- need one date in range
-- sd date in range
-- or bp date in range
-- or ba date in range
and (sd.sd_earliest_report_dt >= @startDate
And sd.sd_earliest_report_dt < @Enddate)
-- OR ( bp.bp_check_dt >= @startDate
-- And bp.bp_check_dt < @Enddate)
-- OR ( ba.ba_date_entered >= @startDate
-- And ba.ba_date_entered < @Enddate)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply