Simple SQL query for counts and Sum

14 Sep 2006 2:19 PM

Pauljh

Hi All,
   As all the comp.database groups seam to be available for read only
(and not post) I'm posting my question here, hopefully someone much
better at SQL can help (which isn't saying much my SQL knowledge is
rather limited).

   Anyway I have a table which has the properties
   Allocation Date (Always the 1st of the month (i.e. relates to a
month/year))
   Employee
   JobNumber
   Allocation (which is a % of time spent)

I need a query to retreve (between two 'months') all empoyees
utilization, kind of like

SELECT Employee
, JobNumber
, SUM(SELECT Allocation
    FROM JobCosting.TimeAllocation b
    WHERE b.AllocationDate=a.AllocationDate
    AND b.Employee=a.Employee
    AND b.JobNumber=a.JobNumber
)/COUNT(SELECT AllocationDate
    FROM JobCosting.TimeAllocation c
    WHERE c.AllocationDate=a.AllocationDate
    AND c.Employee=c.Employee
    AND c.JobNumber=c.JobNumber
) AS Utilization
FROM JobCosting.TimeAllocation a
WHERE AllocationDate>="A date from value"
AND AllocationDate<="A date to value"

But obviously this is completly wrong!

Help much appreciated..

14 Sep 2006 3:56 PM

Arnie Rowland

Something like this is more what you want.

SELECT
     Employee
   , JobNumber
   , Utilization = ( sum( Allocation ) / count(1) )
FROM JobCosting.TimeAllocation a
WHERE (   AllocationDate >= @StartDateTime
      AND AllocationDate <= @EndDateTime
GROUP BY
     Employee
   , JobNumber

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Pauljh" <Paul.Play***@gmail.com> wrote in message news:1158243554.170229.55850@p79g2000cwp.googlegroups.com...
> Hi All,
>   As all the comp.database groups seam to be available for read only
> (and not post) I'm posting my question here, hopefully someone much
> better at SQL can help (which isn't saying much my SQL knowledge is
> rather limited).
>
>   Anyway I have a table which has the properties
>   Allocation Date (Always the 1st of the month (i.e. relates to a
> month/year))
>   Employee
>   JobNumber
>   Allocation (which is a % of time spent)
>
> I need a query to retreve (between two 'months') all empoyees
> utilization, kind of like
>
> SELECT Employee
> , JobNumber
> , SUM(SELECT Allocation
> FROM JobCosting.TimeAllocation b
> WHERE b.AllocationDate=a.AllocationDate
> AND b.Employee=a.Employee
> AND b.JobNumber=a.JobNumber
> )/COUNT(SELECT AllocationDate
> FROM JobCosting.TimeAllocation c
> WHERE c.AllocationDate=a.AllocationDate
> AND c.Employee=c.Employee
> AND c.JobNumber=c.JobNumber
> ) AS Utilization
> FROM JobCosting.TimeAllocation a
> WHERE AllocationDate>="A date from value"
> AND AllocationDate<="A date to value"
>
> But obviously this is completly wrong!
>
> Help much appreciated..
>