5 Replies Latest reply on Feb 20, 2018 1:46 PM by Dan Cory

    Grouping Date by Custom Hours Leads to Long SQL

    Dogan Demir

      Hello all,

       

      I have a student activity data set that I'm trying to group into class/break-time periods. The periods are 50 min + 5 min break each. Based on the advice on this forum, I used IF/ELSEIF statements. While this works up to about halfway, in its entirety it leads to an extremely long query (about 400K characters) which breaks Amazon Athena DB (200K limit). Any suggestions on how to improve this would be greatly appreciated. Thanks!

       

      IF ([Play Begin Hour] == 7 AND [Play Begin Minute] <= 59) THEN '7:10-8:00a'

      ELSEIF ([Play Begin Hour] == 8) AND [Play Begin Minute] < 50) THEN '8:00-8:50a'

      ELSEIF ([Play Begin Hour] == 8 AND [Play Begin Minute] < 55) THEN '8:50-8:55a'

      ELSEIF (([Play Begin Hour] == 8 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 9 AND [Play Begin Minute] < 45)) THEN '8:55-9:45a'

      ELSEIF ([Play Begin Hour] == 9 AND [Play Begin Minute] < 50) THEN '9:45-9:50a'

      ELSEIF (([Play Begin Hour] == 9 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 10 AND [Play Begin Minute] < 40)) THEN '9:50-10:40a'

      ELSEIF ([Play Begin Hour] == 10 AND [Play Begin Minute] < 45) THEN '10:40-10:45a'

      ELSEIF (([Play Begin Hour] == 10 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 11 AND [Play Begin Minute] < 35)) THEN '10:45-11:35a'

      ELSEIF ([Play Begin Hour] == 11 AND [Play Begin Minute] < 40) THEN '11:35-11:40a'

      ELSEIF (([Play Begin Hour] == 11 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 12 AND [Play Begin Minute] < 30)) THEN '11:40-12:30p'

      ELSEIF ([Play Begin Hour] == 12 AND [Play Begin Minute] < 35) THEN '12:30-12:35p'

      ELSEIF (([Play Begin Hour] == 12 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 13 AND [Play Begin Minute] < 25)) THEN '12:35-1:25p'

      ELSEIF ([Play Begin Hour] == 13 AND [Play Begin Minute] < 30) THEN '1:25-1:30p'

      ELSEIF (([Play Begin Hour] == 13 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 14 AND [Play Begin Minute] < 20)) THEN '1:30-2:20p'

      ELSEIF ([Play Begin Hour] == 14 AND [Play Begin Minute] < 25) THEN '2:20-2:25p'

      ELSEIF (([Play Begin Hour] == 14 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 15 AND [Play Begin Minute] < 15)) THEN '2:25-3:15p'

      ELSEIF (([Play Begin Hour] == 15 AND [Play Begin Minute] <= 59) OR ([Play Begin Hour] == 16 AND [Play Begin Minute] < 05)) THEN '3:15-4:05p'

      ELSE '0' END

        • 1. Re: Grouping Date by Custom Hours Leads to Long SQL
          Jacob Goffin

          Hey Dogan,

           

          I'm not sure how to simplify the Tableau calculation, but could you instead create a reference table of all these time periods and join it back to the original data set? So if you had a new table with a structure like this:

           

          Time PeriodStart TimeEnd Time
          7:10-8:00a7:108:00
          8:00-8:50a8:008:50
          8:50-8:50a8:508:55
          etc.etc.etc.

           

          you could join it to your activity data on two join conditions:

           

          1) Play Time >= Start Time

          2) Play Time < End Time

          • 2. Re: Grouping Date by Custom Hours Leads to Long SQL
            Dogan Demir

            How would I go about implementing this on Tableau (with mixing date times/timestamps and all)? My student database is a list of timestamps per student (student location per minute) and I calculate a field called Play Begin Date using

            {FIXED [User Id] : MIN([Time])}

            • 3. Re: Grouping Date by Custom Hours Leads to Long SQL
              Dan Cory

              You could also compute the time as [Play Begin Hour]*60+[Play Begin Minute]. Then you have many options. You could use a group, which Tableau would turn into a temporary table. Or you could use somewhat simpler calculations but the overall expression might be same length.

               

              In theory you could try to compute the period, but it seems you don't have a 5 minute gap between the 7:10-8:00 and 8:00-8:50 or between 2:25-3:15 and 3:15-4:05. That makes that messier.

               

               

              If [Play Begin Hour] and [Play Begin Minute] actually came from a single field called [Play Begin], then stick with the original field and use ranges of times.

               

              Dan

              • 4. Re: Grouping Date by Custom Hours Leads to Long SQL
                Dogan Demir

                Dan, [Play Begin] is a date that does exist - that's how I calculate the hour and minute. What do you mean by?

                 

                If [Play Begin Hour] and [Play Begin Minute] actually came from a single field called [Play Begin], then stick with the original field and use ranges of times.

                • 5. Re: Grouping Date by Custom Hours Leads to Long SQL
                  Dan Cory

                  Unfortunately Tableau doesn't have a specific function for pulling the time out of a datetime field. You can define [Play Begin Time] as FLOAT([Play Begin])-INT([Play Begin]) which gives you a number between 0 and 24.

                  Then you can compare that:

                  IF [Play Begin Time]<8/24 THEN '7:10-8:00a'

                  ELSEIF [Play Begin Time]<8/24+50/24/60 THEN '8:00-8:50a'

                  ELSEIF [Play Begin Time]<8/24+55/24/60 THEN '8:50-8:55a'

                  ELSEIF [Play Begin Time]<9/24+45/24/60 THEN '8:55-9:45a'

                  etc

                   

                  The other option is to use minutes. Define [Play Begin Minute of Day] as HOUR([Play Begin])*60+MINUTE([Play Begin]). Then you can compare that:

                  IF [Play Begin Time]<8*60 THEN '7:10-8:00a'

                  ELSEIF [Play Begin Time]<8*60+50 THEN '8:00-8:50a'

                  ELSEIF [Play Begin Time]<8*60+55 THEN '8:50-8:55a'

                  ELSEIF [Play Begin Time]<9*60+45 THEN '8:55-9:45a'

                  etc