convert to numeric

[toon10]

I'm running the following query through the visual basic 6 data designer against a SQL 2005 database.

SELECT `Part No`, SUM(Val - `Cost Of Sale`) AS Margin, Description,
`Product Group`
FROM tblSales
WHERE (`Invoice Date` BETWEEN (# 01 / 01 / 2006 #) AND
(# 01 / 02 / 2007 #))
GROUP BY `Part No`, Description, `Product Group`
ORDER BY SUM(Val - `Cost Of Sale`) DESC

This works OK apart from the sort order. It lists margins of "0" first, then "- numbers" and then the order is correct, i.e. largest positive number followed by the next largest and so on.

i.e. 0, 0, 0, -14, 590, 570, 300, 200, etc.

I want to be able to display so that largest positive number followed by the next largest number are at the top, eventually going to 0 and then eventuially listing the - numbers.

i.e. 590, 570, 300, 200, 0, 0, 0, -14.

I'm presuming this isn't happening now because maybe the margin field isn't numeric?

I've tried to use a CONVERT statement but get errors. Is there another way to convert the calculated margin field to numeric so I can order the column descending?

Thanks

 

[gmmastros]

There must be something else going on. As written, this query should fail completely because with SQL Server, dates should be surrounded with tick/single-quotes/apostrophes, not the pound sign that your query shows. Also, you shouldn't use apostrophes around your field names. SQL Server prefers square brackets. So, your query should be...

SELECT [Part No], 
       SUM(Val - [Cost Of Sale]) AS Margin, 
       Description, 
       [Product Group[
FROM   tblSales
WHERE  [Invoice Date] BETWEEN '01/01/2006' AND '01/02/2007'
GROUP BY [Part No], Description, [Product Group]
ORDER BY SUM(Val - [Cost Of Sale]) DESC

Also, when specifying dates, you really should be using the ISO unseperated date format which is YYYYMMDD, so your where clause should really be...

WHERE [Invoice Date] BETWEEN '20060101' AND '20070102'

Also, if your [Invoice Date] field includes a time component, then you really shouldn't be using between. Instead, you should use an AND condition, like this...

[tt]
WHERE [Invoice Date] >= '20060101'
AND [Invoice Date] < '20070102'
[/tt]

When using between, and records at exactly midnight on Jan 2, 2007 would be included in the results. With the way I wrote it, they wouldn't be included (because I used less than instead of 'less than or equal to').

So, my query would look like...

[code]
SELECT [Part No], 
       SUM(Val - [Cost Of Sale]) AS Margin, 
       Description, 
       [Product Group[
FROM   tblSales
WHERE  [Invoice Date] >= '20060101' 
       AND [Invoice Date] < '20070102'
GROUP BY [Part No], Description, [Product Group]
ORDER BY SUM(Val - [Cost Of Sale]) DESC

Will this 'fix' your problem? I don't know, but give it a shot and see what happens.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[toon10]

Hi thanks for replying.

No the dates are fine, that part works. You need to use the hash # symbol for dates.

The between statement is also more efficient than using >= AND <= although both work. The square bracket acts in exactly the same way as the ` symbol (programmers preferecnce if you like!)

It must be something to do with the format of my calculated field. If I remove teh "DESC" from my original SQL statement, it works fine oddly enough. It's just that I need the sort order reversed so the highest margin is first.

Andrwe

 

[gmmastros]

As I stated before, something else must be going on. Are you using DAO or ODBC to connect to the database? It could be the source of your problem. If you truely are using a SQL 2005 database drive, then you would see that all of the advice I gave you was correct.

No the dates are fine, that part works. You need to use the hash # symbol for dates.

If you open the SQL Server Management Studio and try to use the hash symbol to delimit your dates, you will get this...
[tt][red]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '#'.
[/red][/tt]
(or possibly a different error message)

The between statement is also more efficient than using >= AND <= although both work.
The between statement is not more efficient. It is equally efficient. The problem is that it is the same as >= AND <=. If you don't want to include records from the end date, then you should use >= AND <. Notice the end range is less than, not less than or equal to.

The square bracket acts in exactly the same way as the ` symbol (programmers preferecnce if you like!)
This isn't true either. The single-quote symbol is used to delimit strings, and the square bracket is used to delimit objects (like tables, columns, etc...)

All of this is true for SQL Server, and may not be true for other databases.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Catadmin]

No, George had it right the first time. Your code should fail in SQL Server. When I try to run it against either SQL Server 2000 or 2005, I get an error near your single quotes.

I think the code you're using is Access SQL code or the VB 6 version of SQL code which is not T-SQL code (what SQL Server 2000 & 2005 use) The use of Between in T-SQL doesn't include the entire range (it drops values) and the # marks remind me of Access SQL. That being the case, you should realize this forum is about T-SQL coding, not the structure of code you've posted, and we may not be able to give you the answer you're looking for. At least, not in the format your query is currently posted.

I assume you are passing this code to SQL Server through a function in VB 6. Correct? You may have to check with the VB forum to get the answer you're looking for.

However, I believe you need to check your VAL and CostOfOrder fields to see if they are indeed numeric or one of other several number data types. If they are not, then you are correct about the order problem. I believe special characters are ordered before numbers or letters. The only way to fix this is with a Convert or Cast statement.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[toon10]

No as mentioned initially, I'm running the query through the visual basic 6 data designer against a SQL 2005 database.

The query does run fine and produces results although the sort order is not quite right.

 

[toon10]

I've posted on the VB 6 forum as all I need is to get the equivilant of the convert statement to run without error.

 

[gmmastros]

This can't be a data type conversion problem because the SUM function can only return numeric data types (depending on the data type of the parameter passed to it). For more information on this, please see

http://msdn2.microsoft.com/en-us/library/ms187810.aspx

Since conventional t-sql code isn't working, perhaps you could try...

Select `Part No`, Margin, Description, 
    `Product Group`
From   (
SELECT `Part No`, SUM(Val - `Cost Of Sale`) AS Margin, Description, 
    `Product Group`
FROM tblSales
WHERE (`Invoice Date` BETWEEN (# 01 / 01 / 2006 #) AND 
    (# 01 / 02 / 2007 #))
GROUP BY `Part No`, Description, `Product Group`
        ) As A
ORDER BY Margin DESC

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[toon10]

I have the fix.

The numeric conversion I required is "SUM(cdbl(Val) - cdbl(`Cost Of Sale`)) "

I knew I needed to convert the fields to matching numeric ones but couldn't find the syntax.

The working query looks like:

SELECT `Part No`, SUM(Val - `Cost Of Sale`) AS Margin, Description,
`Product Group`
FROM tblSales
WHERE (`Invoice Date` BETWEEN (# 01 / 01 / 2006 #) AND
(# 01 / 02 / 2007 #))
GROUP BY `Part No`, Description, `Product Group`
ORDER BY SUM(cdbl(Val) - cdbl(`Cost Of Sale`)) DESC