Tsql Calculating Sum Of Numerous Fields

I have this kind of data: Date Count1 Count2 Count3 ... Countxx 01-05-2012 1 0 1 2 01-05-2012 2 1 3 0 01-05-2012 2 3 3

Solution 1:

createprocedure USP_FindSum @tablenamevarchar(100)                
asbegincreatetable #temp(id intidentity(1,1),name varchar(100))
 declare@sqlcmd nvarchar(max)=''SET@sqlcmd= N'Insert into #temp select name from sys.columns col_table where        
 col_table.object_id=object_id('''+@tablename+''')'EXEC sp_executesql @sqlcmddeclare@sqlsegvarchar(max)=''declare@tempcountintdeclare@iint=1select@tempcount=COUNT(id) from #temp
 while(@i<=@tempcount)
 BEGINdeclare@CNamevarchar(100)
   SELECT@CName= name from #temp where id=@i
   if(@i!=@tempcount)
    SET@sqlseg=+@sqlseg+'sum('+@CName+')'+','elseSET@sqlseg=+@sqlseg+'sum('+@CName+')'SET@i=@i+1ENDSET@sqlcmd=N'select '+@sqlseg+' from '+@tablenameEXEC sp_executesql @sqlcmdDROPTABLE #temp
END

Assuming all the columns in the table are summable. As your requirement is weird this workaround may also be so.

Just pass the table name as parameter and execute,

Exec USP_FindSum '<tablename here>'

Solution 2:

There is no way to sum a variable list of columns, you have to specify them all.

One way to look up the previous row is outer apply, like:

selectDate
,       cur.count1 - isnull(prev.count1,0) as Delta1
from    Table1 cur
outer apply
        (
        select  top 1 *
        from    Table1 prev
        where   prev.Date < cur.Dateorderby
                prev.Date desc
        ) prev

Another way is to join the tables based on row_number():

; with  t1 as
        (
        select  row_number() over (orderbyDate) as rn
        ,       *
        from    Table1
        )
selectDate,
,       cur.count1 - isnull(prev.count1,0) as Delta
from    t1 cur
left join    
        t1 prev
on      cur.rn = prev.rn + 1