asked on

T-SQL: "SELECT TOP 100 PERCENT" is not as easy to use as the books make it out to be!!!

Hello:

Okay. Let me lay my cards out on the table. I'm well past the point of frustration. Below is my code.

All I'm trying to do, much to T-SQL's chagrin, is select the CustomerID, CustomerName, and total amount (i.e. "OPEN A/R") for each customer.

But, especially toward the end of my code, I'm getting syntax errors generally having to do with the parentheses and placement of other simple chracters.

Every time I try to do code similar to this, I get syntax errors. Even when I look up similar code examples, I can't figure out how to fix this.

So, I hope that someone here on this web site can fix this. I'm done trying to study and figure out T-SQL. That language does not want me to learn it. I'll just go pump flippin' gas, somewhere. I've had it!

And, I don't want to group by test.Open A/R. If I do that, I will get thousands of records. I only want to group by CUSTNMBR and, if necessary, CUSTNAME.

Thank you!

John

DECLARE @AGE DATETIME;
DECLARE @RUN DATETIME;
SET @AGE = '2015-09-30 00:00:00.000';
SET @RUN = '2016-07-31 00:00:00.000';

select test2.[CustomerID], test2.[CustomerName], test2.[OPEN A/R]
(
SELECT TOP 100 PERCENT *, SUM([OPEN A/R]) from
(
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],
SUM(CASE WHEN RM20101.CURTRXAM = 0 THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END) 
AS [OPEN A/R]
from RM20101
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
where RM20101.VOIDSTTS = 0 and RM20101.DUEDATE < @AGE AND RM20101.RMDTYPAL < 7
AND 
(RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', 
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968', 
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',  
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01', 'TRN6557', 'SVC989E')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME 
UNION
select RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],
SUM(CASE WHEN RM20101.CURTRXAM = 0 THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END * -1) 
AS [OPEN A/R]
from RM20101
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
where RM20101.VOIDSTTS = 0 and RM20101.DOCDATE < @AGE AND RM20101.RMDTYPAL > 6
AND 
(RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149', 
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968', 
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',  
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01', 'TRN6557', 'SVC989E')) 
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME)
as test
GROUP BY test.CustomerID, test.CustomerName)
as test2

Open in new window

Scott Pletcher

You can do it all in a single query with a little CASE adjustment to the calc:

SELECT RM20101.CUSTNMBR as [CustomerID], RM00101.CUSTNAME as [CustomerName],
SUM(CASE WHEN RM20101.CURTRXAM = 0 THEN RM20101.ORTRXAMT ELSE RM20101.CURTRXAM END *
CASE WHEN RM20101.RMDTYPAL < 7 THEN 1 ELSE -1 END)
AS [OPEN A/R]
from RM20101
INNER JOIN RM00101 ON RM20101.CUSTNMBR = RM00101.CUSTNMBR
where RM20101.VOIDSTTS = 0 and RM20101.DUEDATE < @AGE
AND
(RM20101.CUSTNMBR IN (' 320651', '00065', '00351', '00370', '0035829', '0015200', '0100010149',
'0100011881', '0100011303', '0100011766', '0100012897',
'195145', '0100550714', '238624', '0100013309', '0100010453', '266267', '179520', '0100012517', '0100011519', '0100012160',
'0100012558', '0100075597', '0100075290', '0100354828', '274173', '0100014190', '0100020981', '00144', '0100015049', '0100014968',
'0100018654', '0100019363', '0100019371',
'0100020932', '0100021047', '0100024819', '0100030188', '0100014661', '0100015270', '0100032895', '0100022052',
'0100033398', 'WW_FLIGHT12', 'WEST_MD_HC01', 'TRN6557', 'SVC989E'))
GROUP BY RM20101.CUSTNMBR, RM00101.CUSTNAME
ORDER BY RM20101.CUSTNMBR

John Ellis

ASKER

Hmm....the problem with that modified CASE statement is that it does not multiply ORTRXAMT * -1 for times when the RMDTYPAL is >6. That would explain why, when running this statement, several of my OPEN A/R values are inflated.

Any ideas on how to fix that? I'm just not good with T-SQL syntax and am going to be spending the rest of this day scrambling to find out what I'm good at, instead. And, if I'm not good at anything else, there's always politics.

John

John Ellis

ASKER

I'll qualify what I just said. Even if that CASE statement is doing what I want, it's a moot point. I need a comparison of RM20101.DUEDATE and @AGE when RM20101.RMDTYPAL < 7 and a comparison of RM20101.DOCDATE and @AGE when RM20101.RMDTYPAL > 6.

Any ideas on that?

Sigh....I sure with they made this easier. There's gotta be a way to get computers to understand human language rather than having to get us to understand theirs. It ain't fair.

John

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Ellis

ASKER

As best as I can tell, it looks like that did it! Thank you!

I swear you guys that know this stuff have computerized brains! :)

John

T-SQL: &quot;SELECT TOP 100 PERCENT&quot; is not as easy to use as the books make it out to be!!!

T-SQL: "SELECT TOP 100 PERCENT" is not as easy to use as the books make it out to be!!!