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);

Advertisements

HOW TO: FIX ERROR – “the ‘microsoft.ace.oledb.12.0’ provider is not registered on the local machine”

http://social.msdn.microsoft.com/Forums/en-US/vstsdb/thread/1d5c04c7-157f-4955-a14b-41d912d50a64


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


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 

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


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

 */