10 Replies Latest reply: Mar 4, 2010 2:13 AM by 691371 RSS

    Date Function - LAST WEEK

    623238
      I am trying to run a report that always shows the "last full week" Sun-Sat no matter what day of the week you run it. I have tried to apply the CASE statement to my currently "rolling past week" but still get an error message when running.

      Can you please review

      CASE
      When DayofWeek(Current_Date) = 1 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-8,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-1,(CURRENT_DATE))
      When DayofWeek(Current_Date) = 2 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-9,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-2,(CURRENT_DATE))
      When DayofWeek(Current_Date) = 3 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-10,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-3,(CURRENT_DATE))
      When DayofWeek(Current_Date) = 4 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-11,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-4,(CURRENT_DATE))
      When DayofWeek(Current_Date) = 5 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-12,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-5,(CURRENT_DATE))
      When DayofWeek(Current_Date) = 6 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-13,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-6,(CURRENT_DATE))
      When DayofWeek(Current_Date) = 7 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-14,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-7,(CURRENT_DATE))
      END

      and Advise...your prompt assistance is most appreciated.

      Thanks Debbie
        • 1. Re: Date Function - LAST WEEK
          691371
          what is the error that you are getting. is it a syntax error or are you getting erroneous data.
          • 2. Re: Date Function - LAST WEEK
            623238
            I am getting a View Display Error


            Error getting drill information: SELECT Account.Owner saw_0, Account."Account ID" saw_1, Account."Account Name" saw_2, Account."Account Type" saw_3, Activity.Type saw_4, Activity."Activity ID" saw_5, Date.Date saw_6 FROM "Activity Analysis" WHERE (CASE When DayofWeek(Current_Date) = 1 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-8,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-1,(CURRENT_DATE)) When DayofWeek(Current_Date) = 2 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-9,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-2,(CURRENT_DATE)) When DayofWeek(Current_Date) = 3 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-10,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-3,(CURRENT_DATE)) When DayofWeek(Current_Date) = 4 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-11,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-4,(CURRENT_DATE)) When DayofWeek(Current_Date) = 5 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-12,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-5,(CURRENT_DATE)) When DayofWeek(Current_Date) = 6 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-13,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-6,(CURRENT_DATE)) When DayofWeek(Current_Date) = 7 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-14,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-7,(CURRENT_DATE)) END) AND (Account."Account Type" = 'Prospect')

            Thanks for your assistance.
            • 3. Re: Date Function - LAST WEEK
              alexn (crmnow)
              Deb,

              You're missing your ELSE END in your formula.

              Try this

              CASE
              When DayofWeek(Current_Date) = 1 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-8,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-1,(CURRENT_DATE))
              When DayofWeek(Current_Date) = 2 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-9,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-2,(CURRENT_DATE))
              When DayofWeek(Current_Date) = 3 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-10,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-3,(CURRENT_DATE))
              When DayofWeek(Current_Date) = 4 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-11,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-4,(CURRENT_DATE))
              When DayofWeek(Current_Date) = 5 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-12,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-5,(CURRENT_DATE))
              When DayofWeek(Current_Date) = 6 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-13,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-6,(CURRENT_DATE))
              ELSE
              Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-14,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-7,(CURRENT_DATE))
              END
              • 4. Re: Date Function - LAST WEEK
                623238
                Thanks....

                I am still getting an error.

                Error getting drill information: SELECT Account.Owner saw_0, Account."Account ID" saw_1, Account."Account Name" saw_2, Account."Account Type" saw_3, Activity.Type saw_4, Activity."Activity ID" saw_5, Date.Date saw_6 FROM "Activity Analysis" WHERE (CASE When DayofWeek(Current_Date) = 1 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-8,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-1,(CURRENT_DATE)) When DayofWeek(Current_Date) = 2 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-9,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-2,(CURRENT_DATE)) When DayofWeek(Current_Date) = 3 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-10,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-3,(CURRENT_DATE)) When DayofWeek(Current_Date) = 4 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-11,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-4,(CURRENT_DATE)) When DayofWeek(Current_Date) = 5 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-12,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-5,(CURRENT_DATE)) When DayofWeek(Current_Date) = 6 Then Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-13,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-6,(CURRENT_DATE)) Else Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-14,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-7,(CURRENT_DATE)) END) AND (Account."Account Type" = 'Prospect')

                Thanks
                • 5. Re: Date Function - LAST WEEK
                  691371
                  Remove AND (Account."Account Type" = 'Prospect') from the ELSE clause and run it
                  • 6. Re: Date Function - LAST WEEK
                    623238
                    View Display Error
                    Error getting drill information: SELECT Account.Owner saw_0, Account."Account ID" saw_1, Account."Account Name" saw_2, Account."Account Type" saw_3, Activity.Type saw_4, Activity."Activity ID" saw_5, Date.Date saw_6 FROM "Activity Analysis" WHERE CASE WHEN DayofWeek(Current_Date) = 1 THEN Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-8,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-1,(CURRENT_DATE)) WHEN DayofWeek(Current_Date) = 2 THEN Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-9,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-2,(CURRENT_DATE)) WHEN DayofWeek(Current_Date) = 3 THEN Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-10,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-3,(CURRENT_DATE)) WHEN DayofWeek(Current_Date) = 4 THEN Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-11,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-4,(CURRENT_DATE)) WHEN DayofWeek(Current_Date) = 5 THEN Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-12,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-5,(CURRENT_DATE)) WHEN DayofWeek(Current_Date) = 6 THEN Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-13,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-6,(CURRENT_DATE)) ELSE Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-14,(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-7,(CURRENT_DATE)) END
                    Sorry still get an error
                    • 7. Re: Date Function - LAST WEEK
                      623238
                      Hi Max,

                      I hate to say it but I still can't get past the error. Do you any other thoughts? Mike are you out there?

                      Thanks
                      • 8. Re: Date Function - LAST WEEK
                        691371
                        I am afraid but your Case When statement is wrong.
                        Generally in the THEN clause we directly put the expression and not a condition on any field
                        i.e. in your case it should have been CASE WHEN DayofWeek(Current_Date) = 1 THEN TIMESTAMP.....
                        and not Date.Date BETWEEN....
                        so you need to come up with some alternative as the current logic won't work.

                        What exactly is the requirement. Is it that when run on any day(say Tuesday) then the report should show from Mon till Sun of the previous week or Tues(last week) till Tues(present day on which report is run)
                        • 9. Re: Date Function - LAST WEEK
                          623238
                          Hi Max,

                          My requirement is the first...no matter which day in the following week, I would like a report that is for the previous week Sun-Sat....i have the latter...a rolling week all set. I appreciate your help.

                          Debbie
                          • 10. Re: Date Function - LAST WEEK
                            691371
                            try out one thing. perhaps it might work...
                            remove the case when condition that you were using. build a filter on the date field (i.e. Fx being Date.Date)
                            select advanced, SQL results and write something like this in the dialog box:

                            Date.Date BETWEEN TIMESTAMPADD(SQL_TSI_DAY,-(6 + CAST(DAYOFWEEK(CURRENT_DATE) AS INT)),(CURRENT_DATE)) AND TIMESTAMPADD(SQL_TSI_DAY,-(DAYOFWEEK(CURRENT_DATE)),(CURRENT_DATE))

                            This condition should filter your report for previous week, irrespective of any day that you run the report.