Database

"In the confrontation between the stream and the rock, the stream always wins - not through strength but perseverance." (H. Jackson Brown)

create procedure RepProjHours<br />
as begin<br />
  set nocount on<br />
<br />
  --Create temporary table with line-number.<br />
  create table #temp1 (<br />
    LineNo integer identity not null,<br />
    LogDate datetime null,<br />
    ProjCode varchar(3) null,<br />
    Hrs decimal(6,2) null<br />
    )<br />
  insert into #temp1<br />
    select LogDate, ProjCode, Hrs<br />
    from MyTimesheetTable<br />
    order by LogDate, ProjCode, Hrs desc  --Nice ordering?<br />
<br />
  --Find the first timesheet entry for each day.<br />
  create table #temp2 (<br />
    LogDate datetime null,<br />
    MinLineNo integer not null,<br />
    TotalHrs decimal(6,2) not null<br />
    )<br />
  insert into #temp2<br />
    select LogDate, Min(LineNo), Sum(Hrs)<br />
    from #temp1<br />
    group by LogDate<br />
<br />
  --Join everything together (with subtotal on first line for each date).<br />
  select T1.LogDate, T1.ProjCode, T1.Hrs, T2.TotalHrs<br />
    from #temp1 T1<br />
    left outer join #temp2 T2<br />
      on T2.LogDate = T1.LogDate<br />
      and T2.MinLineNo = T1.LineNo<br />
    order by T1.LineNo<br />
end

DECLARE @t varchar(8000)
SET @t = ''
SELECT @t = columnname + @t FROM thetable
SELECT @t