7 Replies Latest reply on Mar 27, 2012 8:27 AM by Alex Kerin

    Time broken up by specific Shift schedule...

      Hi All,

       

      I am a week-old user of Tableau.  I need to start cranking reports for management and execs.  I was extracting flat files from various source tables, importing into MS Access and Oracle, using SQL queries to manipulate the data then exporting to Excel to build Pivot Tables and Pivot Charts.  I see with Tableau it can all be done easily. 

       

      My Problem:

      I have a query which breaks up a Date and Time Field to give me Shift breakdown e.g.

           7AM - 3PM is 1st Shift

           3PM - 11PM is 2nd Shift

           11PM-7PM is 3rd Shift

       

      In Access this is the query

      - MS Access -

      SELECT Qry00JoinDC_and_Loc.Request_ID, Qry00JoinDC_and_Loc.Event_Start_Date, Format([Qry00JoinDC_and_Loc].Event_Start_Date,"mm/dd/yyyy") AS Dt, Format([Qry00JoinDC_and_Loc].Event_Start_Date,"mm") AS Mnth, Format([Qry00JoinDC_and_Loc].Event_Start_Date,"dd") AS Dei, Format([Qry00JoinDC_and_Loc].Event_Start_Date,"Short Time") AS Tm, IIf(Format([Qry00JoinDC_and_Loc].Event_Start_Date,"h") In (23,0,1,2,3,4,5,6),"3rd Shift",IIf(Format([Qry00JoinDC_and_Loc].Event_Start_Date,"h") In (7,8,9,10,11,12,13,14),"1st Shift","2nd Shift")) AS Shift, Qry00JoinDC_and_Loc.Location, Qry00JoinDC_and_Loc.DC

      FROM Qry00JoinDC_and_Loc;

       

       

      In Oracle this is the Query

      - Oracle -

      select request_id, to_char(event_start_date, 'MM/DD/YYYY HH24:MI:SS') as ESD,

      trunc(event_start_date) AS dateOnly,

      to_char(event_start_date,'YYYY') AS yearOnly,

      to_char(event_start_date,'MM') AS MonthOnly,

      to_char(event_start_date,'DD') AS DayOnly,

      to_char(event_start_date,'HH24') AS HourOnly,

      -- to_char(event_start_date,'MI') AS MinOnly,

      -- to_char(event_start_date,'SS') AS SecOnly,

      decode(trunc((to_char(event_start_date,'HH24')+1)/8),  1,'1st',

                                                             2,'2nd',

                                                             '3rd') as "Shift",

      Location

      from nxsxr.stats_dtcbs

      order by to_char(event_start_date,'HH24');

       

      Can someone tell me how to do it in T?

       

      Thanks in advanced!


      -JA