Split Time Duration Into Hourly Intervals (T-SQL)

http://social.msdn.microsoft.com/Forums/en-US/1b96351f-d4fb-4cf4-ba24-07b2fb5f5007/cartesian-time-data-across-multiple-hourly-rows?forum=transactsql

 
  create table test (EventTime    datetime,     EventLengthSeconds int)
  insert into test values ('2014-04-25 13:31:16.857', 6657)

  ;with mycte as 
  (
  select EventTime as EventTime1, number ,EventLengthSeconds
  ,datediff(second,Eventtime,DATEADD(hour, DATEDIFF(hour,0,EventTime)+number+1,0))  as EventLengthSeconds1
  ,DATEADD(hh, DATEDIFF(hh,0,EventTime)+number+1,0) as EventTime2 
  ,EventLengthSeconds -(number-1)*3600-datediff(second,Eventtime,DATEADD(hour, DATEDIFF(hour,0,EventTime)+1,0)) as EventLengthSeconds2
   FROM [master].[dbo].[spt_values],test
  where type='p'  and (EventLengthSeconds*1./3600)+1>=number
  )

  select  
  case when number=0 then EventTime1 else EventTime2 end EventTime,
  case 
  when number=0 then EventLengthSeconds1
  when EventLengthSeconds2>=3600 then 3600
  when EventLengthSeconds2<3600 then EventLengthSeconds2
  end as EventLengthSeconds

  from mycte



--Clean up
DROP TABLE test		

 

Find Next Anniversary Date (T-SQL)

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9dceaf0b-c939-43fa-bf60-b2377a096d4a/anniversary-date-calculation?forum=transactsql#fa6c226a-ef27-4c5e-8d5f-7eaac4e4dec3

 
 create table test1
(InvestorID int ,pension_start_date datetime)
INSERT INTO test1 (InvestorID,pension_start_date) values (1,'01/01/14'), (2,'05/05/13'),(3,'01/31/13'),(4,'10/25/12'),(5,'03/25/10')

select InvestorID, pension_start_date, CASE WHEN
 (DATEADD(year,DATEDIFF(year, pension_start_date  ,current_timestamp) , pension_start_date) > current_timestamp)
THEN Dateadd(Year,DATEDIFF(year, pension_start_date  ,current_timestamp) , pension_start_date)
ELSE  Dateadd(Year,DATEDIFF(year, pension_start_date  ,current_timestamp)+1 , pension_start_date) END [Anniversary Date] 
FROM test1

--Clean up
DROP TABLE test1		

 

Four Ways to Insert Value into IDENTITY Column in SQL Server

So we can summarize four ways to add values to identity column in SQL Server.

1.SET IDENTITY_INSERT ON;

SET IDENTITY_INSERT [dbo].test1 ON
Go
Insert into test1 (col1) values(11),(12)

SET IDENTITY_INSERT [dbo].test1 OFF
Go

2. Insert into table with default values ;

Insert into test1 default values ;

3. Bcp with -E flag along wiht a format file;

4. Bulk insert with KEPPIDENTITY.



use mytest4
go
 
create table test1 (col1 int primary key identity(1,1))
Go
Insert into test1 default values
go 5

 
SET IDENTITY_INSERT [mytest4].[dbo].test1  ON
Go 
Insert into test1 (col1) values(11),(12)
 
SET IDENTITY_INSERT [mytest4].[dbo].test1  OFF
Go
 
 
--
select * from test1
--drop table test1
  

--Create a format file for bcp and bulk insert
DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[test1] format nul -c -x -f  C:\temp\FORMATFIle.xml -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql
 
  --  -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited 
 
 
----bcp Export
--DECLARE @sql2 VARCHAR(4000) 
--SET @sql2='bcp [mytest4].[dbo].[test1] out "C:\temp\test1.csv" -f "C:\temp\FORMATFIle.xml" -F 1  -T  -t\t  -b 1000 -S'+ @@servername
--exec master..xp_cmdshell @sql2
 
--go
 
 --Remove data from table
 Truncate table test1
 
--Bcp Import
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[test1] IN "C:\temp\test1.csv" -f "C:\temp\FORMATFIle.xml" -F 1 -E -T  -t\t  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql3

--/* "-E Specifies that identity value or values in the imported data file are to be used for
--the identity column. If -E is not given, the identity values for this column in the data file being imported are ignored.
--*/
 


select * from test1

 

 
/*
BULK Insert Options
*/
 
  --Remove data from table
 Truncate table test1
----Option 2: Bulk insert with identity insert
 
BULK INSERT [mytest4].[dbo].test1
   FROM 'C:\temp\test1.csv'
   WITH (
      KEEPIDENTITY
     -- ,FORMATFILE='C:\temp\FORMATFIle.xml'
   );
 /*
 KEEPIDENTITY:
 Specifies that identity value or values in the imported data file are to be used for the identity column. 
 */
 
 
select * from test1
 
drop table test1


 

Sample Code for Bulk Insert and BCP



use mytest4
go

Create Table subtest
(
[ID] [int] IDENTITY(1,1) NOT NULL,
emailID Varchar(50)
)
Go
Insert into subtest values('aaa@abc.com'),('asd@gmail.com')

SET IDENTITY_INSERT [mytest4].[dbo].[subtest]  ON
Go

Insert into [mytest4].[dbo].[subtest] (ID, emailID) values(11,'aaa@abc.com'),(12,'asd@gmail.com')

SET IDENTITY_INSERT [mytest4].[dbo].[subtest]  OFF
Go

go
--select * from Subtest
--drop table subtest
 


DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[subtest] format nul -c -x -f  C:\temp\FORMATFIle.xml -T -t\, -S'+ @@servername
exec master..xp_cmdshell @sql

  --    -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited 


--bcp Export
DECLARE @sql2 VARCHAR(4000) 
SET @sql2='bcp [mytest4].[dbo].[subtest] out "C:\temp\subids.csv" -f "C:\temp\FORMATFIle.xml" -F 1  -T  -t\,  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql2

go


--Bcp Import
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[subtest] IN "C:\temp\subids.csv" -f "C:\temp\FORMATFIle.xml" -F 1 -E -T  -t\,  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql3

/* "-E Specifies that identity value or values in the imported data file are to be used for 
the identity column. If -E is not given, the identity values for this column in the data file being imported are ignored.
*/


--Truncate table Subtest
--go


/*
BULK Insert Options
*/

--Option 1: Bulk insert WITHOUT  identity insert

BULK INSERT [mytest4].[dbo].[subtest]
   FROM 'C:\temp\subids.csv'
   WITH (
          FORMATFILE='C:\temp\FORMATFIle.xml'
   );


--Option 2: Bulk insert with identity insert

BULK INSERT [mytest4].[dbo].[subtest]
   FROM 'C:\temp\subids.csv'
   WITH (
      KEEPIDENTITY,
      FORMATFILE='C:\temp\FORMATFIle.xml'
   );

--Option 3:  Bulk insert with identity insert   No format file
Bulk Insert Subtest
From 'C:\temp\subids.csv'
With 
(
KEEPIDENTITY,
FieldTerminator =','
--FORMATFILE = 'C:\temp\FORMATFIle.xml'
);


select * from Subtest

drop table subtest
 


 

A Sample to remove non-numeric values from columns


CREATE TABLE [dbo].[DecoderModels](
	[PK_DecoderModelID] [bigint] IDENTITY(1,1) NOT NULL Primary Key,
	[ModelName] [nvarchar](50) NOT NULL,
	[SN_First] [varchar](50) NOT NULL,
	[SN_Last] [varchar](50) NOT NULL)

declare @sn varchar(50)='aao255'

Insert into [DecoderModels] Values 
('aaa','om000001','om499999')
,('bbb','0001','9999')
,('ccc','aao000001','aao9999999')

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1),

processTable as (
    select [SN_First], [SN_Last]
	, ISNULL(Cast(Col1 as int),0) as Col1
	, ISNULL(Cast(Col2 as int),99999999) as Col2
	, Cast(Col3 as int) Col3
	,Col4
    from [DecoderModels]
        cross apply (
            select (select C + ''
            from (select N, substring([SN_First], N, 1) C from Num1  where N<=datalength([SN_First])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p (Col1)

		   cross apply (
            select (select C + ''
            from (select N, substring([SN_Last], N, 1) C from Num1  where N<=datalength([SN_Last])) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p2 (Col2)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p3 (Col3)

		   cross apply (
            select (select C + ''
            from (select N, substring(@SN, N, 1) C from Num1  where N<=datalength(@SN)) t
            where PATINDEX('%[^0-9]%',C)> 0
            order by N
            for xml path(''))
        ) p4 (Col4)

 
)

SELECT [SN_First], [SN_Last], Col1,Col2,Col3,col4
FROM processTable
WHERE Cast(Col3 as int) Between cast(Col1 as int) And cast(Col2 as int) 
AND ([SN_First] Like Col4+'%' AND  [SN_Last]  Like Col4+'%') Or (IsnuMeric([SN_First])=1 And Isnumeric([SN_Last])=1 And Col4 Is NUll)



drop table [DecoderModels]


 

http://stackoverflow.com/questions/18625548/t-sql-select-query-to-remove-non-numeric-characters


Retrieve XML Name Value Pair with T-SQL


Declare @s as NVarchar(2000) 
='<d><nv n="ParamID" v="Param/FYIInc./354088488" />
<nv n="ParamId" v="151af628-0000-0000-0000-000025700000" />
<nv n="ExtID" v="Station/Television" />
<nv n="StationId" v="0000357e-0000-0000-0000-000000587000" />
<nv n="StartTime" v="03/11/2014 02:01:00" />
<nv n="IsDynamic" v="True" />
<nv n="IsRecurring" v="True" />
<nv n="Frequency" v="EveryDay" />
<nv n="DurationSecs" v="0" /></d>'

--from a string
SELECT
    S.a.value('@n', 'varchar(50)') as name,
    S.a.value('@v', 'varchar(50)') as value   
FROM (SELECT Cast(@s as XML) as sXML) as t
CROSS APPLY sXML.nodes('d/nv') as S(a)


--from a table
declare @test table(id int identity(1,1), myString nvarchar(2000) );
INSERT INTO @test (myString) values(@s);

SELECT 
S.a.value('@n', 'varchar(50)') as name,
S.a.value('@v', 'varchar(50)') as value
FROM (SELECT Cast(myString as XML) as sXML 
FROM @test) t 
CROSS APPLY sXML.nodes('/d/nv') S(a)

 

Divide a Year into different sections (datetime function, number function)



declare @groupNum int=5

;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num1)


SELECT  dateadd(day,(365.25/@groupNum)*(num-1), dt) StartDate 
, CASE WHEN num=@groupNum Then  DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)  
Else dateadd(day,(365.25/@groupNum)*(num)-1 , dt) End EndDate 
from (select '1/1/2014' dt) m
cross apply (select n from Nums WHERE n<=@groupNum) d(num)