5 Replies Latest reply on Aug 23, 2019 7:09 AM by Sean Connolly

    Pentaho DI SQL Question 2

    Sean Connolly
      Share This:

      I am writing a Pentaho transformation to pull today's tickets.

       

      I'm not very well versed in SQL.  And, the code recommendations I have seen on the Internet are giving me errors.

       

      Can you tell me what SQL statement I need to submit so that it only retrieves today's tickets?  (CreatedDate is today)

       

      Thanks,

      Sean

        • 1. Re: Pentaho DI SQL Question 2
          Supriya Ashtekar

          Hi Sean Connolly,

           

          Please try below query and also you can add additional fields as per your requirement

           

          SELECT Id FROM BMCServiceDesk__Incident__c WHERE CreatedDate = TODAY

           

          Thanks

          Supriya

          1 of 1 people found this helpful
          • 2. Re: Pentaho DI SQL Question 2
            Sean Connolly

            Perfect!  Thanks

             

            Are there any other keywords similar to TODAY?  Something like LASTMONTH, LASTWEEK, THISWEEK?

             

            Sean

            • 3. Re: Pentaho DI SQL Question 2
              Neena Ahirrao Guha

              Hi Sean Connolly,

               

              Please go thorough following link, for similar types of keywords -

               

              https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_dateformats.htm

               

              Thanks,

              Neena

              1 of 1 people found this helpful
              • 4. Re: Pentaho DI SQL Question 2
                Supriya Ashtekar

                Hi

                 

                There are keywords related to this, please find

                 

                Date LiteralRangeExample
                YESTERDAYStarts 00:00:00 the day before and continues for 24 hours.SELECT Id FROM Account WHERE CreatedDate = YESTERDAY
                TODAYStarts 00:00:00 of the current day and continues for 24 hours.SELECT Id FROM Account WHERE CreatedDate > TODAY
                TOMORROW Starts 00:00:00 after the current day and continues for 24 hours.SELECT Id FROM Opportunity WHERE CloseDate = TOMORROW
                LAST_WEEKStarts 00:00:00 on the first day of the week before the most recent first day of the week and continues for seven full days. Your locale determines the first day of the week.SELECT Id FROM Account WHERE CreatedDate > LAST_WEEK
                THIS_WEEKStarts 00:00:00 on the most recent first day of the week before the current day and continues for seven full days. Your locale determines the first day of the week.SELECT Id FROM Account WHERE CreatedDate < THIS_WEEK
                NEXT_WEEKStarts 00:00:00 on the most recent first day of the week after the current day and continues for seven full days. Your locale determines the first day of the week.SELECT Id FROM Opportunity WHERE CloseDate = NEXT_WEEK
                LAST_MONTHStarts 00:00:00 on the first day of the month before the current day and continues for all the days of that month.SELECT Id FROM Opportunity WHERE CloseDate > LAST_MONTH
                THIS_MONTHStarts 00:00:00 on the first day of the month that the current day is in and continues for all the days of that month.SELECT Id FROM Account WHERE CreatedDate < THIS_MONTH
                NEXT_MONTHStarts 00:00:00 on the first day of the month after the month that the current day is in and continues for all the days of that month.SELECT Id FROM Opportunity WHERE CloseDate = NEXT_MONTH
                LAST_90_DAYSStarts 00:00:00 of the current day and continues for the past 90 days.SELECT Id FROM Account WHERE CreatedDate = LAST_90_DAYS
                NEXT_90_DAYSStarts 00:00:00 of the current day and continues for the next 90 days.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_90_DAYS
                LAST_N_DAYS:nFor the number n provided, starts 00:00:00 of the current day and continues for the past n days.SELECT Id FROM Account WHERE CreatedDate = LAST_N_DAYS:365
                NEXT_N_DAYS:nFor the number n provided, starts 00:00:00 of the current day and continues for the next n days.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_DAYS:15
                NEXT_N_WEEKS:nFor the number n provided, starts 00:00:00 of the first day of the next week and continues for the next n weeks.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_WEEKS:4
                LAST_N_WEEKS:nFor the number n provided, starts 00:00:00 of the last day of the previous week and continues for the past n weeks.SELECT Id FROM Account WHERE CreatedDate = LAST_N_WEEKS:52
                NEXT_N_MONTHS:nFor the number n provided, starts 00:00:00 of the first day of the next month and continues for the next n months.SELECT Id FROM Opportunity WHERE CloseDate > NEXT_N_MONTHS:2
                LAST_N_MONTHS:nFor the number n provided, starts 00:00:00 of the last day of the previous month and continues for the past n  months.SELECT Id FROM Account WHERE CreatedDate = LAST_N_MONTHS:12
                THIS_QUARTERStarts 00:00:00 of the current quarter and continues to the end of the current quarter.SELECT Id FROM Account WHERE CreatedDate = THIS_QUARTER
                LAST_QUARTERStarts 00:00:00 of the previous quarter and continues to the end of that quarter.SELECT Id FROM Account WHERE CreatedDate > LAST_QUARTER
                NEXT_QUARTERStarts 00:00:00 of the next quarter and continues to the end of that quarter.SELECT Id FROM Account WHERE CreatedDate < NEXT_QUARTER
                NEXT_N_QUARTERS:nStarts 00:00:00 of the next quarter and continues to the end of the  nth quarter.SELECT Id FROM Account WHERE CreatedDate < NEXT_N_QUARTERS:2
                LAST_N_QUARTERS:nStarts 00:00:00 of the previous quarter and continues to the end of the previous nth quarter.SELECT Id FROM Account WHERE CreatedDate > LAST_N_QUARTERS:2
                THIS_YEARStarts 00:00:00 on January 1 of the current year and continues through the end of December 31 of the current year.SELECT Id FROM Opportunity WHERE CloseDate = THIS_YEAR
                LAST_YEARStarts 00:00:00 on January 1 of the previous year and continues through the end of December 31 of that year.SELECT Id FROM Opportunity WHERE CloseDate > LAST_YEAR
                NEXT_YEARStarts 00:00:00 on January 1 of the following year and continues through the end of December 31 of that year.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_YEAR
                NEXT_N_YEARS:nStarts 00:00:00 on January 1 of the following year and continues through the end of December 31 of the nth year.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_YEARS:5
                LAST_N_YEARS:nStarts 00:00:00 on January 1 of the previous year and continues through the end of December 31 of the previous nth year.SELECT Id FROM Opportunity WHERE CloseDate > LAST_N_YEARS:5
                THIS_FISCAL_QUARTERStarts 00:00:00 on the first day of the current fiscal quarter and continues through the end of the last day of the fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate = THIS_FISCAL_QUARTER
                LAST_FISCAL_QUARTER Starts 00:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate > LAST_FISCAL_QUARTER
                NEXT_FISCAL_QUARTERStarts 00:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of that fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate < NEXT_FISCAL_QUARTER
                NEXT_N_FISCAL_QUARTERS:nStarts 00:00:00 on the first day of the next fiscal quarter and continues through the end of the last day of the nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate < NEXT_N_FISCAL_QUARTERS:6
                LAST_N_FISCAL_QUARTERS:nStarts 00:00:00 on the first day of the last fiscal quarter and continues through the end of the last day of the previous nth fiscal quarter. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Account WHERE CreatedDate > LAST_N_FISCAL_QUARTERS:6
                THIS_FISCAL_YEAR Starts 00:00:00 on the first day of the current fiscal year and continues through the end of the last day of the fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate = THIS_FISCAL_YEAR
                LAST_FISCAL_YEAR Starts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate > LAST_FISCAL_YEAR
                NEXT_FISCAL_YEAR Starts 00:00:00 on the first day of the next fiscal year and continues through the end of the last day of that fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_FISCAL_YEAR
                NEXT_N_FISCAL_YEARS:n Starts 00:00:00 on the first day of the next fiscal year and continues through the end of the last day of the nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDate < NEXT_N_FISCAL_YEARS:3
                LAST_N_FISCAL_YEARS:n Starts 00:00:00 on the first day of the last fiscal year and continues through the end of the last day of the previous nth fiscal year. The fiscal year is defined on the Fiscal Year page in Setup.SELECT Id FROM Opportunity WHERE CloseDat

                 

                For more information you can refer below link

                Salesforce Developers

                 

                Thanks

                Supriya

                1 of 1 people found this helpful
                • 5. Re: Pentaho DI SQL Question 2
                  Sean Connolly

                  Thanks Supriya and Neena!  This will be very helpful going forward.

                   

                  Sean