SQL Server Performance Forum – Threads Archive

# breaking a query up into sets

I have a table that has keeps track of an employees hourly data. The simplified stucture of this table is: Action TableActionDate

ActionID

ActionTimeStart

ActionTimeStop

BillableHours

EmployeeID

I would like to have one query that would sum the billable hours by week, and return multiple results. For example, if i specified that the query should cover 16 days, then I would have 3 results, the sum for the first week, the sum for the second, and the sum of the third. Let me know if you need more information. Thanks, Ben

This depends on a few things, but here is a simple one that would work if you start your data at teh beginning of the year declare @numdays int, @numweeks int set @numdays = 16

set @numweeks = ceiling(@numdays/16) select datepart(wk), sum(billablehours)

from mytable

where datepart(wk) <= @numweeks

group by datepart(wk) If your data started somewhere else in the year, its a bit more, but not a lot more complicated than the above. There are other ways too, but start with this to see if it helps.

I prefer simple maths, like this: DECLARE @Dt DATETIME, @NrOfDays INT SET @Dt = CONVERT(VARCHAR(10), GETDATE(), 101)

SET @NrOfDays = 21 select datepart(ww, mydate) AS WeekNr, SUM(billablehours) AS SumHours

from MyTable

where mydate between @Dt and DATEADD(dd, @NrOfDays, @Dt)

group by datepart(ww, mydate)

That helps out quite a bit, the only questions I have is where would I enter the dates for the period that I would like to get back?

CREATE PROCEDURE dbo.MyProc

(@Dt DATETIME, @NrOfDays INT)

AS select datepart(ww, mydate) AS WeekNr, SUM(billablehours) AS SumHours

from MyTable

where mydate between @Dt and DATEADD(dd, @NrOfDays, @Dt)

group by datepart(ww, mydate) GO Call it like this:

EXEC dbo.MyProc ‘2004-09-29’, 21

Thank you very much. That worked perfect.

]]>