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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s