Sum 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

SQL Server 2000 Forums

Transact-SQL (2000)

Sum question

Author

Topic

shifis
Posting Yak Master

157 Posts

Posted - 2003-07-08 : 21:51:50

HI!!

I have the next query

SELECT dbo.OEINVOICE.COMPANY, dbo.OEINVOICE.LOCATION,
"SALES"=CASE dbo.OEINVCLINE.TAX_CODE
WHEN 'IVA 0%' THEN OEINVCLINE.TAXABLE_BSE
WHEN 'IVA 10%' THEN OEINVCLINE.TAXABLE_BSE*1.1
WHEN 'IVA 15%' THEN OEINVCLINE.TAXABLE_BSE*1.15
ELSE OEINVCLINE.TAXABLE_BSE
END,
"TYPE" =CASE dbo.ITEMMAST.USER_FIELD_N1
WHEN 0 THEN 'N'
WHEN 4 THEN 'N'
ELSE 'I'
END
FROM dbo.OEINVOICE INNER JOIN
dbo.OEINVCLINE ON dbo.OEINVOICE.COMPANY = dbo.OEINVCLINE.COMPANY AND dbo.OEINVOICE.INVC_PREFIX = dbo.OEINVCLINE.INVC_PREFIX AND
dbo.OEINVOICE.INVC_NUMBER = dbo.OEINVCLINE.INVC_NUMBER INNER JOIN
dbo.ITEMMAST ON dbo.OEINVCLINE.ITEM = dbo.ITEMMAST.ITEM
WHERE (dbo.OEINVOICE.INVOICE_DATE = CONVERT(DATETIME, '2003-01-20 00:00:00', 102))

What I need to do is to sum all the "SALES" that are from the same "TYPE" and group by COMPANY and LOCATION

COMPANY LOCATION SALES/NATIONAL SALES/INTERNATIONAL TOTAL
=============================================================
20000 TJU01 $42122.00 $23000.00 $44422.00
20000 TJU02 $23422.00 $12000.00 $35422.00

nr
SQLTeam MVY

12543 Posts

Posted - 2003-07-08 : 21:59:21

Put a group by COMPANY, LOCATION at the end

select COMPANY, LOCATION
, NATIONAL = sum(case when type = 'NATIONAL' then salesamt else 0 end)
, ...

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

shifis
Posting Yak Master

157 Posts

Posted - 2003-07-09 : 11:42:09

To calculate the salesamt I have to do this first
salesamt=
CASE OEINVCLINE.TAX_CODE
WHEN 'IVA 0%' THEN OEINVCLINE.TAXABLE_BSE
WHEN 'IVA 10%' THEN OEINVCLINE.TAXABLE_BSE*1.1
WHEN 'IVA 15%' THEN OEINVCLINE.TAXABLE_BSE*1.15
ELSE OEINVCLINE.TAXABLE_BSE
END

So, how can I integrate that to this sugestion?

select COMPANY, LOCATION
, NATIONAL = sum(case when type = 'NATIONAL' then salesamt
else 0 end)
, ...

Edited by - shifis on 07/09/2003 11:45:47

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2003-07-09 : 14:43:17

shifis,

why is the tax code being stored as a varchar field? cant you store the actual tax percentage in the table? That way it would be a lot easier to calculate your BSE + Tax. Assuming tax rates are stored as integers...

SELECT dbo.OEINVOICE.COMPANY, dbo.OEINVOICE.LOCATION,
"SALES"= OEINVCLINE.TAXABLE_BSE * (1 + (dbo.OEINVCLINE.TAX_CODE/100))

Then you dont need the case statement. You could also create a tax-code table which contains one column with the code description and the other column containing the rate.

Owais

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources