# Beginning of the Minute (T-SQL) and One Usage Example

We can use dateadd and datefiff functions to retrieve the beginning of current minute with this query:

SELECT  dateadd(mi, datediff(mi, 0, CURRENT_TIMESTAMP), 0) beginningOfTheMinute

We can use this method to retrieve last five seconds in any given minutes from a datetime column.
Here is a query to solve the problem I saw in this thread:

There are two solutions given in the thread but with this method we can come up with a query like this:

SELECT pulse_timestamp,pulse_value

You can test the query with Mr. Celko’s sample table:

(pulse_timestamp DATETIME2(0) NOT NULL PRIMARY KEY,
pulse_value INTEGER NOT NULL
CHECK (pulse_value &gt; 0));

VALUES
('2012-11-29 07:58:48', 930), ('2012-11-29 07:58:53', 932), ('2012-11-29 07:58:58', 933),--
('2012-11-29 07:59:03', 935), ('2012-11-29 07:59:08', 935), ('2012-11-29 07:59:13', 937),
('2012-11-29 07:59:18', 938), ('2012-11-29 07:59:23', 940), ('2012-11-29 07:59:27', 941),
('2012-11-29 07:59:33', 943), ('2012-11-29 07:59:38', 944), ('2012-11-29 07:59:43', 945),
('2012-11-29 07:59:48', 946), ('2012-11-29 07:59:53', 948), ('2012-11-29 07:59:58', 949), --
('2012-11-29 08:00:03', 1), ('2012-11-29 08:00:08', 2), ('2012-11-29 08:00:13', 3),
('2012-11-29 08:00:18', 4), ('2012-11-29 08:00:23', 6), ('2012-11-29 08:00:28', 07),
('2012-11-29 08:00:33', 9), ('2012-11-29 08:00:38', 9), ('2012-11-29 08:00:43', 11),
('2012-11-29 08:00:48', 12), ('2012-11-29 08:00:53', 14), ('2012-11-29 08:00:58', 15),--
('2012-11-29 08:01:03', 17), ('2012-11-29 08:01:08', 18), ('2012-11-29 08:01:13', 19),
('2012-11-29 08:01:18', 20), ('2012-11-29 08:01:23', 22), ('2012-11-29 08:01:28', 23),
('2012-11-29 08:01:33', 25), ('2012-11-29 08:01:38', 26), ('2012-11-29 08:01:43', 27),
('2012-11-29 08:01:48', 28), ('2012-11-29 08:01:53', 30), ('2012-11-29 08:01:58', 31), --
('2012-11-29 08:02:03', 33), ('2012-11-29 08:02:08', 34), ('2012-11-29 08:02:13', 35);

# Count Positive/Negative Numbers in a Row (T-SQL)

CREATE TABLE [dbo].[TableName](
[col1] [int] NOT NULL,
[col2] [int] NOT NULL,
[col3] [int] NOT NULL,
[col4] [int] NOT NULL,
[col5] [int] NOT NULL,
[Id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[TableName] ON

GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-15, 12, 9, 5, -7, 1)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-3, 5, -15, 6, -16, 2)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (10, -14, 8, 17, -16, 3)
GO
INSERT [dbo].[TableName] ([col1], [col2], [col3], [col4], [col5], [Id]) VALUES (-11, -10, 5, 2, -4, 4)
GO
SET IDENTITY_INSERT [dbo].[TableName] OFF
GO

SELECT [col1],[col2],[col3],[col4],[col5],pCount,nCount
FROM   (SELECT
--row_number() over(order by newid()) rn,
*
FROM   TableName) src
CROSS apply (SELECT SUM (CASE
WHEN col >= 0 THEN 1
ELSE 0
END) pCount
FROM   (VALUES ([col1]),
([col2]),
([col3]),
([col4]),
([col5])) d (col)) c1
CROSS apply (SELECT SUM (CASE
WHEN col < 0 THEN 1
ELSE 0
END) nCount
FROM   (VALUES ([col1]),
([col2]),
([col3]),
([col4]),
([col5])) d (col)) c2

# Update Duplicate Column With Null (T-SQL)

create table tableName (_g_id int identity(1,1), field1 varchar(50))
insert into tableName values ('aaa'),('aaa'),('ccc'),('aaa'),('bbb'),('bbb')

SELECT field1, Row_number() over(partition by field1 order by newid() ) rn

FROM tableName

;WITH mycte
AS (SELECT field1,ROW_NUMBER()
OVER(
partition BY field1
ORDER BY NEWID() ) rn
FROM   tableName)
UPDATE mycte
SET    field1 = NULL
WHERE  rn > 1

SELECT * FROM   tableName

drop table tablename

# Importing Large Text File with bcp Command

--Run from command line to import one million records and skip the header row
bcp [myDB].dbo.[myTableExistingInDB] in "C:\temp\myFileTest.txt" -c -T -F 2 -S "myServer\myInstanceName"

Or

--Run from SSMS:

DECLARE @sql VARCHAR(4000)
SET @sql='bcp  myDB.dbo.ATable in "C:\temp\test3.txt" -c -T -F 2 -S"'+ @@servername
exec master..xp_cmdshell @sql

# Running Total with Reset – SQL Server 2012

The trick part is to create a group based the resetflag column with a SUM Over first.

CREATE TABLE [dbo].[test1](
[rn] [int] NULL,
[ResetFlag] [int] NULL,
[Value] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (1, 1, 100)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (2, 0, 4)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (3, 0, 8)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (4, 1, 105)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (5, 0, 21)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (6, 0, 5)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (7, 1, 130)
GO
INSERT [dbo].[test1] ([rn], [ResetFlag], [Value]) VALUES (8, 0, 10)
GO
; with mycte as
(select *, sum(resetflag) Over(order by rn) grn from test1)
SELECT rn,ResetFlag,Value,SUM(value)
OVER(
partition BY grn
ORDER BY rn) AS groupRunningTotal
FROM   mycte

# All Combinations of Numbers– T-SQL

This sample code is to show a way for how to get all combination of numbers with T-SQL. Joe Celko has many great solutions with standard compliant SQL query. His SQL books is on my never finishing reading list.
Here is the one I have learned:

;WITH mycte(i)
AS (
SELECT 1 as i
Union ALL
SELECT i +1 as seq FROM mycte WHERE i < 4)

SELECT m1.i AS n1, m2.i AS n2, m3.i AS n3, m4.i AS n4
FROM mycte AS m1
CROSS JOIN mycte AS m2
CROSS JOIN mycte AS m3
CROSS JOIN mycte AS m4
WHERE m1.i NOT IN (m2.i, m3.i, m4.i)
AND m2.i NOT IN (m3.i, m4.i)
AND m3.i NOT IN (m4.i)

--http://www.sqlservercentral.com/blogs/steve_jones/2011/06/07/combinations-and-permutations/
/*
n1	n2	n3	n4
4	1	2	3
3	1	2	4
4	1	3	2
2	1	3	4
3	1	4	2
2	1	4	3
4	2	1	3
3	2	1	4
4	2	3	1
1	2	3	4
3	2	4	1
1	2	4	3
4	3	1	2
2	3	1	4
4	3	2	1
1	3	2	4
2	3	4	1
1	3	4	2
3	4	1	2
2	4	1	3
3	4	2	1
1	4	2	3
2	4	3	1
1	4	3	2

*/