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


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/


Export Query to Tab Delimited Text File with Powershell





Import-Module -Name 'SQLPS' -DisableNameChecking 
$dt = Get-Date -Format yyyyMMddHHmmss
$workdir='C:\temp'
 
$svrname = "MC\MSSQL2014"
 $db ='testcs'
 
  
#Change default timeout time from 600 to unlimited
$svr =  new-object ('Microsoft.SqlServer.Management.Smo.Server') $svrname
$svr.ConnectionContext.StatementTimeout = 0
 
  

  
$myPath0 = "$workdir\myTabfile$($dt)0.txt"
$myPath = "$workdir\myTabfile$($dt).txt"
  
$q = @"
Select * from dbo.web_contact1
"@
Invoke-SQLCmd -ServerInstance $svr.Name  -Database $db -Query $q |  Export-Csv -NoTypeInformation -delimiter "`t" -Path $myPath0
gc $myPath0 | % {$_ -replace '"', ""} | out-file $myPath -Fo -En ascii
 
 


 

Find same value set in adjacent rows in pair with TSQL


 
 CREATE TABLE [dbo].[DATA1](
[Identity] [int] not null,
[C1] [float] NULL,
[C2] [float] NULL,
[C3] [float] NULL,
[C4] [float] NULL,
[C5] [float] NULL,
[C6] [float] NULL,
[C7] [float] NULL,
[C8] [float] NULL
) ON [PRIMARY]

GO

INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (15, 2, 6, 7, 9, 11, 12, 13, 18)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (14, 2, 4, 7, 8, 10, 11, 14, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (13, 2, 3, 4, 7, 8, 11, 12, 16)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (12, 4, 5, 9, 10, 11, 14, 16, 19)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (11, 1, 3, 5, 7, 8, 11, 12, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (10, 1, 3, 4, 7, 13, 14, 17, 19)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (9, 1, 2, 5, 6, 8, 10, 12, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (8, 1, 4, 6, 12, 14, 15, 16, 17)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (7, 2, 3, 8, 9, 10, 12, 15, 17)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (6, 2, 4, 5, 6, 7, 11, 12, 13)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (5, 1, 2, 9, 10, 11, 13, 14, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (4, 1, 3, 6, 8, 9, 10, 11, 12)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (3, 1, 5, 8, 10, 12, 13, 14, 15)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (2, 5, 6, 7, 11, 12, 13, 15, 18)
GO
INSERT [dbo].[DATA1] ([Identity], [C1], [C2], [C3], [C4], [C5], [C6], [C7], [C8]) VALUES (1, 5, 7, 10, 12, 14, 15, 16, 17)
GO

;with mycte as (
select [identity], c1,c2,c3,c4,c5,c6,c7,c8
,SUM([identity]%2) Over(Order by  [identity]) grp 

from data1)
,mycte1 as
(
Select [identity] 
,Case 
when c1=Lead(c1,1)OVER (Partition by grp  ORDER BY  [identity]) Then c1 
when c1=Lag(c1,1)OVER (Partition by grp  ORDER BY  [identity]) Then c1 else null End c1
,Case 
when c2=Lead(c2,1)OVER (Partition by grp  ORDER BY  [identity]) Then c2 
when c2=Lag(c2,1)OVER (Partition by grp  ORDER BY  [identity]) Then c2 else null End c2
,Case 
when c3=Lead(c3,1)OVER (Partition by grp  ORDER BY  [identity]) Then c3 
when c3=Lag(c3,1)OVER (Partition by grp  ORDER BY  [identity]) Then c3 else null End c3
,Case 
when c4=Lead(c4,1)OVER (Partition by grp  ORDER BY  [identity]) Then c4 
when c4=Lag(c4,1)OVER (Partition by grp  ORDER BY  [identity]) Then c4 else null End c4
,Case 
when c5=Lead(c5,1)OVER (Partition by grp  ORDER BY  [identity]) Then c5 
when c5=Lag(c5,1)OVER (Partition by grp  ORDER BY  [identity]) Then c5 else null End c5
,Case 
when c6=Lead(c6,1)OVER (Partition by grp  ORDER BY  [identity]) Then c6 
when c6=Lag(c6,1)OVER (Partition by grp  ORDER BY  [identity]) Then c6 else null End c6
,Case 
when c7=Lead(c7,1)OVER (Partition by grp  ORDER BY  [identity]) Then c7 
when c7=Lag(c7,1)OVER (Partition by grp  ORDER BY  [identity]) Then c7 else null End c7
,Case 
when c8=Lead(c8,1)OVER (Partition by grp  ORDER BY  [identity]) Then c8 
when c8=Lag(c8,1)OVER (Partition by grp  ORDER BY  [identity]) Then c8 else null End c8
 
from mycte)

SELECT [identity],
       c1,
       c2,
       c3,
       c4,
       c5,
       c6,
       c7,
       c8
FROM   mycte1 



drop table data1



 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f78fcd05-874d-43e2-b780-42e6cc71cba6/need-help-with-query?forum=transactsql#0c7e88ad-72ef-47ee-a167-0e45ecefd56c


JOIN Type in MERGE ON clause

DO your know what kind of JOIN is in Merge Statement? It depends on how WHEN clause is used in your MERGE.

It is a simple INNER JOIN when yo have only one WHEN MATCHED clause;
WHen you have One MATCHE and one WHEN NOT Matched clause , the join is LEFT OUTER JOIN;

WHen you have One MATCHE and two WHEN NOT Matched clause, the join is FULL OUTER JOIN;

A MERGE sample snippet with (HOLDLOCK) hint is recommended by Aaron Bertrand:

http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/


 
 If  (object_id('dbo.mytest','U') is not null)
drop table dbo.mytest;
If  (object_id('dbo.mylog','U') is not null)
drop table dbo.mylog;
 
CREATE TABLE dbo.mytest(id INT);
CREATE TABLE dbo.myLog(action varchar(10), idnew int, idold int);
GO
INSERT dbo.mytest VALUES(1),(5);
GO
 
select * from mytest
 
--Check Requests Log
INSERT INTO  dbo.mylog (action,  idNew, idOld)
SELECT  action,   idNew, idOld
FROM
( 
MERGE dbo.mytest WITH (HOLDLOCK) AS Target
USING (VALUES(1),(2),(3)) AS Source(id) ON Target.id = Source.id
WHEN MATCHED THEN UPDATE SET Target.id = Source.id
WHEN NOT MATCHED THEN INSERT(id) VALUES(Source.id)
WHEN NOT MATCHED BY SOURCE THEN DELETE
 
 OUTPUT $action
, inserted.id 
, deleted.id
 )
AS Changes (Action, idNew, idOld)  ;
 
select * from mylog
select * from mytest