Fill Null from Previous Non Null Value –(float or decimal)

 If (Object_Id('dbo.T1','U') is not null)
 Drop table dbo.T1;

CREATE TABLE T1
(
  id INT NOT NULL CONSTRAINT PK_T1 PRIMARY KEY,
  col1 INT NULL
  ,col_Float float  NULL
  ,col_Decimal Decimal(8,2)  NULL

);

 

INSERT INTO dbo.T1(id, col1, col_Decimal, col_Float) VALUES
( 2, NULL,NULL,NUll),
  ( 3,   10,8.45,3.33000258),
  ( 5,   -1,9.87,NULL),
  ( 7, NULL, NULL,NULL),
  (11, NULL, 5.55,NULL),
  (13,  -12, NULL,7.77123456),
  (17, NULL, NULL,NULL),
  (19, NULL, NULL,NULL),
  (23, 1759,1.23,0.000129);

  SELECT id, col_float, 
  CAST(Cast(SUBSTRING(
      MAX( CAST(id AS BINARY(4)) + Cast(Cast(col_float as decimal(38,17)) as  BINARY(38)) ) 
        OVER( ORDER BY id
              ROWS UNBOUNDED PRECEDING ),
      5, 38)
    AS  decimal(38,17) ) as float ) AS lastvalFloat

  

		, col_Decimal, CAST(
    SUBSTRING(
      MAX( CAST(id AS BINARY(4)) + CAST(col_Decimal AS BINARY(8)) )
        OVER( ORDER BY id
              ROWS UNBOUNDED PRECEDING ),
      5, 8)
    AS   Numeric(8,2)) AS lastvalDecimal


	-- 


FROM dbo.T1;

--Refer to: Itzik Ben-Gan
--http://sqlmag.com/t-sql/last-non-null-puzzle

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/eab6bf17-1901-4b9b-8ab6-352d7b1519f2/lag-lead-functions-any-way-to-retrieve-the-1st-nonnull-values?forum=transactsql

Advertisements

Copy Database Files While They are in Use with Hobocopy

Download a copy Hobocopy from GitHub:
https://github.com/candera/hobocopy/downloads
After download the zipped file, unzip it to a folder at your choice.

Use the following Powershell script to copy the database files that are in use.

Powershell to copy database files while they are in use
C:\bcp\Hobocopy.exe C:\MSSQL\Data c:\temp\hotcopied\ testdb*

You can attach these files to SQL Server instance to access the database.


Custom Date and Time formats for the FORMAT Function from MSDN

Custom Date and Time formats for the FORMAT Function
This link is hiding deep when you try to find it:

https://msdn.microsoft.com/en-us/library/ee634398.aspx

A sample:



select format(getdate(),'HH:mm')  [HourMinute24]
, format(getdate(),'HH:mm tt') [HourMinuteAM/PM]

 

Binary conversion: numeric and datetime2 internal storage

Two links I found that are helpful.

http://weblogs.sqlteam.com/peterl/archive/2010/12/15/the-internal-storage-of-a-datetime2-value.aspx

http://raresql.com/2012/10/07/sql-server-convert-varbinary-to-numeric-vice-versa/


Fill Null From Previous Non-NULL Value in A Column


CREATE  TABLE Table1 (Prod_Order int, Datettime datetime, Datavalue Decimal(18,7))

INSERT INTO Table1 values('106', '2015-02-20 09:00:00.000' , 1010.3499756)
INSERT INTO Table1 values('106', '2015-02-20 10:00:00.000',    null)
INSERT INTO Table1 values('106', '2015-02-20 11:00:00.000' ,  3113.1999512)
INSERT INTO Table1 values('106', '2015-02-20 12:00:00.000' ,           null)
INSERT INTO Table1 values('106', '2015-02-20 13:00:00.000' ,           null)
INSERT INTO Table1 values('106', '2015-02-20 14:00:00.000' ,            null)
INSERT INTO Table1 values('106', '2015-02-20 15:00:00.000' ,           null)
INSERT INTO Table1 values('106', '2015-02-20 16:00:00.000' ,  5219.0998536)


--select * from Table1
 
SELECT Prod_Order, /*Datavalue, */
Datettime,
CAST(SUBSTRING(MAX( CAST(Datettime AS BINARY(32)) + CAST(Datavalue AS BINARY(12)) )
OVER( ORDER BY Datettime ASC ROWS UNBOUNDED PRECEDING ),33,12) AS Decimal(18,7) ) lastNonNullval

  
FROM Table1
 

drop table Table1
/*



*/

--Check this link about this solution:
--Itzik Ben-Gan
--http://sqlmag.com/t-sql/last-non-null-puzzle

--
/*
From your question, you need to use right data type for table columns and I made some changes to provide the solution posted.

1.Use the right data type for your table columns (your have datetime data not datetime2 and Datavalue is not varchar(200));
2. An empty string '' is not the same as a null value and null is null and it is an important concept.

By the way, thanks for providing your table structure and sample data.

*/

 

https://social.msdn.microsoft.com/Forums/en-US/b3167796-5f96-4188-9230-35076f5cb44c/sql-to-copy-data-from-previous-non-null-rows?forum=transactsql


Calendar Table With ISO_Week


 
DECLARE  @Year SMALLINT = 2015, @FirstISOWKDay DATETIME

SELECT @FirstISOWKDay=dateadd(week, Datediff(week,0,datefromparts(@year,1,1)),-1)

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n)-1 FROM Num2)
----0-10200

SELECT  
          theDay    = DATEADD(DAY, n*7+num, @FirstISOWKDay)
		 ,Start    = DATEADD(DAY, n*7, @FirstISOWKDay)
        ,[End]      = DATEADD(DAY, n*7 + 6, @FirstISOWKDay)
        ,Friendly   = CONCAT(format(DATEADD(DAY, n*7, @FirstISOWKDay),'MMM dd, yyyy'),' To ', format(DATEADD(DAY, n*7+6, @FirstISOWKDay),'MMM dd, yyyy'))
        ,ISOWEEK    = DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay+1))
		,ISO8601Year = Case 
		When 
		Year(DATEADD(DAY, n*7, @FirstISOWKDay))<>Year(DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) Then		
		Case when  DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay+1))=1 Then Year(DATEADD(DAY, n*7 + 6, @FirstISOWKDay))  
		when  DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay+1))>1 Then Year(DATEADD(DAY, n*7 , @FirstISOWKDay))
		End 
		Else
		Year(DATEADD(DAY, n*7+num, @FirstISOWKDay))
		End  
		 
FROM Nums
 
cross apply (values(0),(1),(2),(3),(4),(5),(6)) d(num)

WHERE DATEPART(YEAR, DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) >= @Year and DATEPART(YEAR, DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) < @Year+2

--http://stackoverflow.com/questions/14133816/how-do-i-build-iso-week-number-table-programatically-in-t-sql-query



 

Create a Striped Backup and restore the DB with T-SQL


 
 

BACKUP DATABASE MyTestDB
 TO DISK = 'C:\MSSQL\bak\MyTestDB_20150219_stripe1_of_2.bak'
  ,DISK = 'E:\MSSQL\bak\MyTestDB_20150219_stripe2_of_2.bak'
   WITH FORMAT,COMPRESSION


   ----Restore stripped backup
RESTORE DATABASE MyTestDB2 
FROM  
DISK='C:\MSSQL\bak\MyTestDB_20150219_stripe1_of_2.bak', 
DISK='E:\MSSQL\bak\MyTestDB_20150219_stripe2_of_2.bak'
WITH
MOVE 'MyTestDB' TO 'c:\mssql\bak\MyTestDBCopy_Data.mdf', 
MOVE 'MyTestDB_Log' TO 'c:\mssql\bak\MyTestDBCopy_Log.ldf', 
RECOVERY, REPLACE, STATS = 10;
  
GO



 

http://www.sqlservercentral.com/articles/dba+skills/122679/

http://sqlserverzest.com/2013/09/03/sql-server-how-to-perform-striped-database-backup-restore-using-t-sql-command/