Pick two week data automatically

[Anonymous]

Hi I have a question regarding the below query. I have two date fields in the query, one is AL1.FIRST_FUNDED_DT & other is AL2.PROCESS_DATE. I want my AL1.FIRST_FUNDED_DT to pick only one week data & the process date to pick up two weeks data automatically irrespective of the selection.Please suggest? Presently i have used rolling year condition SELECT AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT,AL2.TXN_CNT, SUM ( AL2.TXN_CNT ) as Sum_transaction_Count, case when (SUM ( AL2.TXN_CNT )) > 0  then 'Y' else 'N' end as Trade_Ind, AL1.POE_CAT, case when AL1.BUS_LINE = 'PAS' then 'PAS' when AL1.BUS_LINE = 'SMAF' then 'PAS' when AL1.BUS_LINE = 'UMA' then 'PAS' when AL1.BUS_LINE = 'UMAT' then 'PAS' when AL1.BUS_LINE = 'BDI' then 'PAS' when AL1.BUS_LINE = 'SMEI' then 'PAS' when AL1.BUS_LINE = 'SMLC' then 'PAS' when AL1.BUS_LINE = 'DMA' then 'PAS' when AL1.BUS_LINE = 'PWM' then 'PAS' when AL1.BUS_LINE = 'PWMT' then 'PAS' else 'Non PAS' end as Pas_Ind, AL1.HH, AL1.BUS_LINE, AL1.CIT_STATE, AL1.CITY, AL1.ORIGIN, AL1.STATE, AL1.ZIP, AL1.RETIRE_INDIC, AL1.ACCT_TYPE, AL1.ACCT_STATUS,AL2.PROCESS_DATE FROM PLVDBO.ACCOUNT AL1, PLVDBO.A_FIN_TXN_DTL AL2 WHERE ( AL1.ACCT = AL2.ACCT (+)) AND ((AL1.FIRST_FUNDED_DT BETWEEN TIMESTAMP '$(vDateStart)' AND TIMESTAMP '$(vDateEnd)') AND (AL2.PROCESS_DATE(+) BETWEEN TIMESTAMP '$(vDateStart)' AND TIMESTAMP '$(vDateEnd)') AND (AL2.COMM_TRADE_INDIC(+)='Y')) GROUP BY AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT,AL1.POE_CAT, case when AL1.BUS_LINE = 'PAS' then 'PAS' when AL1.BUS_LINE = 'SMAF' then 'PAS' when AL1.BUS_LINE = 'UMA' then 'PAS' when AL1.BUS_LINE = 'UMAT' then 'PAS' when AL1.BUS_LINE = 'BDI' then 'PAS' when AL1.BUS_LINE = 'SMEI' then 'PAS' when AL1.BUS_LINE = 'SMLC' then 'PAS' when AL1.BUS_LINE = 'DMA' then 'PAS' when AL1.BUS_LINE = 'PWM' then 'PAS' when AL1.BUS_LINE = 'PWMT' then 'PAS' else 'Non PAS' end , AL1.HH, AL1.BUS_LINE, AL1.CIT_STATE, AL1.CITY, AL1.ORIGIN, AL1.STATE, AL1.ZIP, AL1.RETIRE_INDIC, AL1.ACCT_TYPE, AL1.ACCT_STATUS,AL2.TXN_CNT,AL2.PROCESS_DATE;

[YoussefBelloum]

Hi,

try to write in a more readable way please. no one will have time to "try" to read.

Thank you

[Anonymous]

Hi

I have a question regarding the below query.

I have two date fields in the query, one is AL1.FIRST_FUNDED_DT & other is AL2.PROCESS_DATE. I want my AL1.FIRST_FUNDED_DT to pick only one week data & the process date to pick up two weeks data automatically irrespective of the selection.Please suggest?

Presently i have used rolling year condition

SELECT AL1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT,AL2.PROCESS_DATE

FROM

PLVDBO.ACCOUNT AL1, PLVDBO.A_FIN_TXN_DTL AL2 WHERE ( AL1.ACCT = AL2.ACCT (+)) AND ((AL1.FIRST_FUNDED_DT BETWEEN TIMESTAMP '$(vDateStart)' AND TIMESTAMP '$(vDateEnd)') AND (AL2.PROCESS_DATE(+) BETWEEN TIMESTAMP '$(vDateStart)' AND TIMESTAMP '$(vDateEnd)') AND (AL2.COMM_TRADE_INDIC(+)='Y')) GROUP BY

L1.ACCT, AL1.REG_CAT_COARSE, AL1.FIRST_FUNDED_DT,AL2.PROCESS_DATE

[YoussefBelloum]

I want my AL1.FIRST_FUNDED_DT to pick only one week data & the process date to pick up two weeks data automatically irrespective of the selection.

what do you mean by pick ? you mean LOAD on the script ?

what is the value of your variables $(vDateStart) and $(vDateEnd) ?

[Anonymous]

Yes Loading the particular week data