SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
Register Now and get your question answered!
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 SQL SUM assistance please
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

3 Posts

Posted - 06/17/2014 :  08:25:24  Show Profile  Reply with Quote
Hello -

I’ve ran into a little snag and hoping someone will be able to assist. Basically what I have is a query that I’m trying to change to SUM column clmdet.cauth_total and have the SUM display in the CheckAmount column. The problem is that my totals are coming out way out of range and I’m not sure how to go about resolving it.

EXAMPLE what I’m trying to achieve: SUM up ComponentTotalCost and the total to show up in the CheckAmout column repedively.

ComponentTotalCost CheckAmount
250.00 845.00
320.00 845.00
50.00 845.00
225.50 845.00

Any assistance is appreciated!

SELECT DISTINCT 'PCS' AS TPACode, CASE WHEN LEN(deal.sdealer_number)>10
      AND LEFT(deal.sdealer_number,3)='DTD' THEN 'DTDG' + CONVERT(VARCHAR(6),RIGHT(LTRIM(RTRIM(deal.sdealer_number)),6))
ELSE deal.sdealer_number END AS DealerNumber, con.scontract_no AS AgreementNumber, NULL AS AgreementSuffix,
clm.sclaim_number AS ClaimNumber, clmdet.sjob_no AS ClaimSequenceNumber, 
ISNULL(CONVERT(VARCHAR(10),tcm.UniComponentCode),'UNMAPPED') AS ComponentCode, 
CASE clm.sclaim_status WHEN 'P' THEN 'C' WHEN 'O' THEN 'P' WHEN 'V' THEN 'N' WHEN 'D' THEN 'D' ELSE NULL END AS ClaimStatus, 
sjccc.scomplaint AS Complaint, CONVERT(CHAR(10),MIN(clmhist.dtcycle_date),101) AS BusinessDate, 
   /* Per BU, use the minimum dtcycle_date since there are lots of dates in the history table*/
CONVERT(CHAR(10),clm.dtro_received,101) AS ReportedDate, CONVERT(CHAR(10),ISNULL(clm.dtdate_loss_occurred,clm.dtro_received),101) AS FailedDate, 
clm.iodometer_at_time_of_loss AS FailedMileage, ssc.sservice_name AS RepairFacilityName, NULL AS FailedComponentFlag,
clmdet.ntax_percent AS TaxRate, cp.cauth_unit_cost AS PartCost, CASE cp.sdetail_Type WHEN'P' THEN cp.nauth_qty ELSE 0.00 END AS PartQuantity, 
CASE cp.sdetail_Type WHEN'L' THEN cp.nauth_qty ELSE 0.00 END AS LaborHours,
clmdet.clabor_rate AS LaborRate, CASE clmdet.sdetail_Type WHEN'P' THEN clmdet.ctax_amt_1 ELSE 0.00 END AS PartTotalTax, 
CASE clmdet.sdetail_Type WHEN'L' THEN clmdet.ctax_amt_1 ELSE 0.00 END AS LaborTotalTax,
  clmdet.cauth_total AS ComponentTotalCost 
CASE cst.sclaim_det_status_code WHEN 'PD' THEN 'C' ELSE 'N' END AS PaymentStatus,
--This column has 2 character values PD (paid), AP (approved to pay), AU (authorized), DE (denied), and RE (requested).
-- All other values mean "not paid."
CONVERT(CHAR(10),(CASE WHEN cp.dtpaid IS NULL THEN clmdet.dtpaid ELSE cp.dtpaid END),101) AS CheckDate, 
CASE WHEN cp.scheck_number IS NULL AND clmdet.scheck_number IS NOT NULL THEN clmdet.scheck_number 
    WHEN cp.scheck_number IS NOT NULL AND clmdet.scheck_number IS NULL THEN cp.scheck_number ELSE NULL END AS CheckNumber, 
 ISNULL(SUM(clmdet.cauth_total),0.00) AS CheckAmount 
, NULL AS CCAuthorization, NULL AS TransientFlag, 'M' AS DateFormatType,
scr.scancel_reason_code AS DeniedReasonCode, con.cdeductible_amount AS DeductibleAmount 
FROM ASA_Auto.dbo.scs_claims clm
INNER JOIN ASA_Auto.dbo.scs_contracts con
ON clm.icontract_id = con.icontract_id
INNER JOIN ASA_Auto.dbo.scs_dealers deal
ON con.sdealer_number = deal.sdealer_number
INNER JOIN ASA_Auto.dbo.scs_claim_jobs cj
ON clm.iclaim_ID = cj.iclaim_ID
INNER JOIN ASA_Auto.dbo.scs_claim_details clmdet
ON clm.iclaim_ID = clmdet.iclaim_ID
AND cj.sjob_no = clmdet.sjob_no
AND clmdet.sjob_no = cj.sjob_no
INNER JOIN ASA_Auto.dbo.scs_claim_detail_status cst  
ON clmdet.iclaim_det_status_id = cst.iclaim_det_status_id
LEFT OUTER JOIN (SELECT UniComponentCode, ComponentOriginator, OriginatorComponentCode
                FROM Auto_DB.dbo.tblComponentMapping
                WHERE ComponentOriginator = 'Test CREEK') tcm
ON LTRIM(RTRIM(clmdet.sloss_code)) = LTRIM(RTRIM(tcm.OriginatorComponentCode))
LEFT OUTER JOIN ASA_Auto.dbo.scs_claim_parts cp
ON clm.iclaim_ID = cp.iclaim_ID
AND clmdet.sjob_no = cp.sjob_no
AND clmdet.sdetail_Type = cp.sdetail_Type
LEFT OUTER JOIN ASA_Auto.dbo.scs_claim_jobs_complaint_cause_correction sjccc
ON cj.ijob_id = sjccc.ijob_id
LEFT OUTER JOIN ASA_Auto.dbo.scs_service_centers ssc
ON clm.iservice_center_ID = ssc.iservice_ID
LEFT OUTER JOIN ASA_Auto.dbo.scs_cancel_reason scr
ON con.icancel_reason_id = scr.icancel_reason_ID
LEFT OUTER JOIN ASA_Auto.dbo.scs_claim_details_history clmhist
ON clm.iclaim_id = clmhist.iclaim_id
WHERE con.icontract_type = 1
AND con.scontract_no='VSE00146887'
AND clm.sclaim_number='C000045326'
GROUP BY deal.sdealer_number, con.scontract_no, clm.sclaim_number, clmdet.sjob_no, tcm.UniComponentCode, clm.sclaim_status, 
sjccc.scomplaint, clm.dtro_received, clm.dtdate_loss_occurred, clm.iodometer_at_time_of_loss, ssc.sservice_name, 
clmdet.ntax_percent, cp.cauth_unit_cost, cp.nauth_qty, clmdet.nreq_qty, clmdet.clabor_rate, cp.ctax_amt_1, clmdet.ctax_amt_1, 
clmdet.cauth_total,cst.sclaim_det_status_code, cp.dtpaid, clmdet.dtpaid, cp.scheck_number, clmdet.scheck_number, clmdet.camt_paid, 
scr.scancel_reason_code, clmdet.sdetail_Type, con.cdeductible_amount, cp.sdetail_Type;


Edited by - igloo21 on 06/17/2014 08:40:04

Constraint Violating Yak Guru

420 Posts

Posted - 06/17/2014 :  10:57:13  Show Profile  Reply with Quote
Take it a part, bit by bit. Are the results of the joins always 1 to 1? If not, you might be summing some values more than once.
Go to Top of Page

Starting Member

3 Posts

Posted - 06/17/2014 :  11:01:26  Show Profile  Reply with Quote
I've been chopping it down chunk by chunk and still can't seem to get my MAX total i.e. SUM to only show that.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000