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.
]]>