Sliding AVG From Both Directions–T-SQL

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

Advertisements

A few Queries that are related to Agent Information

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)

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

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

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)

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)


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;