• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

Help with a SQL Statement - returning results in every region

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;
0
fmi_techsupport
Asked:
fmi_techsupport
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume some of the joins will result in the "duplication" of the output.
please read this article to understand and solve the issue: http://www.experts-exchange.com/A_3203.html
0
 
rmm2001Commented:
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.
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Tackle projects and never again get stuck behind a technical roadblock.
Join Now