1 Reply Latest reply on Oct 28, 2018 10:53 AM by Ken Flerlage

    Custom SQL as datasource from existing MSSQL

    Suzanne Parr

      I would really like to reuse the query some developers built for me earlier this year via MSSQL server management studio.

      I've tried changing all the @varmoyr  with still no results (a syntax error that leads nowhere).  I have many questions!

       

      (1) It seems to be common to use custom SQL to create a view in Tableau itself that you then query, due to performance issues. how do you do that?

      (2) how do I change the declare variable to a parameter?  I really don't want or need 10 years worth of monthly data bogging things up when I need the data connection

      (3) anyone see anything else in here that Tableau would not like? I read ORDER BY is not tolerated but I don't have one of those.  However I bet there are other thinks in here I shouldn't be using.

       

      Thanks in advance!

       

       

      DECLARE
      @Varmoyr smalldatetime

      SET
      @Varmoyr = '2017-12-01' ----please change date here

       

       

      Select
      @Varmoyr as [Date],B.GroupNum as [Group #],GroupName as [Group Name],

      1. B.VendorNum
        as [Agency #], VendorName as [Agency Name],
      2. B.AgentNum
        as [Agent #],B.AgentName as [Agent Name],

      BusinessSegment
      as [Business Segment],CommissionSched as [Commission schedule],

      1. B.Eligibles
        as [Eligibles],

      ISNULL(MedSubscrib,0)
      as [#Med Subs], ISNULL(MedMembers,0) as [# Med mbrs],

      ISNULL(DentSubscrib,0)
      as [#Dent Subs],ISNULL(DentMembers,0) as [#Dent Mbrs],

      PreScaledMedCommiss as [Med comm paid],PreScaledDentCommiss as [Dent comm paid],

      ISNULL(A.[Dental
      Bonus],0) as [Dent Bonus Paid],ISNULL(A.[Medical Bonus],0) as [Medical Bonus
      Paid],

      ISNULL(MedPremium,0)
      as [Med Premium],ISNULL(DentPremium,0) as [Dent Premium],

      CASE
      WHEN C.Groupnum is NULL then 'NO' else 'YES' end as [New Med Groups],

      CASE
      WHEN D.Groupnum is NULL then 'NO' else 'YES' end as [New Dental Groups],

      CASE
      WHEN ISNULL(MedMembers,0) >0  then 'YES' else 'NO' end as [Med Group],

      CASE
      WHEN ISNULL(DentMembers,0) > 0 then 'YES' else 'NO' end as [Dental Group]

       

      FROM

      (Select
      * from ViewMemRelaDetail where MMoYr = @Varmoyr )as B Left join

      (Select
      distinct(A.Groupnum),A.Vendornum,SUM(ISNULL([Dental_Bonus],0)) as 'Dental Bonus'
      ,SUM(ISNULL([Medical_Bonus],0)) as 'Medical Bonus'

      FROM

      (Select
      Groupnum,Vendornum,CASE WHEN BonusType = 'Dental New Member Bonus' Then
      ISNULL(Apportionment,0) end as 'Dental_Bonus' ,

      CASE
      WHEN BonusType <> 'Dental New Member Bonus' Then ISNULL(Apportionment,0)
      end   as 'Medical_Bonus'

      from
      tblbonusapportionment Where earnedmoyr = @Varmoyr) as A

      Group
      by Groupnum,vendornum ) as A on A.GroupNum = B.Groupnum and A.vendornum =
      B.Vendornum

      Left
      join

      (Select
      * from tblNewGroups where moyr = @Varmoyr) as C on A.GroupNum = C.Groupnum

      Left
      join


      (Select * from tblnewDentalGroups where moyr = @Varmoyr) as D on A.GroupNum =
      D.Groupnum

      Left
      join

      (Select
      distinct(vendornum),SUM([Dental Groups Count]) as [Dental Groups Count],

      SUM([Medical
      Groups count]) as [Medical Groups count]

      FROM

      (Select
      Distinct(Groupnum),Vendornum,case when  DentMembers>0 then 1 else 0
      end  as [Dental Groups Count],

      case
      when  medmembers>0 then 1 else 0 end as [Medical Groups count]


      FROM ViewMemRelaDetail where MMoYr = @Varmoyr Group by VendorNum
      ,DentMembers,MedMembers,GroupNum) as E


      Group by vendornum)


      as E on E.VendorNum = B.VendorNum