weekly report
-
I have been collecting data into a table for 2 months and I want to generate a cumulative weekly report starting from the day1.
Day1 - day7 as week1
day1 - day14 as week2
day1 - day21 as week3
day1 - day28 as week4
......
My table have three columns student_id,date,no_of_hours
So based on date I need to calculate total number of hours for every week.
Can any one help in this issue.
Thanks.
-
create #table
(
date datetime,
hours numeric(10,2)
)
--insert some stuff into #table
declare @start_date datetime
--set your start date if you want
select @start_date = min(date)
from #Table
declare @results table
(
week int primary key,
hours numeric(10,2)
)
insert @results
select datediff(day,@start_date,date)/7+1 WEEK,sum(hours)
from #table
group by datediff(day,@start_date,date),date
declare @int integer
set @int = 1
while @int < (select max(week) from @results)
begin
if (select count(*) from @results where week= @int)=0
begin
insert @results
select @int,0.00
end
set @int = @int + 1
end
select * from @results
HTH
Mathew J Kulangara
sqladventures.blogspot.com -
--this is a NUMBERS table
------------------------------------------------
DECLARE @MyTable TABLE(Col1 int)
DECLARE @i int
SET @i=1
WHILE @i<31
BEGIN
INSERT INTO @MyTable(col1) VALUES( @i)
SET @I=@i+1
END
-------------------------------------------------
--report generating query
DECLARE @StartDay datetime
SET @StartDay='1/1/2006'
SELECT StudentID , Col1,SUM(noOfHours)
FROM test1.dbo.hours,@MyTable
WHERE DATEDIFF(wk,@StartDay,[date])<=Col1 and COL1<=DATEDIFF(wk,@StartDAy,GETDATE())+1
GROUP BY StudentID,Col1
Kindest Regards,Vasc
-
Nice solution, Vasc...
But you might want to useWHERE DATEDIFF(wk,@StartDay,[date]) + 1 <= Col1
instead, otherwise the first data returned will be from the period from @StartDay to @StartDay + 2 weeks.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply