case question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

case question

Author

Topic

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 11:17:08

i am trying to get a case setup and its erroring out.

The fields I am using is ardetl.arinvno and ardetl.artotal

i thought i could use this
CASE WHEN ardetl.arinvno 'Match' THEN sum(ardetl.artotal)END AS balance

See we have invoices that are used more than once and if they match up i want them to sum the artotal of those invoices and create a new field called balance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-31 : 11:20:04

CASE WHEN ardetl.arinvno = 'Match' THEN sum(ardetl.artotal)END AS balance

E 12°55'05.25"
N 56°04'39.16"

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 11:28:25

i am now getting an error stating incorrect syntax near the keyword 'FROM'

here is my code

SELECT custmast.custname AS manufacturer, ardetl.arinvno, ardetl.artotal, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate, CASE WHEN ardetl.arinvno = 'Match' THEN SUM(ardetl.artotal)END AS balance

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')

GROUP BY custmast.custname AS manufacturer, ardetl.arinvno, ardetl.artotal, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-31 : 11:38:18

SUM(CASE WHEN ardetl.arinvno = 'Match'
THEN ardetl.artotal
ELSE 0
) END AS balance

Jim

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 11:45:17

Now getting a new error message
Error in list of function arguments: '0' not recognized.
Error in list of function arguments: ')' not recognized.
Unable to parse query text.

SELECT custmast.custname AS manufacturer, ardetl.arinvno, ardetl.artotal, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate, SUM(CASE WHEN ardetl.arinvno = 'Match' THEN ardetl.artotalELSE 0) END AS balance

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')

ORDER BY manufacturer, ardetl.arduedate

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 11:46:22

then the error message states incorrect syntax near ')'.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-31 : 12:11:14

Oops, ) in the wrong place!

SELECT custmast.custname AS manufacturer
  , ardetl.arinvno
  , ardetl.artotal
  , DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days
  , ardetl.kcustnum
  , custmast.custsnum
  , ardetl.arbranch, ardetl.arslsmn
  , ardetl.ardate, ardetl.arduedate
  , SUM(CASE 
           WHEN ardetl.arinvno = 'Match' 
           THEN ardetl.artotal
           ELSE 0 ) END
       ) AS balance

FROM ardetl 
   INNER JOIN 
 custmast 
ON 
   ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') 
  AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') 
  AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')


ORDER BY manufacturer, ardetl.arduedate

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-31 : 12:15:16

SELECT custmast.custname AS manufacturer
, ardetl.arinvno
~~, ardetl.artotal~~
, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days
, ardetl.kcustnum
, custmast.custsnum
, ardetl.arbranch, ardetl.arslsmn
, ardetl.ardate, ardetl.arduedate
, SUM(CASE
WHEN ardetl.arinvno = 'Match'
THEN ardetl.artotal
ELSE 0 END
)AS balance

FROM ardetl
INNER JOIN
custmast
ON
ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999')
AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')
AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')

GROUP BY
custmast.custname
, ardetl.arinvno
~~, ardetl.artotal~~
, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1
, ardetl.kcustnum
, custmast.custsnum
, ardetl.arbranch, ardetl.arslsmn
, ardetl.ardate, ardetl.arduedate

ORDER BY manufacturer, ardetl.arduedate

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 12:24:12

we are getting closer but now its having an issue with 'Match' saying Conversion failed when converting the Valchar value 'Match' to data type int.

I was not sure if i could use match or not but the arinvno field is numbers and since they have numbers that match i thought i should use match.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 12:29:15

for example i have invoices that match
invoice, totals
11545 ,500.00
11545 ,500.00
11254 ,200.00
11254 ,200.00
11254 ,200.00

I want them to total up or sum if you will. the invoice 11545, 1000.00 and 11254, 600.00
and they each have totals that go with them

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 12:43:11

group by invoice field and take the sum of totals. that should give the result asked. however i'm not sure if this is the final result you're looking for.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 12:53:50

no see what i want to do then is if the sum =0 then take them off my list.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 12:59:18

quote:
Originally posted by midpenntech

no see what i want to do then is if the sum =0 then take them off my list.

that you can filter out using HAVING clause

SELECT invoice, sum(totals)
FROM table
GROUP BY invoice
HAVING sum(totals)>0

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 13:11:53

I added what you told put up above and it actually sum'ed up the arinvoices not the artotals.

SELECT custmast.custname AS manufacturer, ardetl.arinvno, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate, ardetl.artotal

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')

GROUP BY ardetl.arinvno, custmast.custname, ardetl.arduedate, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate, ardetl.artotal
HAVING (SUM(ardetl.artotal) > 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:16:52

quote:
Originally posted by midpenntech

I added what you told put up above and it actually sum'ed up the arinvoices not the artotals.

SELECT custmast.custname AS manufacturer, ardetl.arinvno, DATEDIFF(d, GETDATE(), ardetl.arduedate) * - 1 AS Days, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate, ardetl.artotal

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000') AND (ardetl.artotal <> 0) AND (custmast.custsnum = '000')

GROUP BY ardetl.arinvno, custmast.custname, ardetl.arduedate, ardetl.kcustnum, custmast.custsnum, ardetl.arbranch, ardetl.arslsmn, ardetl.ardate, ardetl.arduedate, ardetl.artotal
HAVING (SUM(ardetl.artotal) > 0)

then put the sum onto correct column. i dunno your table structure just gave solution according to your sample data.

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 13:30:12

ok here is some of our data to give you an idea of what i am talking about. If you look at the action Equipment co 2 invoices match i want them to then total and show 0 in a new column if that makes any more sense.

Manufacture, Invoice, Artotal

ACTION EQUIPMENT CO, 10009739, -42.89
ACTION EQUIPMENT CO, 10009739, 42.89
ADMAR SUPPLY COMPANY, 50003257, -361.25
ADMAR SUPPLY COMPANY, 50003257, 361.25
ADVANCED CONCRETE, 7080001, -18.79
ADVANCED CONCRETE, 7080001, 18.79
ALTEC INDUSTRIES, 5000301, -255.23
ALTEC INDUSTRIES, 50003012, 255.23
BANDIT INDUSTRIES, 7080002, -2.33
BANDIT INDUSTRIES, 10003477, 339.86
BANDIT INDUSTRIES, 10007332, -331.27
BANDIT INDUSTRIES, 16002582, -340.3
BANDIT INDUSTRIES, 16002587, 340.3
BANDIT INDUSTRIES, 20851, 495
BANDIT INDUSTRIES, 10003477, -333.47
BANDIT INDUSTRIES, 10003477, -6.39
BANDIT INDUSTRIES, 7080002, 2.33
BANDIT INDUSTRIES, 10006781, 250
BANDIT INDUSTRIES, 90000402, -54.44
BANDIT INDUSTRIES, 90000403, -158.98
BANDIT INDUSTRIES, 90000403, -12.4
BANDIT INDUSTRIES, 90000403, 171.38
BANDIT INDUSTRIES, 90000402, 54.44
BANDIT INDUSTRIES, 10007017, 94.19
BANDIT INDUSTRIES, 10007784, -65.66
BANDIT INDUSTRIES, 10007332, 331.27
BANDIT INDUSTRIES, 10007017, -87.78
BANDIT INDUSTRIES, 10007017, -6.41

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-31 : 13:37:58

quote:
Originally posted by midpenntech

ok here is some of our data to give you an idea of what i am talking about. If you look at the action Equipment co 2 invoices match i want them to then total and show 0 in a new column if that makes any more sense.

Manufacture, Invoice, Artotal

ACTION EQUIPMENT CO, 10009739, -42.89
ACTION EQUIPMENT CO, 10009739, 42.89
ADMAR SUPPLY COMPANY, 50003257, -361.25
ADMAR SUPPLY COMPANY, 50003257, 361.25
ADVANCED CONCRETE, 7080001, -18.79
ADVANCED CONCRETE, 7080001, 18.79
ALTEC INDUSTRIES, 5000301, -255.23
ALTEC INDUSTRIES, 50003012, 255.23
BANDIT INDUSTRIES, 7080002, -2.33
BANDIT INDUSTRIES, 10003477, 339.86
BANDIT INDUSTRIES, 10007332, -331.27
BANDIT INDUSTRIES, 16002582, -340.3
BANDIT INDUSTRIES, 16002587, 340.3
BANDIT INDUSTRIES, 20851, 495
BANDIT INDUSTRIES, 10003477, -333.47
BANDIT INDUSTRIES, 10003477, -6.39
BANDIT INDUSTRIES, 7080002, 2.33
BANDIT INDUSTRIES, 10006781, 250
BANDIT INDUSTRIES, 90000402, -54.44
BANDIT INDUSTRIES, 90000403, -158.98
BANDIT INDUSTRIES, 90000403, -12.4
BANDIT INDUSTRIES, 90000403, 171.38
BANDIT INDUSTRIES, 90000402, 54.44
BANDIT INDUSTRIES, 10007017, 94.19
BANDIT INDUSTRIES, 10007784, -65.66
BANDIT INDUSTRIES, 10007332, 331.27
BANDIT INDUSTRIES, 10007017, -87.78
BANDIT INDUSTRIES, 10007017, -6.41

SELECT Manufacture,invoice, sum(Artotal)
FROM table
GROUP BY Manufacture,invoice
HAVING sum(Artotal)>0

midpenntech
Posting Yak Master

137 Posts

Posted - 2008-07-31 : 14:18:28

ok let me show you whats happening this is the result of the code before i add your info look at the bandit line and invoice # 10007017 and notice the values. they should equal to 0.

Here is the code i used to get this.

SELECT ardetl.kcustnum, custmast.custname, ardetl.arinvno, ardetl.artotal, custmast.custsnum

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (custmast.custsnum = '000')

GROUP BY ardetl.kcustnum, custmast.custname, ardetl.arinvno, custmast.custsnum, ardetl.artotal

ORDER BY custmast.custname, ardetl.arinvno

-------------------------------------------------------------------
BANDIT INDUSTRIES INC., 10007017, -87.78
BANDIT INDUSTRIES INC., 10007017, -6.41
BANDIT INDUSTRIES INC., 10007017, 94.19
-------------------------------------------------------------------

Now here is with the code you gave me,noticed the Value come out to some real odd # and i dont know how it got it.

SELECT ardetl.kcustnum, custmast.custname, ardetl.arinvno, SUM(ardetl.artotal) AS Expr1, custmast.custsnum

FROM ardetl INNER JOIN custmast ON ardetl.kcustnum = custmast.kcustnum

WHERE (ardetl.kcustnum BETWEEN '005000' AND '0059999') AND (custmast.custsnum = '000')

GROUP BY ardetl.kcustnum, custmast.custname, ardetl.arinvno, custmast.custsnum

ORDER BY custmast.custname, ardetl.arinvno
-------------------------------------------------------------------
BANDIT INDUSTRIES INC., 10007017, -3.55E-15
-------------------------------------------------------------------

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources