Average values group by date for top 20 % rows

Transact-SQL
1

i have SQL server table as below

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TB1](
[Ddate] [date] NULL,
[Amount] [int] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

and the table is sorted by Ddate,amount

and the data as below
SET IDENTITY_INSERT [dbo].[TB1] ON

GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 1, 1)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 2, 2)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 3, 3)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 4, 4)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 5, 5)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 6, 6)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 7, 7)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 8, 8)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 9, 9)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 10, 10)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 0, 11)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 0, 12)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 1, 13)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 1, 14)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 15)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 16)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 17)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 18)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 19)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 20)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 21)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 22)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 23)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 24)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 25)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 26)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 27)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 28)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 29)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 30)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 31)
GO
SET IDENTITY_INSERT [dbo].[TB1] OFF
GO
and i need to get result as below

type || Ddate || Avg || Explain the result

Top 20% || 1-1-2020 || 9.5 || 10+9= 19 / 2 =9.5
Down 20% || 1-1-2020 || 5.5 || 3+4+5+6+7+8= 33 / 6 =5.5
middle 60 % || 1-1-2020 || 1.5 || 1+2=3 /2 =1.5

Top 20% || 2-1-2020 || 4 || 4+4+4+4=16 /4 = 4
Down 20% || 2-1-2020 || 2 || 2+2+2+2+2+2+2+2+2+2+2+2=24 / 12 = 2
middle 60 % || 2-1-2020 || 1.5 || 1+1+2+2=6 / 4 =1.5

2

hi

i tried to do this !!!

its a lot of CTE's ... but something ...

Hope it helps

..i am sure there is a mathematical way of doing thats ... very simple looking in Terms of CODE
and also very short !!

please click arrow to the left for Drop Create DATA ... 
drop table [dbo].[TB1]
go 

CREATE TABLE [dbo].[TB1](
[Ddate] [date] NULL,
[Amount] [int] NULL,
[ID] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO



SET IDENTITY_INSERT [dbo].[TB1] ON

GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 1, 1)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 2, 2)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 3, 3)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 4, 4)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 5, 5)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 6, 6)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 7, 7)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 8, 8)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 9, 9)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-01' AS Date), 10, 10)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 0, 11)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 0, 12)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 1, 13)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 1, 14)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 15)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 16)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 17)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 18)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 19)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 20)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 21)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 22)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 23)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 24)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 25)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 2, 26)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 27)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 28)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 29)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 30)
GO
INSERT [dbo].[TB1] ([Ddate], [Amount], [ID]) VALUES (CAST(N'2020-01-02' AS Date), 4, 31)
GO
please click arrow to the left for SQL .. 
; with cte_top20per as 
(
select ROW_NUMBER() over(partition by Ddate order by Ddate,amount desc ) as rntop , * from   [dbo].[TB1] 
) , cte_bot20per as 
(
select ROW_NUMBER() over(partition by Ddate order by Ddate,amount asc ) as rnbot , * from   [dbo].[TB1] 
) , cte_top20percent as 
(
select a.* from cte_top20per a  join (select Ddate, max(rntop) as maxrn from cte_top20per group by Ddate ) b  on a.Ddate = b.Ddate and  a.rntop between 1 and b.maxrn*.2

) , cte_bot20percent as 
(
select a.* from cte_bot20per a  join (select Ddate, max(rnbot) as maxrn from cte_bot20per group by Ddate ) b  on a.Ddate = b.Ddate and  a.rnbot between 1 and b.maxrn*.2
), cte_fin_top20 as 
(
select 'Top 20 percent' as top20 ,Ddate , Avg(amount*1.00) as avgtop20 from cte_top20percent group by Ddate 
) , cte_fin_bot20 as 
(
select 'Bot 20 percent' as bot20 ,Ddate , Avg(amount*1.00) as avgbot20 from cte_bot20percent group by Ddate 
) 
select * from cte_fin_top20
union all 
select * from cte_fin_bot20
order by 2 , 1 desc 
go

image

3

Here you go, this produces top,middle and bottom:-

	declare @counter int 
	declare @start int 
	declare @startdate date 

	set @counter = datediff(day,(select min(Ddate) from [TB1]),(select max(Ddate) from [TB1]))+1
	set @start =  1
	set @startdate =  (select min(Ddate) from [TB1])


	while  @start <= @counter begin


	;with topx as (
					select top 20 percent 
					'Top 20%' as Flag,
					*
					from [TB1]
					where Ddate = @startdate
					order by amount desc
					),Bottomx as (
									select top 20 percent 
									'Down 20%' as Flag,
									*
									from [TB1]
									where Ddate = @startdate
									order by amount 
										),middle as (
														select 'middle 60%' as Flag,
														* 
														from [TB1] b
														where Ddate = @startdate
														and not exists (
																			select * from topx a
																			where a.Ddate = b.Ddate
																			and a.id = b.id
																		)
														and not exists (
																			select * from Bottomx a
																			where a.Ddate = b.Ddate
																			and a.id = b.id
																		)
	),final as ( 
					select 
					 Flag
					,Ddate
					,convert(float,sum(amount))/convert(float,count(amount)) as [Avg]
					 from topx
					 group by 
					 Ddate
					 ,Flag
			union all 
					select 
					 Flag
					,Ddate
					,convert(float,sum(amount))/convert(float,count(amount)) as [Avg]
					 from middle
					 group by 
					 Ddate
					 ,Flag
			union all 
					select 
					 Flag
					,Ddate
					,convert(float,sum(amount))/convert(float,count(amount)) as [Avg]
					 from Bottomx
					 group by 
					 Ddate
					 ,Flag
	)
	select * 
	from final

	
	set @startdate = dateadd(day,1,@startdate)
set @start = @start+1
End

Result

4

thanks for your help but can we appear it in one table

5

Yes its possible

append all rows into temp table

After
END

select * into TABLE
from temptable

6

Okay - here is one way to get this all in a single output, ordered the way you want it:

Declare @tb1 Table (
        Ddate date
      , Amount int
      , ID int
        );

 Insert @tb1 (Ddate, Amount, ID)
 Values ('2020-01-01', 1, 1)
      , ('2020-01-01', 2, 2)
      , ('2020-01-01', 3, 3)
      , ('2020-01-01', 4, 4)
      , ('2020-01-01', 5, 5)
      , ('2020-01-01', 6, 6)
      , ('2020-01-01', 7, 7)
      , ('2020-01-01', 8, 8)
      , ('2020-01-01', 9, 9)
      , ('2020-01-01', 10, 10)
      , ('2020-01-02', 0, 11)
      , ('2020-01-02', 0, 12)
      , ('2020-01-02', 1, 13)
      , ('2020-01-02', 1, 14)
      , ('2020-01-02', 2, 15)
      , ('2020-01-02', 2, 16)
      , ('2020-01-02', 2, 17)
      , ('2020-01-02', 2, 18)
      , ('2020-01-02', 2, 19)
      , ('2020-01-02', 2, 20)
      , ('2020-01-02', 2, 21)
      , ('2020-01-02', 2, 22)
      , ('2020-01-02', 2, 23)
      , ('2020-01-02', 2, 24)
      , ('2020-01-02', 2, 25)
      , ('2020-01-02', 2, 26)
      , ('2020-01-02', 4, 27)
      , ('2020-01-02', 4, 28)
      , ('2020-01-02', 4, 29)
      , ('2020-01-02', 4, 30)
      , ('2020-01-02', 4, 31);

   With dates
     As (
 Select t.Ddate
      , TotalAmount = sum(t.Amount * 1.0)
   From @tb1 t
  Group By
        t.Ddate
        )
      , top20
     As (
 Select Flag = 'Top 20%'
      , FlagOrder = 1
      , t20.DDate
      , t20.Amount
      , t20.ID
   From dates d

  Cross Apply (Select Top 20 Percent
                      *
                 From @tb1 t2
                Where t2.Ddate = d.Ddate
                  And t2.Amount <> 0
                Order By
                      t2.Amount desc
              ) As t20
        )
      , bottom20
     As (
 Select Flag = 'Bottom 20%'
      , FlagOrder = 3
      , b20.DDate
      , b20.Amount
      , b20.ID
   From dates d

  Cross Apply (Select Top 20 Percent
                      *
                 From @tb1 t2
                Where t2.Ddate = d.Ddate
                  And t2.Amount <> 0
                Order By
                      t2.Amount
              ) As b20
        )
      , middle60
     As (
 Select Flag = 'Middle 60%'
      , FlagOrder = 2
      , m60.DDate
      , m60.Amount
      , m60.ID
   From dates d

  Cross Apply (Select *
                 From @tb1 t2
                Where t2.Amount <> 0
                  And t2.Ddate = d.Ddate
                  And Not Exists (Select * From top20 t Where t.ID = t2.ID)
                  And Not Exists (Select * From bottom20 b Where b.ID = t2.ID)
              ) As m60
        )
 Select Flag
      , FlagOrder
      , DDate
      , AvgAmount = avg(Amount * 1.0)
   From top20
  Group By
        Flag
      , FlagOrder
      , DDate
  Union All
 Select Flag
      , FlagOrder
      , DDate
      , AvgAmount = avg(Amount * 1.0)
   From bottom20
  Group By
        Flag
      , FlagOrder
      , DDate
  Union All
 Select Flag
      , FlagOrder
      , DDate
      , AvgAmount = avg(Amount * 1.0)
   From middle60
  Group By
        Flag
      , FlagOrder
      , DDate
  Order By
        DDate
      , FlagOrder;

I changed the sample to a table variable for the test data so I could test the solution. One item to note is that your middle 60% value for 2020-01-02 is not correct in your example. In your example you have 5 values of 4 and only 4 of those are included in the top 20.

You could try to avoid that by using 'WITH TIES' - but that will move rows from the middle into the top/bottom percentage and change the expected results.

You also seem to be excluding the amounts = 0 from consideration...including those will also change the expected results since they should be included in the bottom 20 percent of rows and that would affect the average amount.

Your expected results also has Down/Middle reversed for 2020-01-01.

1 Like
7

here is all data in one table:-

	if object_id('tempdb..#temp') is not null drop table #temp

	create table #temp (
						 [Type] varchar(50)
						,[Ddate] date
						,[Avg] float
	)



	declare @counter int 
	declare @start int 
	declare @startdate date 

	set @counter = datediff(day,(select min(Ddate) from [TB1]),(select max(Ddate) from [TB1]))+1
	set @start =  1
	set @startdate =  (select min(Ddate) from [TB1])


	while  @start <= @counter 
	begin



	;with topx as (
					select top 20 percent 
					'Top 20%' as Flag,
					*
					from [TB1]
					where Ddate = @startdate
					order by amount desc
					),Bottomx as (
									select top 20 percent 
									'Down 20%' as Flag,
									*
									from [TB1]
									where Ddate = @startdate
									order by amount 
										),middle as (
														select 'middle 60%' as Flag,
														* 
														from [TB1] b
														where Ddate = @startdate
														and not exists (
																			select * from topx a
																			where a.Ddate = b.Ddate
																			and a.id = b.id
																		)
														and not exists (
																			select * from Bottomx a
																			where a.Ddate = b.Ddate
																			and a.id = b.id
																		)
	),final as ( 
					select 
					 Flag
					,Ddate
					,convert(float,sum(amount))/convert(float,count(amount)) as [Avg]
					 from topx
					 group by 
					 Ddate
					 ,Flag
			union all 
					select 
					 Flag
					,Ddate
					,convert(float,sum(amount))/convert(float,count(amount)) as [Avg]
					 from middle
					 group by 
					 Ddate
					 ,Flag
			union all 
					select 
					 Flag
					,Ddate
					,convert(float,sum(amount))/convert(float,count(amount)) as [Avg]
					 from Bottomx
					 group by 
					 Ddate
					 ,Flag
	)

		insert into #temp 

	select * 
	from final



set @startdate = dateadd(day,1,@startdate)
set @start = @start+1



End



select * from #temp
1 Like