Counting Sum of days using Calendar table

Posted on 2011-10-27
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
    LVL 18

    Author Comment

    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 73

    Accepted Solution

    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

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

    760 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now