rwheeler23
asked on
SQL Case Statements with And
Can someone explain what is wrong with my case statements? I have checked the values on the records in the table and everyone should fit within one of these criteria. I believe I must have the syntax wrong with the use of the word AND. Each record has two fields which determine where to total goes. When I run this script no totals are accumulated for any of the values.
DECLARE @COMPANYID CHAR(5)
SET @COMPANYID = 'GSE'
SELECT @COMPANYID as COMPANYID,left(rtrim(jt.jobno),15) as jobnumber,coalesce(jt.invoiceno,'') as TRANSNMBR,'ACS' AS mastertype,
coalesce(pa.custnmbr,'') as masterid,coalesce(jt.vendorname,'') as mastername,jt.amount as debitamt,jt.amount as crdtamnt,
case rcrdtype when 'Cash' then 3 when 'Cost' then 1 when 'Sales' then 2 end as DOCTYPE,
jt.DateTrx as DOCDATE,jt.DatePost as POSTDATE,coalesce(jt.invoiceno,'') as DOCNUMBR,LEFT(jt.TrxDscr,31) as TRXDSCRN,
'Access_Import' as docsource,
(case when (rtrim(jt.glcredit) = '1065' or rtrim(jt.glcredit) = '1070') and rtrim(jt.gldebit) = '1000' then jt.amount else 0 end) as RCPTRCVD,
(case when rtrim(jt.glcredit) = '1065' and rtrim(jt.gldebit) = '5100' then jt.amount else 0 end) as RCPTDISC,
(case when rtrim(jt.glcredit) = '3368' and rtrim(jt.gldebit) = '1070' then jt.amount else 0 end) as PPREQREC,
(case when rtrim(jt.glcredit) = '1070' and rtrim(jt.gldebit) = '1000' then jt.amount else 0 end) as PPAYPAID,
(case when rtrim(jt.glcredit) = '5020' and rtrim(jt.gldebit) = '1065' then jt.amount else 0 end) as SALESALE,
(case when rtrim(jt.glcredit) = '7475' and rtrim(jt.gldebit) = '1065' then jt.amount else 0 end) as SALFRGHT
FROM job_transactions as jt
inner join (
SELECT distinct rtrim(PACONTNUMBER) as pacontnumber
FROM PA01101
) as P
on rtrim(jt.jobno) = rtrim(p.PACONTNUMBER)
inner join pa01201 pa
on jt.jobno = pa.pacontnumber
where len(rtrim(jt.jobno))>0 and jt.DatePost < '08/01/2010' and
jt.trxtype in ('Sales','Requested','Freight','Cash Receipts') AND jt.jobno='2020'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER