Solved

SQL Case Statements with And

Posted on 2011-03-03
2
263 Views
Last Modified: 2012-05-11
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'

Open in new window

0
Comment
Question by:rwheeler23
2 Comments
 
LVL 15

Accepted Solution

by:
derekkromm earned 500 total points
ID: 35033125
Which case statement isn't working? They all look logically/syntactically correct.

Can you give some sample data for ones that aren't working?
0
 

Author Closing Comment

by:rwheeler23
ID: 35033332
This was my fault. The database I was looking at had extra characters on the end of the GLDEBIT and GLCREDIT fields. My database does not have them so I had completely forgoten they were there.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question