Post reply

Adding an extra column to show internal users

  • February 6, 2017 at 9:56 am

    #334792

    Hi,

    I need to differentiate when a user is internal or external.,

    So I have an existing query below. I would like to add a column called 'Internal Users'. I have a list of the internal users. It's okay to hard code this list.There are only five users.

    So in my new 'internal users' column I need the query to only add the user when it is in the internal user list. (when theya re a valid internal user - other wise empty for this column)
    Here is my query so far:

    SELECT
       [AUTHN] AS [Auth Number]
      ,convert(date, [AuthDate], 1) AS [Effective Date]
      ,convert(date, [ExpirationDate] , 1) AS [Expiration Date]
      ,convert(date, [DecisionDateTime] , 1)AS [Decision Date]
      ,[IPACD] AS [State]
      ,[Category]
      ,[PLCSV] AS [Place of Service]
      ,[Status] AS [Auth Status]
      ,[ReferredTo] AS [Referred To]
      ,[Facility]
      ,[ReferralType] AS [Referral Type]
      ,[InputUser] AS [Input User]
      ,convert(date, [ChgDateTime] , 1)AS [Change Date]
      ,[ReceivedDate] AS [Recieved Date]
      ,[DecisionBy] AS [Decision By]

    FROM [iTest2_Reportingsource].[dbo].[AUTHS]

  • February 6, 2017 at 10:00 am

    #1926910

    Use a CASE statemnt:
    SELECT
     [AUTHN] AS [Auth Number]
    ,convert(date, [AuthDate], 1) AS [Effective Date]
    ,convert(date, [ExpirationDate] , 1) AS [Expiration Date]
    ,convert(date, [DecisionDateTime] , 1)AS [Decision Date]
    ,[IPACD] AS [State]
    ,[Category]
    ,[PLCSV] AS [Place of Service]
    ,[Status] AS [Auth Status]
    ,[ReferredTo] AS [Referred To]
    ,[Facility]
    ,[ReferralType] AS [Referral Type]
    ,[InputUser] AS [Input User]
    ,convert(date, [ChgDateTime] , 1)AS [Change Date]
    ,[ReceivedDate] AS [Recieved Date]
    ,[DecisionBy] AS [Decision By],
      ,CASE WHEN [Your Internal User Logic] THEN 'Internal User' ELSE NULL END AS [Internal User]
    FROM [iTest2_Reportingsource].[dbo].[AUTHS]

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • February 6, 2017 at 10:04 am

    #1926911

    You would just OUTER join your query to the table of internal users.  The result would be that any external users would generate a NULL in the Users table, so just query for that.

    SELECT
     [AUTHN] AS [Auth Number]
    ,convert(date, [AuthDate], 1) AS [Effective Date]
    ,convert(date, [ExpirationDate] , 1) AS [Expiration Date]
    ,convert(date, [DecisionDateTime] , 1)AS [Decision Date]
    ,[IPACD] AS [State]
    ,[Category]
    ,[PLCSV] AS [Place of Service]
    ,[Status] AS [Auth Status]
    ,[ReferredTo] AS [Referred To]
    ,[Facility]
    ,[ReferralType] AS [Referral Type]
    ,[InputUser] AS [Input User]
    ,convert(date, [ChgDateTime] , 1)AS [Change Date]
    ,[ReceivedDate] AS [Recieved Date]
    ,[DecisionBy] AS [Decision By]
    CASE WHEN u.UserID IS NULL THEN 'External' ELSE 'Internal' END EmployeeType
      FROM [iTest2_Reportingsource].[dbo].[AUTHS] a
        LEFT JOIN dbo.[UserList] u ON a.AuthNumber = u.UserID

  • February 6, 2017 at 10:30 am

    #1926918

    very helpful..thanks guys!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply