SmallMoney + Drops Decimal

  • 15 years ago

    I'm running SQL Server 2000 enterprise edition (sp 4) on windows server 2000 (sp 4).


    When I run the below stp, jobid 1 and jobid 2 will have a fee of 32, when in the table that I'm pulling the fee from has a fee of 32.50 for both jobs.  However I could run the stp again, and jobid 1 will now have a fee of 32.50 but jobid 2 will still have 32.  I could run the stp again and jobid 1 will now be back to 32 and jobid 2 would be 32.50.  


    It seems to drop the decimal when it feels like it.  Any thoughts?


    Here's the stp if anyone wants to take a look:



    Code:

    ALTER PROCEDURE stpGetInvoices
       @CaseStatus
    Closed BIGINT, --this will always be 133
       @ThisRundt VARCHAR(50), --this will always be equalled to getdate()
       @Class
    nm VARCHAR(50),--this will always be 'st. louis'
       @FeeGroupClient BIGINT,--this will always be 168
       @FeeGroup
    ProcessServer BIGINT,--this will always be 169
       @ParentTypeCompany BIGINT,--this will always be 11
       @ParentType
    Person BIGINT,--this will always be 12
       @ServiceLineProcessServing BIGINT,--this will always be 1
       @ServiceLine_Investigative BIGINT--this will always be 2
    AS


    DECLARE @Personvv BIGINT
    DECLARE @Company
    vv BIGINT


    SELECT @Personvv = (SELECT ValidValueid FROM tblValidValue WHERE ValidValuenm = 'Person')
    SELECT @Companyvv = (SELECT ValidValueid FROM tblValidValue WHERE ValidValuenm = 'Company')


    -- Commented out because it should not be needed, if problems occur, uncomment the next line first
    -- EXEC stpRollupInvoiceFees @CaseStatusClosed, @FeeGroupClient, @FeeGroupProcessServer


    SELECT
    ,
    Plaintiff_desc + ' Vs. ' + Defendant_desc as CaseCaption,
    CASE ServiceLine_vv
       WHEN @ServiceLine_Process_Serving THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE LTRIM(RTRIM(First_nm)) + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 'No Process Server' END)
       ELSE ''
    END AS ProcessServer,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN 'BILL'
       ELSE 'INVOICE'
    END AS TrnsType,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN 'Accounts Payable'
       ELSE 'Accounts Receivable'
    END AS TrnsAccnt,
    FeeDescriptionVV.ValidValue_desc as SPLAccnt,
    FeeDescriptionVV.ValidValue_nm as SPLMemo,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE RTRIM(First_nm) + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 'No Process Server' END)
       ELSE (SELECT CASE Client_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE First_nm + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = Client_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = Client_pk) ELSE '' END)
    END AS FeeGroup,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE RTRIM(First_nm) + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 'No Process Server' END)
       ELSE (SELECT CASE Client_vv WHEN @ParentType_Person THEN (SELECT CASE First_nm WHEN '' THEN Last_nm ELSE First_nm + ' ' + Last_nm END as Full_nm FROM tbl_Person WHERE Person_id = Client_pk) WHEN @ParentType_Company THEN (SELECT Company_nm FROM tbl_Company WHERE Company_id = Client_pk) ELSE '' END)
    END AS InvoiceToName,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN (SELECT CASE ProcessServer_vv WHEN @ParentType_Person THEN (SELECT QuickBooks_flg FROM tbl_Person WHERE Person_id = ProcessServer_pk) WHEN @ParentType_Company THEN (SELECT QuickBooks_flg FROM tbl_Company WHERE Company_id = ProcessServer_pk) ELSE 0 END)
       ELSE (SELECT CASE Client_vv WHEN @ParentType_Person THEN (SELECT QuickBooks_flg FROM tbl_Person WHERE Person_id = Client_pk) WHEN @ParentType_Company THEN (SELECT QuickBooks_flg FROM tbl_Company WHERE Company_id = Client_pk) ELSE 0 END)
    END AS FeeGroup_PSClientBoth,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN convert(decimal(10,2),tbl_job.ProcessServer_amt)
       ELSE convert(decimal(10,2),tbl_job.Fee_amt)
    END AS Amount,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN (SELECT Address_1 FROM tbl_Address WHERE Parent_vv = ProcessServer_vv and Parent_pk = ProcessServer_pk AND PrimaryAddress_flg = 1)
       ELSE (SELECT Address_1 FROM tbl_Address WHERE Parent_vv = Client_vv and Parent_pk = Client_pk AND PrimaryAddress_flg = 1)
    END AS Address1,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN (SELECT Address_2 FROM tbl_Address WHERE Parent_vv = ProcessServer_vv and Parent_pk = ProcessServer_pk AND PrimaryAddress_flg = 1)
       ELSE (SELECT Address_2 FROM tbl_Address WHERE Parent_vv = Client_vv and Parent_pk = Client_pk AND PrimaryAddress_flg = 1)
    END AS Address2,
    CASE FeeGroup_vv
       WHEN @FeeGroup_ProcessServer THEN (SELECT City_nm + ', ' + State_cd + ' ' + Zip_cd FROM tbl_Address WHERE Parent_vv = ProcessServer_vv and Parent_pk = ProcessServer_pk AND PrimaryAddress_flg = 1)
       ELSE (SELECT City_nm + ', ' + State_cd + ' ' + Zip_cd FROM tbl_Address WHERE Parent_vv = Client_vv and Parent_pk = Client_pk AND PrimaryAddress_flg = 1)
    END AS Address3,
    FeeDue_amt
    FeeDueqty as TotalFee,
    @ThisRun
    dt as Rundt,
    FeeGroupVV.ValidValue
    nm as FeeGroupnm,
    FeeDescriptionVV.ValidValue
    nm as FeeDescription,
    ServiceLineVV.ValidValuenm as ServiceLine,
    FeeGroupVV.ValidValue
    id as FeeGroupid,
    FeeDescriptionVV.ValidValue
    id as FeeDescriptionid,
    ServiceLineVV.ValidValue
    id as ServiceLineid,
    JobNavigationVV.ValidValue
    id as JobNavigationid,
    case tbl
    jobgroup.filenum
       when '' then tbl
    jobgroup.casenum
       else tbl
    jobgroup.filenum
    end as TrnsMemo,
    CASE tbl
    Person.Lastnm
       WHEN 'Unassigned' THEN ''
       ELSE tbl
    Person.Firstnm + ' ' + tblPerson.Lastnm
    END AS Contact,
    (SELECT MAX(Invoice
    dt) FROM tblJob) AS LastRundt,
    dbo.ufgetReversedPersonCompanyName(Recipientvv, Recipientpk) Recipient,
    class
    nm as ClassNm
    FROM
    tbl
    ValidValue FeeGroupVV,
    tblValidValue FeeDescriptionVV,
    tbl
    ValidValue ServiceLineVV,
    tblValidValue JobNavigationVV,
    tbl
    ServiceType,
    tblJobGroup,
    tbl
    Person,
    tblJob
    LEFT OUTER JOIN tbl
    FeeDue ON tblFeeDue.Jobid = tblJob.Jobid
    WHERE
    tblJob.Invoicedt IS NULL AND
    tblJob.CaseStatusvv = @CaseStatusClosed AND
    tbl
    Job.QuickBooksflg = 1 AND
    tbl
    FeeDue.FeeGroupvv = FeeGroupVV.ValidValueid AND
    tblFeeDue.FeeDescriptionvv = FeeDescriptionVV.ValidValueid AND
    tbl
    Job.ServiceTypeid = tblServiceType.ServiceTypeid AND
    tbl
    ServiceType.ServiceLinevv = ServiceLineVV.ValidValueid AND
    tblJobGroup.CLientContactid =  tblPerson.Personid AND
    tblJob.JobGroupid = tblJobGroup.JobGroupid AND
    tblJob.JobNavigationvv = JobNavigationVV.ValidValueid
    ORDER BY
    tbl
    FeeDue.FeeGroupid,
    tbl
    Job.Job_id

Post a reply

No one has replied yet! Why not be the first?

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Hofstadter's Law: It always takes longer than you expect, even when you take into account Hofstadter's Law.”