How to Select all Days of Month GROUP BY Date 1

[Kristjan]

I need totals of a table grouping by date....
My Table is:
Empleado_Horas_Labor(....,EMPLEADO,Fecha,Horas_Normales,....)

SELECT Fecha,SUM(Horas_Normales) FROM Empleado_Horas_Labor
WHERE EMPLEADO = '02-061' GROUP BY Fecha

The result is:

Fecha
--------------------------- -----------------
2004-02-01 00:00:00.000 8.0
2004-02-02 00:00:00.000 10.0
2004-02-03 00:00:00.000 10.0
2004-02-04 00:00:00.000 10.0
2004-02-05 00:00:00.000 10.0
2004-02-06 00:00:00.000 8.0
2004-02-08 00:00:00.000 8.0
.......
.......
2004-02-26 00:00:00.000 10.0
2004-02-27 00:00:00.000 8.0
2004-02-29 00:00:00.000 8.0

(27 row(s) affected)
***********************
I want a result like this:

Fecha
--------------------------- -----------------
2004-02-01 00:00:00.000 8.0
2004-02-02 00:00:00.000 10.0
2004-02-03 00:00:00.000 10.0
2004-02-04 00:00:00.000 10.0
2004-02-05 00:00:00.000 10.0
2004-02-06 00:00:00.000 8.0
2004-02-07 00:00:00.000 NULL
2004-02-08 00:00:00.000 8.0
.......
.......
2004-02-26 00:00:00.000 10.0
2004-02-27 00:00:00.000 8.0
2004-02-28 00:00:00.000 NULL
2004-02-29 00:00:00.000 8.0

(29 row(s) affected)

HAVING ALL DAYS OF THE MONTH in the result set

Thanks

 

[SQLSister]

Best way to do this is to create a date table which stores all the days of the year and do a left join to it (date table being on the left side of the join).

Of course don't actually name it date as that is a reserved word.

 

[Kristjan]

Yes SQLSister, i thought that, but posted searching for another way.
And that approach take more than a simple SELECT...
Oracle has SELECT xxxx FROM DUAL, etc, etc, MSSQL-SERVER has something like that?

I need to spend a table to do that!!!?
it must have another way!!!

thanks

 

[MartinCroft]

Hi
It is possible to do without creating another object as to whether its better thats debateable personally i'd use a calander table, but i thought i'd share the possibility
with you

I created a datestable to mimic your current table



DECLARE @lastDay Smalldatetime
,@FirstDay Smalldatetime

SET @Firstday=convert(varchar(12),getdate()-day(getdate())+1,102) --first day of current month
SET @Lastday=dateadd(m,1,@firstday)-1 --last day of current month


SELECT
A.Monthdate
,sum(qty)
FROM

( SELECT Dateadd(d,Ten.digit*10+unit.digit,@firstday)as Monthdate --generate each day in current month
FROM (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION
SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION
SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION
SELECT 9
) AS Unit( Digit )
CROSS JOIN
(
SELECT 0 UNION SELECT 1 UNION
SELECT 2 UNION SELECT 3
) AS Ten( Digit )

WHERE
Ten.digit*10+unit.digit BETWEEN 0 AND 31
AND
Dateadd(d,Ten.digit*10+unit.digit,@firstday) <=@Lastday
) A
LEFT JOIN DatesTable ON DatesTable.Dateval=A.monthdate

GROUP BY
A.Monthdate


Hope this give you some ideas

 

[tektipdjango]

Good Idea
Can we use a table variable @days like this :


declare @days table
(dayDay datetime)
declare @DateCalc datetime
set @dateCalc='1/1/2004'

while @dateCalc<'1/1/2005'
begin
select @dateCalc into days
@dateCalc=dateCalc+1
end

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[r937]

mysticmart's Unit and Ten tables are extremely useful

you should seriously consider implementing them as a permanent integers table

see The integers table and Finding all the dates between two dates (registration may be required, but it's free)


rudy

http://r937.com/

SQL Consulting

 

[tektipdjango]

Will this kind of function work?
I haven't SQL Server at the moment

CREATE FUNCTION DaysBetween2Dates( @startDate datetime,@endDate datetime )
RETURNS @DaysBetweenDatesTab TABLE
   (
    days datetime
   )
AS
BEGIN
  declare @stDate datetime
  set @stDate=@startDate 
  while @stDate<=@endDate
  begin
    select @stDate into  @DaysBetweenDatesTab 
    set @stDate=@stdate+1
  end
  return
end

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[tektipdjango]

then you can say with the function (you have to enter the startDate and EndDate accordingly):

select days,SUM(Horas_Normales)
from
DaysBetween2Dates(startDate,EndDate)
left join
Empleado_Horas_Labor
on days=fecha
WHERE EMPLEADO = '02-061'
GROUP BY days

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​