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
 


 

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)

 

 

Sort Datetime Columns With Unique Requirements

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/30353d4a-6686-43b1-be32-674e5b05620b/how-to-sort-data-based-on-two-date-columns-among-which-one-has-to-be-grouped?forum=transactsql#760ff585-8a53-42b5-a9ee-98edcbba474e

I have contributed a solution for this thread. Here is the DDL and question requirements.

create table STATEMENT_HISTORY (stmt_key	int, stmt_dte datetime,	stmt_start_dte datetime)
insert into STATEMENT_HISTORY (stmt_key, stmt_dte, stmt_start_dte)
select 12,	'2013-10-13 00:00:00.000',	'2013-07-10 00:00:00.000'
union all select 11,	'2013-10-12 00:00:00.000',	'2013-03-10 00:00:00.000'
union all select 10,	'2013-10-11 00:00:00.000',	'2013-07-10 00:00:00.000'
union all select 9,	'2013-10-10 00:00:00.000',	'2013-09-10 00:00:00.000'
union all select 8,	'2013-09-10 00:00:00.000',	'2013-08-10 00:00:00.000'
union all select 7,	'2013-08-10 00:00:00.000',	'2013-07-10 00:00:00.000'
union all select 6,	'2013-07-10 00:00:00.000',	'2013-06-10 00:00:00.000'
union all select 5,	'2013-06-10 00:00:00.000',	'2013-05-10 00:00:00.000'
union all select 4,	'2013-05-10 00:00:00.000',	'2013-04-10 00:00:00.000'
union all select 3,	'2013-04-10 00:00:00.000',	'2013-03-10 00:00:00.000'
union all select 2,	'2013-03-10 00:00:00.000',	'2013-02-10 00:00:00.000'
union all select 1,	'2013-02-10 00:00:00.000',	'2013-01-10 00:00:00.000'


--select * from STATEMENT_HISTORY
-- order by stmt_dte desc

--My requirement is as follows.
--1. The row with latest stmt_dte should be on top
--2. All other rows that match with the stmt_start_dte of the top row should take next place sorted by stmt_dte among them and so on.





;With mycte as
(select  stmt_key, stmt_dte, dt, col, min(rn) Over(Partition by dt) minRn
from (select stmt_key, stmt_dte,stmt_start_dte 
, row_number() Over(order by stmt_dte DESC) rn
from STATEMENT_HISTORY ) t
Cross Apply (Values(stmt_dte,'sd'),(stmt_start_dte,'ssd')) d(dt,col)
)

Select stmt_key from mycte
WHERE col='ssd'
Order by minrn,stmt_dte DESC, dt DESC

Drop table STATEMENT_HISTORY


/*
stmt_key
12
10
7
11
3
9
8
6
5
4
2
1

*/


 

Epoch and Unix Timestamp Conversion (T-SQL)

create table #temp (eventtimegmt bigint null, eventtimezone int null)
 --CST -18000 seconds
Insert into #temp values(1394461183 , -18000)
  
 
 
  Select eventtimegmt as  epochdt ,  DATEADD(second,eventtimegmt, CAST('1970-01-01 00:00:00' AS datetime)) as gmtdt 
  , DATEADD(second,eventtimegmt + eventtimezone, CAST('1970-01-01 00:00:00' AS datetime)) as localdt

  from #temp

 drop table #temp 


 

A tool to check the conversion:
http://www.epochconverter.com/