RepSplit is an int.
Even if you declare it as a decimal field?
1 Like
Oh… so it grabs the underlying type… not the type from the calculated field.
1 Like
@Banderson That seems to have worked for the most part…I notice its rounding the values…can I stop it from doing that? Or is it the same problem where I need to convert everything to decimal?
Is your RepOrderTotal a decimal or an int?
My Rep Order total is an int
yeah, change it to decimal and see if that fixes it.
I would try converting everything to a decimal first, but if that doesn’t work, you can use cast to explicitly set the precision.
cast(yourfield as decimal(10,5))
Well deserved moniker. 
1 Like
I think that pretty much solved it!
I believe that is exactly what I needed. I gotta go back now and do some number verifications. I’m assuming I can take this and throw it into another query as a pivot right?
You guys were doing so well! keep working at it. I can pitch in here or there, but I don’t have the time to work up example for you.
Give it a shot and post up when you get stuck.
@Banderson No problem thanks for taking the time anyways!
I will keep working at the pivot issue and let you know.
Bit of a side question while I’m here…Is there a way to go back into closed orders to fix commission/other data mistakes?
You can re-open the order, but generally, I’m not sure I would do that. But I’m not super into the financials to know what kind of impact that could have.
Ok sounds good! I can’t seem to find out how to get the Subquery criteria to work to get OrderDate = current Year. I tried using the constant but it keeps giving me a bad SQL error.
There should be something for first day of the year in the BAQ special constants. This year is just and integer (2023) and the date field is a date, so they won’t match. You’ll have to make it greater than or equal to the first day of the year.
Ok I got that part, but I think I’ve done my pivot wrong…
select
[SubQuery3].[SalesRep_SalesRepCode] as [SalesRep_SalesRepCode],
[SubQuery3].[SalesRep_Name] as [SalesRep_Name],
[SubQuery3].[SubQuery2_1] as [SubQuery2_1],
[SubQuery3].[SubQuery2_2] as [SubQuery2_2],
[SubQuery3].[SubQuery2_3] as [SubQuery2_3],
[SubQuery3].[SubQuery2_4] as [SubQuery2_4],
[SubQuery3].[SubQuery2_5] as [SubQuery2_5],
[SubQuery3].[SubQuery2_6] as [SubQuery2_6],
[SubQuery3].[SubQuery2_7] as [SubQuery2_7],
[SubQuery3].[SubQuery2_8] as [SubQuery2_8],
[SubQuery3].[SubQuery2_9] as [SubQuery2_9],
[SubQuery3].[SubQuery2_10] as [SubQuery2_10],
[SubQuery3].[SubQuery2_11] as [SubQuery2_11],
[SubQuery3].[SubQuery2_12] as [SubQuery2_12]
from (select
[SubQuery2].[SalesRep_SalesRepCode] as [SalesRep_SalesRepCode],
[SubQuery2].[SalesRep_Name] as [SalesRep_Name],
[SubQuery2].[1] as [SubQuery2_1],
[SubQuery2].[2] as [SubQuery2_2],
[SubQuery2].[3] as [SubQuery2_3],
[SubQuery2].[4] as [SubQuery2_4],
[SubQuery2].[5] as [SubQuery2_5],
[SubQuery2].[6] as [SubQuery2_6],
[SubQuery2].[7] as [SubQuery2_7],
[SubQuery2].[8] as [SubQuery2_8],
[SubQuery2].[9] as [SubQuery2_9],
[SubQuery2].[10] as [SubQuery2_10],
[SubQuery2].[11] as [SubQuery2_11],
[SubQuery2].[12] as [SubQuery2_12]
from (select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[SalesRep].[SalesRepCode] as [SalesRep_SalesRepCode],
[SalesRep].[Name] as [SalesRep_Name],
[OrderHed].[SalesRepList] as [OrderHed_SalesRepList],
[OrderHed].[RepSplit1] as [OrderHed_RepSplit1],
[OrderHed].[RepSplit2] as [OrderHed_RepSplit2],
[OrderHed].[RepSplit3] as [OrderHed_RepSplit3],
[OrderHed].[RepSplit4] as [OrderHed_RepSplit4],
[OrderHed].[RepSplit5] as [OrderHed_RepSplit5],
([Ice].entry(1, OrderHed.SalesRepList ,'~' )) as [Calculated_SalesRep1],
([Ice].entry(2, OrderHed.SalesRepList ,'~' )) as [Calculated_SalesRep2],
([Ice].entry(3, OrderHed.SalesRepList ,'~' )) as [Calculated_SalesRep3],
([Ice].entry(4, OrderHed.SalesRepList ,'~' )) as [Calculated_SalesRep4],
([Ice].entry(5, OrderHed.SalesRepList ,'~' )) as [Calculated_SalesRep5],
(case
when SalesRep.SalesRepCode = SalesRep1 then OrderHed.RepSplit1
when SalesRep.SalesRepCode = SalesRep2 then OrderHed.RepSplit2
when SalesRep.SalesRepCode = SalesRep3 then OrderHed.RepSplit3
when SalesRep.SalesRepCode = SalesRep4 then OrderHed.RepSplit4
when SalesRep.SalesRepCode = SalesRep5 then OrderHed.RepSplit5
else 0
end) as [Calculated_RepCommSplit],
[OrderHed].[DocOrderAmt] as [OrderHed_DocOrderAmt],
(OrderHed.DocOrderAmt * (RepCommSplitDec / 100)) as [Calculated_RepOrderTotal],
(convert(decimal, RepCommSplit)) as [Calculated_RepCommSplitDec],
(datepart(month,OrderHed.OrderDate)) as [Calculated_OrderMonth],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
(datepart(year,OrderHed.OrderDate)) as [Calculated_OrderYear]
from Erp.OrderHed as OrderHed
inner join Erp.SalesRep as SalesRep on
'~'+OrderHed.SalesRepList+'~' LIKE '%~'+SalesRep.SalesRepCode+'~%') SubQuery2_src
pivot
(sum(Calculated_RepOrderTotal)
for Calculated_OrderMonth in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]) )
as SubQuery2
where (SubQuery2.Calculated_OrderYear = Constants.Year)) as SubQuery3
What’s the problem?
