Sliding AVG From Both Directions–T-SQL

Posted: August 1, 2014 | Author: Jingyang Li | Filed under: SQL Server 2012, SQL Server 2014 | Leave a comment

CREATE TABLE [dbo].[testTable](
	[colA] [char](2) NULL,
	[colDate] [date] NULL,
	[colC] [char](2) NULL,
	[colD] [char](4) NULL,
	[colValue] [tinyint] NULL
) ON [PRIMARY]

INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0000', 2)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0005', 4)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0010', 5)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0015', 3)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hA', N'0020', 1)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0025', 6)--changed from '0035'
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0030', 2)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0035', 3)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0040', 4)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-01' AS Date), N'hB', N'0045', 3)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0000', 1)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0005', 9)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0010', 10)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0015', 8)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hA', N'0020', 7)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0025', 2)--changed from '0035'
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0030', 4)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0035', 6)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0040', 1)
INSERT [dbo].[testTable] VALUES (N'cA', CAST(N'2014-06-02' AS Date), N'hB', N'0045', 8)

;with mycte as (SELECT *, avg([colValue]) Over(PARTITION BY [colA], ColDate,Colc   Order by colD  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)  myAvg1,
avg([colValue]) Over(PARTITION BY [colA], ColDate,Colc   Order by colD  ROWS BETWEEN CURRENT ROW AND 2 Following )  myAvg2
FROM testTable)

,mycte1 as (select *, row_number() Over(partition by [colA], ColDate, ColC Order by myavg1 DESC) rn
, max(myAvg1) Over(partition by [colA], ColDate, ColC) maxAvg3 
, max(myAvg2) Over(partition by [colA], ColDate, ColC) maxAvg4 from mycte )

,mycte2 as (Select colA, ColDate, ColC, ColD, rn, myAvg1,myAvg2
, Case When maxAvg3>maxAvg4 Then maxAvg3 else maxAvg4 End maxAvg 
,row_number() Over(partition by rn Order by Case When maxAvg3>maxAvg4 Then maxAvg3 else maxAvg4 End  DESC) rn2
from mycte1
WHERE rn=1)

SELECT colA, ColDate,  ColC, ColD,  maxAvg FROM   mycte2
WHERE  rn2 = 1 



drop table testTable
/*colA	ColDate	ColC	ColD	maxAvg
cA	2014-06-02	hA	0015	9
*/

http://social.msdn.microsoft.com/Forums/en-US/54987b88-a236-4cce-b3d8-f264c8eca276/help-with-a-complex-group-by?forum=transactsql

A few Queries that are related to Agent Information

Posted: June 4, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server 2012, SQL Server 2014 | Leave a comment

You can tweak a few useful queries Datta Sindol compiled to make them useful for you if you want to get some information about agent jobs from MSDB.
Here is the link:

http://www.mssqltips.com/sqlservertip/2561/querying-sql-server-agent-job-information/

Query Result To Excel with Messed Up Rows (SSMS 2012)

Posted: March 28, 2014 | Author: Jingyang Li | Filed under: SQL Server 2012 | Leave a comment

When you export your query result from SSMS 2012 (not SSMS 2008) to a text file or copy and paste the result (with header), you may run into problem with columns (such as varcahr(max)…) that include Tab — char(9), Line feed — char(10) or Carriage return — char(13). You have garbled rows due to these invisible chars.

A quick fix is to remove them by using nested REPLACE functions in your SELECT query.
Here is and example:


--Sample code
--

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),''), CHAR(13),'') ,CHAR(9),'')  as yourcolumn

--

SELECT Replace( REPLACE(REPLACE(yourCOlumn, CHAR(10),”), CHAR(13),”) ,CHAR(9),”) as yourcolumn

If you want to remove them forever, you can use an UPDATE command to update these columns in your source table.

Format File for BCP Or Bulk Insert

Posted: March 7, 2014 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2005, SQL Server 2008, SQL Server 2012 | Leave a comment

------bcp create non-XML format file

--One option:
 DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [myDB].[dbo].[myTable] format nul -c  -f  C:\temp\myFormat2.fmt -T -t\t  -S'+ @@servername
exec master..xp_cmdshell @sql

--Another

exec master..xp_cmdshell  'bcp [myDB].[dbo].[myTable] format nul   -f  C:\temp\myFormatFile.fmt  -c -T -S myServer\MyInstance';

go


------bcp create XML format file

----Another Sample:
DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [myDB].[dbo].[myTable] format nul -c -x -f  C:\temp\yourFORMATFIle.xml -T  -t\t -S'+ @@servername
exec master..xp_cmdshell @sql


exec master..xp_cmdshell  'bcp [myDB].[dbo].[myTable] format nul -c -x -f  C:\temp\myFormatFile.xml  -T -S myServer\MyInstance';

go

--http://technet.microsoft.com/en-us/library/ms179250.aspx

Use a Format File to Map Table Columns to Data-File Fields (SQL Server)
–http://technet.microsoft.com/en-us/library/ms191175.aspx

Use a Format File to Skip a Table Column (SQL Server)
–http://technet.microsoft.com/en-us/library/ms179250.aspx

Use a Format File to Skip a Data Field (SQL Server)
–http://technet.microsoft.com/en-us/library/ms187908.aspx

XML Format file
–Simply remove the that column from section

Non-XML Format File
— Keep the leftmost row number as is (Host file field order) and change the Server coloumn order value to 0 and renumber all other columns as well.

Using a tariff table with banded pricing units to calculate a total price

Posted: March 4, 2014 | Author: Jingyang Li | Filed under: SQL Server 2012 | Leave a comment

http://social.msdn.microsoft.com/Forums/en-US/0aed0dfd-5e82-47c5-a4d6-d10f94f07344/trouble-using-a-tariff-table-with-banded-pricing-units-to-calculate-a-total-price?forum=transactsql


CREATE TABLE [dbo].[tariff_ratebrek](
	[break_seq_num] [int] NULL,
	[break_amount] [float] NULL,
	[break_unit] [float] NULL,
	[break_point] [float] NULL
) 
 

INSERT INTO tariff_ratebrek
VALUES (0,400,0,0),
(1,100,0,2),
(2,200,1,3),
(3,150,1,5),
(4,250,1,6),
(5,400,1,7),
(6,250,1,10),
(7,220,1,28),
(8,200,1,55),
(9,150,1,9999)

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



,mycte as
(
select [break_seq_num], break_amount,  [break_unit], t.break_point, d.num ,  row_number()over(order by num ) rn
from [tariff_ratebrek] t 
RIGHT Join  Nums d
 
 on t.break_point=d.num
)

,mycte1 as(
Select break_unit, bu,[break_point], bp,  ba, break_amount, m.num from mycte m
Cross apply (select top 1 break_unit from mycte m1 WHERE m.num>=m1.num and break_unit is not null Order by num DESC) d1(bu) 
Cross apply (select top 1 [break_point] from mycte m2 WHERE m.num>=m2.num and break_point is not null   Order by num DESC) d2(bp) 
outer APPLY (SELECT TOP 1 break_amount FROM   mycte WHERE  rn >= m.rn AND break_amount IS NOT NULL   ORDER  BY rn  ) d3( ba)
)

select top 1  num, bu as [break_unit],bp as [break_point], ba as break_amount
,  sum(Case WHEN bu=0 and bp=0 and num<>0 Then 0 Else ba End ) Over(Order by num) amt  
from mycte1
WHere num<=12
ORDER  BY num DESC


option(maxrecursion 999)
 
 
 


drop table [tariff_ratebrek]

Create Format File For bcp (T-SQL)

Posted: February 28, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

We can use bcp command to generate a format file from an existing table.


  exec master..xp_cmdshell  'bcp myDB..myTable format nul -T -n -f c:\temp\myformatfile.fmt -S myMachine\myDBInstance';

  --http://technet.microsoft.com/en-us/library/ms191516.aspx
  --http://technet.microsoft.com/en-us/library/ms191479(v=sql.110).aspx


--Sample usage of the format file to retrieve a text file using this format file

SELECT 
* 
--or column list from format file
FROM  OPENROWSET(BULK N'C:\temp\mytextfile.txt',  FORMATFILE='C:\temp\myformatfile.fmt') AS t

Recursive Sample for Employee Structure (T-SQL)

Posted: February 17, 2014 | Author: Jingyang Li | Filed under: SQL Server 2005, SQL Server 2008, SQL Server 2012 | Leave a comment


Create Table Test(empid  int primary key,  empName varchar(50),  hr_id int);
Insert into Test  Values
(16,'John',12),(15,'Joe',13),(13,'Chad',12),(12,'David',11)
,(11,'Adam',null),(6,'Jean',5),(5,'Paige',4)
,(4,'Steven',3),(3,'Dan',2),(2,'Ryan',null),(10,'Ruth', 9)
,(9,'Greg',8),(8, 'Sam',7),(7,'Pane',1),(1,'Brandon', null);

;With mycte AS
(Select empid, empName, Cast('' AS varchar(max)) AS path,  empid As ManagerEmpId,  empid AS Seq
From Test Where hr_id Is Null 
Union All
Select t.empid, t.empName, Cast(t.hr_id AS varchar(max)) + Case When m.path <> '' Then ','+ m.path Else '' End AS path, m.ManagerEmpId, m.Seq + t.empid AS Seq 
From mycte m
Inner Join Test t On t.hr_id = m.empid)

Select  * From mycte
Order By ManagerEmpId, Seq;

go

Drop Table Test;

Fill Null Value in a column with extra requirement

Posted: February 14, 2014 | Author: Jingyang Li | Filed under: SQL Server 2005, SQL Server 2008, SQL Server 2012 | Leave a comment

http://social.msdn.microsoft.com/Forums/en-US/78715bd3-e309-44b1-afb8-f157124e4708/help-required-for-complex-query?forum=transactsql

CREATE TABLE tt (id INT,ord int,dt1 datetime, wd int)

INSERT INTO tt VALUES (101,'1','2011-12-20','484') 
INSERT INTO tt VALUES (101,'2','2011-02-12','444') 
INSERT INTO tt VALUES (101,'3','2011-02-12','444') 
INSERT INTO tt VALUES (101,'4','2011-02-14',NULL)

 
INSERT INTO tt VALUES (102,'1','2013-05-27','544') 
INSERT INTO tt VALUES (102,'2','2013-06-02','544') 
INSERT INTO tt VALUES (102,'3','2013-06-03',NULL) 
INSERT INTO tt VALUES (102,'4','2013-06-10',NULL) 
INSERT INTO tt VALUES (102,'5','2013-07-08',NULL) 
INSERT INTO tt VALUES (102,'6','2013-07-08','690') 
INSERT INTO tt VALUES (102,'7','2013-07-10','690')

;with mycte as
(
SELECT id,ord,dt1,wd, row_number()over(order by id, dt1,wd desc) rn
 
FROM tt A)

SELECT m.id,m.ord,m.dt1,m.wd,d2.wd2  FROM mycte m
OUTER APPLY (SELECT TOP 1 wd  FROM mycte
WHERE id=m.id and rn<= m.rn AND wd IS NOT NULL
ORDER BY rn desc) d2 (wd2 )
 
Order by id,ord

 

CREATE TABLE  tt_result (id INT,ord int,dt1 datetime, wd int)

INSERT INTO tt_result VALUES (101,'1','2011-12-20','484') 
INSERT INTO tt_result VALUES (101,'2','2011-02-12','444') 
INSERT INTO tt_result VALUES (101,'3','2011-02-12','444') 
INSERT INTO tt_result VALUES (101,'4','2011-02-14','444')

INSERT INTO tt_result VALUES (102,'1','2013-05-27','544') 
INSERT INTO tt_result VALUES (102,'2','2013-06-02','544') 
INSERT INTO tt_result VALUES (102,'3','2013-06-03','544') 
INSERT INTO tt_result VALUES (102,'4','2013-06-10','544') 
INSERT INTO tt_result VALUES (102,'5','2013-07-08','690') 
INSERT INTO tt_result VALUES (102,'6','2013-07-08','690') 
INSERT INTO tt_result VALUES (102,'7','2013-07-10','690')

 
select * from tt_result

 drop table tt_result,tt

UPDATE with MERGE

Posted: February 14, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

http://social.msdn.microsoft.com/Forums/en-US/ae2324e1-dc6a-4142-a446-956f27a408ed/update-one-table-with-the-max-value-of-another-table?forum=transactsql


CREATE TABLE [dbo].[General](
	[a_id] int NOT NULL,
	[a_date] datetime NOT NULL,
	[c_date] datetime NULL)


CREATE TABLE [dbo].[Specific](
	[a_id] int NOT NULL,
	[a_date] datetime NOT NULL,
	[b_id] int NOT NULL,
	[d_date] datetime NULL)

insert into General values (111,'Jan 1 2013', null)
insert into General values (112,'Jan 1 2013', 'Mar 10 2014')
insert into General values (113,'Jan 1 2013', null)
insert into General values (114,'Feb 1 2013', null)
insert into General values (115,'Feb 1 2013', 'Apr 1 2013')
insert into General values (116,'Feb 1 2013', 'Jan 1 1970')

insert into Specific values (111,'Jan 1 2013',1, 'Jan 1 2013')
insert into Specific values (111,'Jan 1 2013',2, 'Feb 1 2013')
insert into Specific values (111,'Jan 1 2013',3, 'Mar 1 2013')
insert into Specific values (112,'Jan 1 2013',1, 'Jan 1 2013')
insert into Specific values (112,'Jan 1 2013',2, 'Feb 1 2013')
insert into Specific values (113,'Jan 1 2013',1, 'Jan 1 1970')
insert into Specific values (114,'Feb 1 2013',1, null)
insert into Specific values (115,'Feb 1 2013',1, 'Jan 15 2013')
insert into Specific values (115,'Feb 1 2013',2, 'Feb 15 2013')
insert into Specific values (116,'Feb 1 2013',1, 'Jan 30 2013')
insert into Specific values (116,'Feb 1 2013',2, 'Jan 1 1970')


;with mycte as
(Select a_id,a_date,c_date from General WHERE c_date is null or c_date='1970-01-01')
Merge mycte as a
Using (Select a_id,a_date,Max(d_date) as d_date from specific 
WHERE d_date is not null or d_date<>'1970-01-01' Group by a_id,a_date) as b 
On a.a_id=b.a_id AND a.a_date=b.a_date 
WHen Matched Then
UPDATE
Set c_date = b.d_date;



select * from [General]


drop table [General],Specific

Delete with CTE

Posted: February 12, 2014 | Author: Jingyang Li | Filed under: SQL Server 2005, SQL Server 2008, SQL Server 2012 | Leave a comment


create table Products(id int, pname varchar(10))
create table Price(id int, price int)
create table stock(ids int, sname varchar(10))

Insert into Products values (1,'a1'),(2,'a2'),(3,'a3'),(4,'a')

Insert into price values (1,1),(2,2),(3,2),(4,2),(5,3)
Insert into stock values (1,'a1'),(2,'a2'),(3,'a3'),(14,'a'),(15,'b15')

declare @intprice int=2

;with mycte as
(
Select * from stock WHERE IDs IN (
SELECT pt.ID from Products pt
 INNER JOIN Price pr
 ON pt.ID = pr.ID
 Where pr.price= @intprice )
)

 Delete from mycte
 Select * from stock

drop table Products
 drop table Price
 drop table Stock

http://social.msdn.microsoft.com/Forums/en-US/920a2b62-1435-4caf-9187-0d1d90eae41e/delete-multiple-records?forum=transactsql

Jingyang Li

SQL Server