Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Case Statements with And

Posted on 2011-03-03
2
Medium Priority
?
268 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 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 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