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:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/1ee3bc49-08ff-4191-8a57-2813521ae122

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
 --dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0),
 --dateadd(s,-5, dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0)),
   FROM Pulse_Readings
  WHERE  pulse_timestamp=dateadd(s,-5, dateadd(mi, datediff(mi, 0, pulse_timestamp)+1, 0))

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

CREATE TABLE Pulse_Readings
 (pulse_timestamp DATETIME2(0) NOT NULL PRIMARY KEY,
  pulse_value INTEGER NOT NULL
  CHECK (pulse_value > 0));
 

INSERT INTO Pulse_Readings
 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 

--http://social.msdn.microsoft.com/Forums/mr/transactsql/thread/9178b50f-6094-4460-83d2-9ec925d0b4f4

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

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 

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/d3f07e58-1cf7-4975-9c4e-616208fac67a

T-SQL Restore Column’s Filestream Feature After Modifing Filestream Table From SSMS 2008

When you modify a filestream table to add new column or some other changes through SSMS 2008, the filestream attribute of the column will be lost after the change with the data type varbinary(max) only.
Here is a good user case in this MSDN thread: “Imagine you have a production table with thousands and uploaded files in varbinary(max) column
but stored in the file system using the FILESTREAM capability in 2008. A new or existing developer forgets and makes a simple table
modification using SSMS. Suddenly the uploads begin to fail. The DBA finally determines that the FILESTREAM attribute has been removed
from the varbinary column on this table. How to you place the FILESTREAM attribute back on the varbinary(max)
column without destroying the existing data? ”

The way to get back the filestream feature back (to store files in the filegroup folder) is to create a new column with filestream feature and copy data
from the old column to this new column. The filestream feature will be restored in the table.

Here is the script to finish this process:

/* Use your filestream database */

USE yourdatabase
GO

/* Rename the varbinary(max) column  to soemthing like oldColumnName */
EXEC sp_RENAME '[filstramTableName].[binaryfile]', 'oldbinaryfile' , 'COLUMN'
GO

/* create a new varbinary(max) FILESTREAM column to the table */
ALTER TABLE  [dbo].[binaryfile]
ADD [binaryfile] varbinary(max) FILESTREAM NULL
GO

/* Copy the contents of varbinary(max) column to varbinary(max) FILESTREAM column 
to trigger the file move to the filegroup folder. It may take some time to finish the process.
*/

UPDATE  [dbo].[filstramTableName]
SET [binaryfile] = [oldbinaryfile]
GO

/* drop the old column */
ALTER TABLE  [dbo].[filstramTableName]
DROP COLUMN oldbinaryfile
GO

--http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/e4ae96e8-fe79-4235-a89b-018e3db26541/