Cluskitt
asked on
Counting Sum of days using Calendar table
I have a calendar table defined. I use it, for example, in this simplified query:
'11-06-2011' '25-06-2011' '15'
'15-10-2011' '20-10-2011' '6'
But now, what I want is the sum of the Dias for each FK_ID_intFuncionario. Something like:
SELECT FK_ID_intFuncionario, SUM(Dias)
FROM .... etc
Where the result would be something like:
1451 21
1452 15
1453 0
I know how to create the whole query except the part to SUM the days from the Calendar subquery.
SELECT datHoraEntradaTrabalho,
datHoraSaidaTrabalho,
(SELECT COUNT(*) FROM Calendar WHERE CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho) Dias
FROM tblTipoTrabalho
WHERE FK_ID_intEmpresa=41
AND FK_ID_intFuncionario=1451
AND strTipoTrabalho='X'
Now, this works fine for a single record. If I want weekdays I can add (AND businessday=1). It returns something like:'11-06-2011' '25-06-2011' '15'
'15-10-2011' '20-10-2011' '6'
But now, what I want is the sum of the Dias for each FK_ID_intFuncionario. Something like:
SELECT FK_ID_intFuncionario, SUM(Dias)
FROM .... etc
Where the result would be something like:
1451 21
1452 15
1453 0
I know how to create the whole query except the part to SUM the days from the Calendar subquery.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yes, that's the one I wanted. I just wasn't sure how to join the Calendar table (still new with working with one). Also, I wanted the REPLACE lines dropped. Thanks for the help :)
ASKER
Open in new window
And now I want the results from this, but with the Sums of Cal and Util. I tried using SUM on the subqueries and grouping by the first two fields (other two are no longer important), but it complains that "Cannot perform an aggregate function on an expression containing an aggregate or a subquery".