[Last Call] Learn how to a build a cloud-first strategyRegister Now


Counting Sum of days using Calendar table

Posted on 2011-10-27
Medium Priority
Last Modified: 2012-05-12
I have a calendar table defined. I use it, for example, in this simplified query:
SELECT datHoraEntradaTrabalho,
	(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'

Open in new window

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.
Question by:Cluskitt
  • 2
LVL 18

Author Comment

ID: 37037214
To be more specific. I have this:
SELECT psa_num_empregado Num, psa_nome_abreviado Nome, 
	REPLACE(CONVERT(varchar(10),datHoraEntradaTrabalho,103),'/','-') Inicio,
	REPLACE(CONVERT(varchar(10),datHoraSaidaTrabalho,103),'/','-') Fim, 
	(SELECT COUNT(*) FROM Calendar WHERE CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho) Cal, 
	(SELECT COUNT(*) FROM Calendar WHERE CalendarDate BETWEEN datHoraEntradaTrabalho AND datHoraSaidaTrabalho AND businessday=1) Util 
FROM uv_Cadastro LEFT JOIN tblTrabalho ON psa_emp_empresa=FK_ID_intEmpresa AND psa_num_empregado=FK_ID_intFuncionario 
INNER JOIN tblTiposTrabalho ON FK_ID_intTipoTrabalho=ID_intTipoTrabalho 
WHERE strTipoTrabalho='Férias'AND FK_ID_intEmpresa='41' AND YEAR(datHoraSaidaTrabalho)=2011 AND strNumHorario NOT LIKE '%/%' 
ORDER BY Num, Inicio

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".
LVL 74

Accepted Solution

sdstuber earned 2000 total points
ID: 37037252
I'm not entirely sure what  your final goal is, but try using this as your subquery and see if you can sum on these results

SELECT psa_num_empregado num,
       psa_nome_abreviado nome,
       REPLACE(CONVERT(varchar(10), dathoraentradatrabalho, 103), '/', '-') inicio,
       REPLACE(CONVERT(varchar(10), dathorasaidatrabalho, 103), '/', '-') fim,
       COUNT(calendardate) cal,
       COUNT(CASE WHEN businessday = 1 THEN calendardate END) util
  FROM uv_cadastro
      LEFT JOIN tbltrabalho
          ON psa_emp_empresa = fk_id_intempresa AND psa_num_empregado = fk_id_intfuncionario
      INNER JOIN tbltipostrabalho
          ON fk_id_inttipotrabalho = id_inttipotrabalho
      LEFT JOIN calendar
          ON calendardate BETWEEN dathoraentradatrabalho AND dathorasaidatrabalho
 WHERE strtipotrabalho = 'Férias'
   AND fk_id_intempresa = '41'
   AND year(dathorasaidatrabalho) = 2011
   AND strnumhorario NOT LIKE '%/%'
GROUP BY psa_num_empregado,
         REPLACE(CONVERT(varchar(10), dathoraentradatrabalho, 103), '/', '-'),
         REPLACE(CONVERT(varchar(10), dathorasaidatrabalho, 103), '/', '-')
ORDER BY num, inicio
LVL 18

Author Closing Comment

ID: 37037401
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 :)

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question