Better Practice of SQL query

[cumap]

Hi,
Is there a way to make this query shorter or more importantly, make it more efficiency

sql = "SELECT TOP 1 SUM(a.numTotalPt) AS tMonthlyPt " _
	& "FROM tblCPBPS a " _
	& "INNER JOIN tblUsers b " _
	& "	ON a.idUser = b.UserID " _
	& "WHERE a.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
	& "	AND b.Saleman = 1 " _
	& " AND b.Active = 1 " _
	& "GROUP BY a.idUser " _
	& "ORDER BY tMonthlyPt DESC"
set rs = objConn.Execute(sql)
If NOT rs.eof then
	highest = rs("tMonthlyPt")
End if
call closeRS(rs)
'Get Lowest Score of Seller
sql = "SELECT TOP 1 SUM(a.numTotalPt) AS tMonthlyPt " _
	& "FROM tblCPBPS a " _
	& "INNER JOIN tblUsers b " _
	& "	ON a.idUser = b.UserID " _
	& "WHERE a.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
	& "	AND b.Saleman = 1 " _
	& " AND b.Active = 1 " _
	& "GROUP BY a.idUser " _
	& "ORDER BY tMonthlyPt ASC"
set rs = objConn.Execute(sql)
If NOT rs.eof then
	lowest = rs("tMonthlyPt")
End if
call closeRS(rs)
[code]

Also,
Is it better/faster processing if I do this
[code]
sql = "SELECT a.idUser, SUM(a.numTotalPt) AS tMonthlyPt " _
	& "FROM tblCPBPS a " _
	& "INNER JOIN tblUsers b " _
	& "	ON a.idUser = b.UserID " _
	& "WHERE a.dtAddDate LIKE '" & gMonth & "%" & gYear & "%' " _
	& "	AND b.Saleman = 1 " _
	& " AND b.Active = 1 " _
	& "GROUP BY a.idUser " _
	& "ORDER BY SUM(a.numTotalPt) DESC"
set rs = objConn.Execute(sql)
If NOT rs.eof then
	do while not rs.eof
		arrayPts = arrayPts & ";" & rs("idUser") & "," & rs("tMonthlyPt") & ";"
		if len(rs("tMonthlyPt"))>0 then
			if cdbl(rs("tMonthlyPt")) > cdbl(highest) then
				highest = rs("tMonthlyPt")
			elseif cdbl(rs("tMonthlyPt")) < cdbl(lowest) then
				lowest = rs("tMonthlyPt")
			end if
		end if	
	rs.movenext
	loop
End if

Then, for the top sample, I ran another getSum for ALL Users and DO WHILE LOOP to show results; while for the bottom one, to print the results, I SPLIT the arrayPts to display.

I'd like to know which is a better practice.
If there's a better way to do just that, I'd like to know as well.
Thanks!

 

[NastiaMurzin]

Here is an example how you might do that,

USE production;
GO
WHILE (SELECT AVG(listprice) FROM production.product) < 300.00
BEGIN
UPDATE production.product
SET listprice = listprice * 2
SELECT MAX(listprice) FROM production.product
IF (SELECT MAX(listprice) FROM production.product) > 500.00
BREAK
ELSE
CONTINUE
END
PRINT 'Too much for the market to bear';

 

[cumap]

Thank NastiaMurzin for the reply...

could you elaborate a little more on the purpose of your query for me to have a better understanding of it?

By the way, I don't want to UPDATE my db in this case. All I want is to print the Users' point and, at the same time (if possible), get the highest and lowest point of all results.

Thanks again.

 

[markros]

Is your dtAddDate a date field or a character field?

Take a look for your problem of getting lowest/maximum info at these two links
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2

and also it may be helpful for you to take a look at this link (look at the first link from that link):

Series of "Bad habits to kick" by Aaron Bertrand


PluralSight Learning Library

 

[cumap]

it's a Date field.

 

[markros]

If it's a date field, then don't use LIKE operator with it. Start from the last link (and the first link from it) I gave you.

PluralSight Learning Library

 

[cumap]

Thank you markros, I'm looking into the tutorial right now.