Help with query using two fields as a range...

[t16turbo]

Hi folks,


I have a query that returns trasaction date from our finance table PPA_WIP.

I need to be able to return the financial period that this trasaction falls within.

There is a view called NBI_DIM_FISCAL_TIME_V that has the following fields I would be interested in:

Period_Key
Period_Start
Period_End


how can I return the Period_Key, by checking which Period_Start-Period_End the Transaction date (TransDate from PPA_WIP) is within?

 

[Farab]

select Period_Key
from NBI_DIM_FISCAL_TIME_V
where :Transaction_Date >= Period_Start
and :Transaction_Date <= Period_End

 

[t16turbo]

maybe its my lunch time drink, but I really don;t know how I didn;t work that out!!!

thanks Farab!

you're Farabulous! ;-)

 

[lewisp]

You can also use BETWEEN

select Period_Key
from NBI_DIM_FISCAL_TIME_V
where :Transaction_Date BETWEEN Period_Start
                        AND     Period_End

 

[t16turbo]

just a thought.

here is my SQL that contains the transdate as a selected field

SELECT r.last_name, r.first_name, r.external_id, wipunion.emplyhomedepart, m.departcode,  nbi.customer, cst.dsti_proj_ref, nbi.project_name,  wipunion.STATUS, wipunion.TRANSCLASS, wipunion.COMPANY_CODE, wipunion.PROJECT_CODE, wipunion.RESOURCE_CODE, wipunion.CHARGE_CODE, chg.prname, wipunion.EXTERNAL_ID, wipunion.QUANTITY, (wipunion.Quantity/7.5)  as Days, wipunion.TRANSDATE, wipunion.DEPARTCODE
FROM
(SELECT STATUS, TRANSCLASS, EMPLYHOMEDEPART, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, CHARGE_CODE, EXTERNAL_ID, QUANTITY, TRANSDATE, DEPARTCODE
FROM NIKU.PPA_WIP
WHERE RESOURCE_CODE in (select r.unique_name from NIKU.srm_resources r, NIKU.pac_mnt_resources d WHERE r.UNIQUE_NAME = d.resource_code AND 
d.departcode in ('UK1205', 'UK1206', 'UK1209', 'UK1211', 'UK1212', 'UK1213', 'UK1214', 'UK1216', 'UK1217', 'UK1218') OR (d.departcode IN ('UK1000', 'UK1104', 'UK1105', 'UK1106') AND SUBSTR(r.unique_name, 1, 3) IN ('THA', 'AUS', 'BOS')))
UNION
SELECT 0, TRANSCLASS, EMPLYHOMEDEPART, COMPANY_CODE, PROJECT_CODE, RESOURCE_CODE, CHARGE_CODE, EXTERNAL_ID, QUANTITY, TRANSDATE, DEPARTCODE
FROM NIKU.PPA_TRANSCONTROL
WHERE RESOURCE_CODE in (select r.unique_name from NIKU.srm_resources r, NIKU.pac_mnt_resources d WHERE r.UNIQUE_NAME = d.resource_code AND 
d.departcode in ('UK1205', 'UK1206', 'UK1209', 'UK1211', 'UK1212', 'UK1213', 'UK1214', 'UK1216', 'UK1217', 'UK1218') OR (d.departcode IN ('UK1000', 'UK1104', 'UK1105', 'UK1106') AND SUBSTR(r.unique_name, 1, 3) IN ('THA', 'AUS', 'BOS')))) wipunion,
NIKU.pac_mnt_resources m,
NIKU.srm_resources r,
NIKU.nbi_project_current_facts nbi,
NIKU.odf_ca_project cst,
NIKU.prchargecode chg
WHERE wipunion.project_code = nbi.project_code AND
wipunion.resource_code = m.resource_code AND
cst.id = nbi.project_ID AND
wipunion.charge_code = chg.prexternalid AND
r.UNIQUE_NAME = m.resource_code

it is in there as wipunion.transdate

I'm guessing I need a nested query selecting the time period from the NBI_DIM_FISCAL_TIME_V?

would I use the query suggested above, in my SELECT clause or the FROM clause?