Systems, consultation and development 

Facebook E-mail

Cumulative totals with CTE

Following on from this post, here is an alternative that uses a lot less grunt. (Using the same testtable) ;with cte (i,v,running_total) as { select i,v,v from testtable where i = 1 // initialise the first entry union all select x.i, x.v, cte.running_total + x.v from cte join testtable x on cte.i+1 = x.i }…

Basic cumulative totals etc with T-SQL

Very often when writing reports or dealing with time data you’ll come across the need to see what the total at that point was, not the grand total of all data, just total up to (and maybe including that point). Lets start with a basic set of data declare @testtable as table (i datetime,v int)…

Point-in-time SQL Server recovery

Assuming the backups are from a Full backup set with a set of transaction logs RESTORE DATABASE NewDatabase FROM DISK = ‘F: \BackupFiles\OldFullBackup.bak’ WITH MOVE ‘OldfullDB’ TO ‘C:\DataFiles\TmpDatabase.mdf’, MOVE ‘OldfullDB_log’ TO ‘C:\DataFiles\TmpDatabase_Log.ldf’, NORECOVERY Remember, NORECOVERY means, noone can connect yet (A VERY GOOD THING)! Now, onto the transaction logs For each one, in chronological order…

Home Posts tagged "sql"