4 Replies Latest reply: Dec 12, 2017 5:46 AM by surya j RSS

    Pick two week data automatically

    surya j

      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;

        • Re: Pick two week data automatically
          youssef belloum

          Hi,

           

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

           

          Thank you

            • Re: Pick two week data automatically
              surya j

              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

            • Re: Pick two week data automatically
              youssef belloum

              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) ?