Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hi,
try to write in a more readable way please. no one will have time to "try" to read.
Thank you
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
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) ?
Yes Loading the particular week data