Solved

SQL Case Statements with And

Posted on 2011-03-03
2
262 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

895 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now