We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Help with a SQL Statement - returning results in every region

Medium Priority
203 Views
Last Modified: 2012-12-13
Here is a usp built to gather data for a VS report

The study I am running it against has 22 invoices. Yet I get 88 results (one for each region). Something is a miss.

lil' help

      SELECT C.CompanyGuid, FB.StudyNumb, FB.Transnumb, C.CompanyName AS ClientName,
            C.Addr1 AS ClientAdd1, C.Addr2 AS ClientAdd2, C.City AS ClientCity,
            C.State AS ClientState, C.Zip AS ClientZip, C.PhoneVoice, C.PhoneFAX,
            FBD.ShipperName, FBD.OriginCity, FBD.OriginState,
            FBD.OriginZip, FB.PRONumber, FBD.BillLaddingNo,
            FB.InvoiceDate, FBD.ShipDateActual, FBD.MoveType,
            FBD.ConsigneeName, FBD.DestCity, FBD.DestState,
            FBD.DestZip, FBD.DestCountry, FBD.AuditedErrorAmt, FBD.ActualWght,
            FBD.AsWght, FBD.GrossBilled, FBD.NetBilledAmt,
            FB.TransNumb, CA.CompanyName AS CarrierName, FB.FlatChargeAmt,
            FB.CaseCount, FBD.RatePerCWT, FBD.SeqNo,
            FBD.AcctCode1, FBD.AcctCode2, FBD.AcctCode3,
            FBD.MiscCharge, ST.StudyTotalActualWght, ST.StudyTranCount,
            FBD.CompanyGuidClient,
            -- compute here or use parameter/constant
            /*
            case when ST.StudyAverageWght > 4999.00 then 0.285
                  when ST.StudyAverageWght > 1999 then 0.285
                  when ST.StudyAverageWght > 1199 then 0.285
                  when ST.StudyAverageWght > 799 then 0.285
                  when ST.StudyAverageWght > 399 then 0.285
                  else 0.285 end as FlatFee,
            */
             ST.GeoRegion,
            fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) as FeeRate,
            case when ST.StudyTotalActualWght * fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) < 99 then 100
                  else ST.StudyTotalActualWght * fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) end as AdjGrossAmt,
            case when ST.StudyTotalActualWght * fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) < 99 then 100
                  else ST.StudyTotalActualWght * fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) end as AdjGrossAmt,

            -- [AdjGrossAmt] + V.MiscCharge AS AdjNetBilled,
            case when ST.StudyTotalActualWght * fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) < 99 then 100
                  else ST.StudyTotalActualWght * fmidba.fnCarrierActivityFeeRate(ST.GeoRegion, ST.StudyAverageWght) end  + FBD.MiscCharge as AdjNetBilled
      FROM dbo.PreAudTranHdr as FB (nolock)
            JOIN dbo.PreAudTranDtl as FBD (nolock) ON FB.FBGuid = FBD.FBGuid
            JOIN dbo.Client as C (nolock) on FB.CompanyGuidClient = C.CompanyGuid
            JOIN dbo.Carrier as CA ON FB.CompanyGuidCarr = CA.CompanyGuid
            -- Summary totals across each study
            join (
                        Select H.CompanyGuidClient, H.StudyNumb,
                              sum(H.ActualWght) as StudyTotalActualWght,
                              count(*) as StudyTranCount,
                              sum(H.ActualWght) / count(*) as StudyAverageWght,
                              R.GeoRegion
                        from dbo.PreAudTranHdr as H (nolock)
                              join (
                                    select D.FBGuid,
                                          case when D.OriginState = 'BC' or D.DestState = 'BC' then 'BC'
                                                when D.OriginState = 'ON' or D.DestState = 'ON' then 'ON'
                                                when D.OriginState = 'QC' or D.DestState = 'QC' then 'QC'
                                                else 'US' end as GeoRegion
                                    from PreAudTranDtl as D (nolock)
                                          join PreAudTranHdr as H (nolock) on D.FBGuid = H.FBGuid
                                    where H.CompanyGuidClient = '1FF6E500-E0A1-4F89-AA24-80D307030E7D'
                                          and H.StudyNumb = '20110325'
                                    Group By D.FBGuid,
                                          case when D.OriginState = 'BC' or D.DestState = 'BC' then 'BC'
                                                when D.OriginState = 'ON' or D.DestState = 'ON' then 'ON'
                                                when D.OriginState = 'QC' or D.DestState = 'QC' then 'QC'
                                                else 'US' end
                              ) as R on H.FBGuid = R.FBGuid
                        where H.CompanyGuidClient = '1FF6E500-E0A1-4F89-AA24-80D307030E7D'
                              and H.StudyNumb = '20110325'
                        Group by H.COmpanyGuidCLient, H.StudyNumb, R.GeoRegion
                  ) as ST on FB.CompanyGuidClient = ST.CompanyGuidClient and FB.StudyNumb = ST.StudyNumb
      WHERE FB.CompanyGuidClient = '1FF6E500-E0A1-4F89-AA24-80D307030E7D'
                  and FB.StudyNumb = '20110325'
      -- Order by for the report
      Order by ST.GeoRegion, FBD.AcctCode1, CA.CompanyName, C.CompanyName, fb.transnumb


And here's the fn it uses
ALTER function [fmidba].[fnCarrierActivityFeeRate]
(
      @GeoRegion char(2),
      @AverageWeight decimal(18,2)
)
Returns decimal(18,2)
as
begin
      declare @Rate decimal(18,2)
      if @GeoRegion = 'BC'
            set @Rate = 0.30

      else if @GeoRegion = 'ON'
            set @Rate = 0.40

      else if @GeoRegion = 'QC'
            set @Rate = 0.50

--      04/20/2011 add if for US Region
--      04/20/2011       else
      else -- if @GeoRegion = 'US'
      begin
            if @AverageWeight >= 5000            set @Rate = 0.18
            else if @AverageWeight >= 2000      set @Rate = 0.20
            else if @AverageWeight >= 1200      set @Rate = 0.22
            else if @AverageWeight >=  800      set @Rate = 0.25
            else if @AverageWeight >=  300      set @Rate = 0.31
            else set @Rate = 0.0
      end

      return @Rate;
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Commented:
It's hare to say without looking at your source data - what you should do though is comment out all of the SELECT columns. And then all of the joins except for the FROM and WHERE clause. Then just do a SELECT COUNT and each time you run it - uncomment one of the joins to see which one is giving you more than what you want.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.