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!
@Varmoyr = '2017-12-01' ----please change date here
@Varmoyr as [Date],B.GroupNum as [Group #],GroupName as [Group Name],
as [Agency #], VendorName as [Agency Name],
as [Agent #],B.AgentName as [Agent Name],
as [Business Segment],CommissionSched as [Commission schedule],
as [#Med Subs], ISNULL(MedMembers,0) as [# Med mbrs],
as [#Dent Subs],ISNULL(DentMembers,0) as [#Dent Mbrs],
PreScaledMedCommiss as [Med comm paid],PreScaledDentCommiss as [Dent comm paid],
Bonus],0) as [Dent Bonus Paid],ISNULL(A.[Medical Bonus],0) as [Medical Bonus
as [Med Premium],ISNULL(DentPremium,0) as [Dent Premium],
WHEN C.Groupnum is NULL then 'NO' else 'YES' end as [New Med Groups],
WHEN D.Groupnum is NULL then 'NO' else 'YES' end as [New Dental Groups],
WHEN ISNULL(MedMembers,0) >0 then 'YES' else 'NO' end as [Med Group],
WHEN ISNULL(DentMembers,0) > 0 then 'YES' else 'NO' end as [Dental Group]
* from ViewMemRelaDetail where MMoYr = @Varmoyr )as B Left join
distinct(A.Groupnum),A.Vendornum,SUM(ISNULL([Dental_Bonus],0)) as 'Dental Bonus'
,SUM(ISNULL([Medical_Bonus],0)) as 'Medical Bonus'
Groupnum,Vendornum,CASE WHEN BonusType = 'Dental New Member Bonus' Then
ISNULL(Apportionment,0) end as 'Dental_Bonus' ,
WHEN BonusType <> 'Dental New Member Bonus' Then ISNULL(Apportionment,0)
end as 'Medical_Bonus'
tblbonusapportionment Where earnedmoyr = @Varmoyr) as A
by Groupnum,vendornum ) as A on A.GroupNum = B.Groupnum and A.vendornum =
* from tblNewGroups where moyr = @Varmoyr) as C on A.GroupNum = C.Groupnum
(Select * from tblnewDentalGroups where moyr = @Varmoyr) as D on A.GroupNum =
distinct(vendornum),SUM([Dental Groups Count]) as [Dental Groups Count],
Groups count]) as [Medical Groups count]
Distinct(Groupnum),Vendornum,case when DentMembers>0 then 1 else 0
end as [Dental Groups Count],
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