what is the datatype of the column? DATETIME or VARCHAR?
and are you sure you're actually using SQL Server?
because MID is not a valid SQL Server function
Hi again, this is for a school assignment
im using Access right now because i dont know how convert an access table file to sql server management.
what i did so far.
select * from(select MID(ordertbl.orddate,3,2) as MonthValue, employee.empno, employee.emplastname, (product.prodprice * sum(ordline.qty)) * employee.empcommrate as TotCommAmt from ordertbl, ordline, product, employee where ordertbl.ordno=ordline.ordno and ordline.prodno=product.prodno and employee.empno=ordertbl.empno group by MID(ordertbl.orddate,3,2), product.prodprice, employee.empno, employee.emplastname, employee.empcommrate) order by totcommamt desc,monthvalue asc
but the ORDER BY dont work properly because of the month
List the total commission earned by each employee in each month of 2007. The result should include the month, employee number, employee last name, and the total commission amount earned in that month. The amount of a commission for an
individual employee is the sum of the dollar amount of products ordered times the
commission rate of the employee.Sort the result by the month in ascending month
number and the total commission amount in descending order. For SQL Server, the
month number can be extracted by the Month function with a date as the argument.
P.S i am not sure if the total commission cal. is correct. but anyway, i just want the month to work correctly for now