Returning Value in sub-select 1

[blar9]

I am not that good with sql and i need to be able to get this query to return the same information but i also need the HoursWorked that is in the subselect to return its value. I would also LOVE any advise on making this run faster

THANK ALOT!



SELECT TOP (5) SUM(Commissions.UnitsSold * Commissions.PriceAtSale / Commissions.Split) /

(SELECT ISNULL(SUM(CAST(DATEDIFF(ss, Clockin, ISNULL(ClockOut, @CurTime)) AS float) / 60 / 60), 1) AS HoursWorked
FROM Emp_TimeCard
WHERE (Clockin <= GETDATE()) AND (ISNULL(ClockOut, GETDATE()) <= GETDATE()) AND (Clockin >= @TheDate) AND
(Commissions.EmployeeUID = EmployeeUID) AND
(HoursWorked >= 1)

GROUP BY EmployeeUID) AS DollarPerHour, Emp_General.LastName + ', ' + Emp_General.FirstName AS EmpName, Commissions.EmployeeUID
FROM Commissions

INNER JOIN Emp_General
ON Commissions.EmployeeUID = Emp_General.EmployeeUID
WHERE (Commissions.Deleted = 0) AND (Emp_General.Deleted = 0) AND (Commissions.DateofSale >= @TheDate)
AND
(Commissions.DateofSale < DATEADD(dd, 1, @TheDate))

GROUP BY Commissions.EmployeeUID, Emp_General.LastName, Emp_General.FirstName
ORDER BY DollarPerHour DESC

 

[gmmastros]

Try this...

[COLOR=blue]SELECT[/color]  TOP (5) 
        SUM(Commissions.UnitsSold * Commissions.PriceAtSale / Commissions.Split) /
        (HourlyTime.HoursWorked) [COLOR=blue]AS[/color] DollarPerHour, 
        HourlyTime.HoursWorked,
        Emp_General.LastName + [COLOR=red]', '[/color] + Emp_General.FirstName [COLOR=blue]AS[/color] EmpName, 
        Commissions.EmployeeUID
[COLOR=blue]FROM[/color]    Commissions 
        [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] Emp_General 
          [COLOR=blue]ON[/color] Commissions.EmployeeUID = Emp_General.EmployeeUID
        [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] 
          (
          [COLOR=blue]SELECT[/color] EmployeeUId,
                 [COLOR=#FF00FF]ISNULL[/color](SUM([COLOR=#FF00FF]CAST[/color]([COLOR=#FF00FF]DATEDIFF[/color](ss, Clockin, [COLOR=#FF00FF]ISNULL[/color](ClockOut, @CurTime)) [COLOR=blue]AS[/color] [COLOR=blue]float[/color]) / 60 / 60), 1) [COLOR=blue]AS[/color] HoursWorked
          [COLOR=blue]FROM[/color]   Emp_TimeCard
          [COLOR=blue]WHERE[/color]  (Clockin <= [COLOR=#FF00FF]GETDATE[/color]()) 
                 AND ([COLOR=#FF00FF]ISNULL[/color](ClockOut, [COLOR=#FF00FF]GETDATE[/color]()) <= [COLOR=#FF00FF]GETDATE[/color]()) 
                 AND (Clockin >= @TheDate) 
                 AND (HoursWorked >= 1)
          [COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] EmployeeUID
          ) [COLOR=blue]As[/color] HourlyTime
             [COLOR=blue]On[/color] (Commissions.EmployeeUID = HourlyTime.EmployeeUID) 
[COLOR=blue]WHERE[/color]   (Commissions.Deleted = 0) 
        AND (Emp_General.Deleted = 0) 
        AND (Commissions.DateofSale >= @TheDate) 
        AND (Commissions.DateofSale < [COLOR=#FF00FF]DATEADD[/color](dd, 1, @TheDate))
[COLOR=blue]GROUP[/color] [COLOR=blue]BY[/color] Commissions.EmployeeUID, Emp_General.LastName, Emp_General.FirstName, HourlyTime.HoursWorked
[COLOR=blue]ORDER[/color] [COLOR=blue]BY[/color] DollarPerHour [COLOR=#FF00FF]DESC[/color]

-George

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

 

[blar9]

THANKS!

 

[gmmastros]

You're welcome.

Just out of curiousity, is it any faster?

-George

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

 

[blar9]

I havn't noticed much speed increase, that has been a constant issue with this site the db is not really well designed and everything is SLOW. ANy ideas on increasing speed for this would be welcome

 

[gmmastros]

Indexes?

Run this in query analyzer and post the output here.

sp_helpindex 'Commisions'
sp_helpIndex 'Emp_General'


-George

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