3 Replies Latest reply on May 1, 2018 1:21 PM by Dan Gordon

    Pivot and Combine SQL Date Fields

    Dan Gordon

      Hello everyone,

       

      I have a problem I am hoping an experienced sql developer can assist me with.  I have an SQL table that has the following fields:

      AccountNumber

      QueryID

      QueryResponse

       

      I only want to query for 6 specific QueryIDs:

      'ED.DECADMD01' -- Attending Contact Date

      'ED.DECADMT01' -- Attending Contact Time

      'ED.D2ADMTPROV' -- Attending Contact Name

      'ARRA.EDD2AYN' -- Admit Y/N - (Query Response a 1 = 'Y' or 2 = 'N' or 3 = 'Obsv'

      'ED.D2ADMDATE' - Decision to Admit Date

      'ED.D2ADMTIME' -- Decision to Admit Time

       

      When you query these ID's you can return 6 records for each AccountNumber.  What I want to do is create a single record for each AccountNumber that has the following additional fields:

      AttendingContactDT  - where the QueryID = 'ED.DECADMD01' and 'ED.DECADMT01' then combine the Query Reponses for both QueryIds to create a DateTime field

      AttendingContacted - return the QueryResponse value where QueryID = 'ED.D2ADMTPROV'

      DecisionToAdmit - return the Query Response for when Query ID = 'ARRA.EDD2AYN' where 1= 'YES' or 2='No' or 3= 'Obsv'

      DecToAdmitDT -- where QueryID = 'ED.D2ADMDATE' and 'ED.D2ADMTIME' then combine the QueryReponses for both to create a DateTime field

       

      I have included a sample table of the raw data.  Any assistance on this is greatly appreciated. 

       

      Dan 

        • 1. Re: Pivot and Combine SQL Date Fields
          Jonathan Drummey

          Hi Dan,

           

          Is this Meditech by any chance?? My general way of handling this kind of "pivot/crosstab" operation in SQL with complex criteria is to use an aggregate query that would look something like this for MS SQL Server:

           

          SELECT [AccountNumber],

               MAX(CASE WHEN [QueryID] = 'ED.DECADMD01' THEN [QueryResponse] END) + ' ' + MAX(CASE WHEN [QueryID] = 'ED.DECADMT01' THEN [QueryResponse] END AS [AttendingContactDT],

               MAX(CASE WHEN [QueryID] = 'ED.D2ADMTPROV' THEN [QueryResponse] END) AS [AttendingContacted],

               MAX(CASE WHEN [QueryID] = 'ARRA.EDD2AYN' AND [QueryResponse] IN (1,2,3) THEN [QueryResponse] END) AS [DecisionToAdmit],

               MAX(CASE WHEN [QueryID] = 'ED.D2ADMDATE' THEN [QueryResponse] END) + ' ' + MAX(CASE WHEN [QueryID] = 'ED.D2ADMTIME' THEN [QueryResponse] END AS [DecToAdmitDT]

          FROM [table]

          GROUP BY [AccountNumber]

           

          A few notes:

          - for the two datetimes you could wrap everything on each line up to the AS inside a CAST() or CONVERT().

          - for the DecisionToAdmit text you'd get the most performance in Tableau by using Tableau aliases. If you really wanted text then I'd wrap the MAX in a CASE statement.

           

          Jonathan

          1 of 1 people found this helpful
          • 2. Re: Pivot and Combine SQL Date Fields
            mark.welsh

            Looks like Meditech to me! 

            • 3. Re: Pivot and Combine SQL Date Fields
              Dan Gordon

              This worked like a champ!  Thank you Jonathan!