Solved

SQL Case Statements with And

Posted on 2011-03-03
2
265 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Display SQL maintenance plan SQL Code 3 47
SQL Server Pivot 5 42
Restore a log backup compressed 3 16
SSIS Package Not Running in Batch File 3 11
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

739 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