November 18, 2015 at 3:58 am
Hi,
i have the following Table :
PeriodDetails : PeriodID,CustomerID, CloseBalance.
at the beginning of new month auto incremented Period-ID is used.
for the last 4 periods i need to get top 10 CloseBalance.
i could achieve the second part : top 10 CloseBalance using following :
;With Test as (
SELECT PeriodID, CloseBalance,row_number() over(Partition by PeriodID order by CloseBalance desc) As RowID
FROM PeriodDetails
)
Select PeriodID, CloseBalance,RowID
from Test
Where RowID<=10
order by PeriodID desc
but i dont know how to limit the result to last 4 periods instead of listing all periods..
Any Help!!
Thanks,
November 18, 2015 at 5:47 am
So you need 40 rows in the output
top 10 closing balances for each of the last 4 periods?
November 18, 2015 at 5:53 am
See DENSE_RANK() https://msdn.microsoft.com/en-us/library/ms189798(v=sql.120).aspx
November 18, 2015 at 6:24 am
Exactly anthony.green ..
November 18, 2015 at 6:26 am
OK, this is my take on the problem then
create table #test_period (periodid int, customerid int, closebalance decimal (18,2))
insert into #test_period values
(1,1,10.82),
(1,2,14.67),
(1,3,15.90),
(1,4,12345.90),
(1,5,545.86),
(1,6,8456.05),
(1,7,549851.00),
(1,8,564891.02),
(1,9,7845.09),
(1,10,12.23),
(1,11,74.15),
(1,12,98.45),
(1,13,98.99),
(1,14,931.25),
(2,1,54.44),
(2,2,518.54),
(2,3,8253.25),
(2,4,455.90),
(2,5,5575.86),
(2,6,556.05),
(2,7,851.00),
(2,8,891.02),
(2,9,75.09),
(2,10,1.23),
(2,11,7.15),
(2,12,9.45),
(2,13,9.99),
(2,14,91.25),
(3,1,118.82),
(3,2,154.67),
(3,3,155.90),
(3,4,1345.90),
(3,5,45.86),
(3,6,856.05),
(3,7,59851.00),
(3,8,56891.02),
(3,9,785.09),
(3,10,2.23),
(3,11,7.15),
(3,12,8.45),
(3,13,988.99),
(3,14,31.25),
(4,1,174.82),
(4,2,174.67),
(4,3,175.90),
(4,4,127345.90),
(4,5,5745.86),
(4,6,87456.05),
(4,7,5851.00),
(4,8,891.02),
(4,9,75.09),
(4,10,742.23),
(4,11,7444.15),
(4,12,9858.45),
(4,13,99698.99),
(4,14,95231.25),
(5,1,1052.82),
(5,2,12544.67),
(5,3,15245.90),
(5,4,1254345.90),
(5,5,52445.86),
(5,6,84422456.05),
(5,7,5449851.00),
(5,8,56424891.02),
(5,9,784245.09),
(5,10,4212.23),
(5,11,224274.15),
(5,12,922458.45),
(5,13,24532.99),
(5,14,2545.25)
select * from #test_period
--Assuming you don't have any gaps in periodid
;with cte as
(select row_number() over(partition by periodid order by closebalance desc) as rownum,
periodid, customerid, closebalance
from #test_period
)
select * from cte
where rownum <= 10
and periodid >= (select max(periodid)-3 from #test_period)
--If you do have gaps in periodid
;with cte as
(select
DENSE_RANK() over(order by periodid) AS DR,
row_number() over(partition by periodid order by closebalance desc) as rownum,
periodid, customerid, closebalance
from #test_period
)
select * from cte
where rownum <= 10
and dr >= (select max(dr)-3 from cte) --EDIT serg-52 pointed out logic flaw, changed the "and periodid" to "and dr" to compare dense_rank with dense_rank if there are gaps in period id
Others may chip in with different solutions
November 18, 2015 at 6:55 am
i have got this error :
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
November 18, 2015 at 6:57 am
Yeah I had a typo, the script should work now, just retested it.
November 18, 2015 at 7:01 am
Antony,
and periodid >= (select max(dr)-3 from cte)
may lead to too much periods returned. Consider periods
(3,4,5,6,7). Then max(dr) -3 ==2.
I'd prefer
with cte as
(select
DENSE_RANK() over(order by periodid desc) AS DR,
row_number() over(partition by periodid order by closebalance desc) as rownum,
periodid, customerid, closebalance
from #test_period
)
select * from cte
where rownum <= 10
and dr <= 4
November 18, 2015 at 7:55 am
serg-52 (11/18/2015)
Antony,and periodid >= (select max(dr)-3 from cte)
may lead to too much periods returned. Consider periods
(3,4,5,6,7). Then max(dr) -3 ==2.
MAX(DR) = 7
7 - 3 = 4
>=4 = 4,5,6,7
So would only bring back the recent 4 periods
I'd prefer
with cte as(select
DENSE_RANK() over(order by periodid desc) AS DR,
row_number() over(partition by periodid order by closebalance desc) as rownum,
periodid, customerid, closebalance
from #test_period
)
select * from cte
where rownum <= 10
and dr <= 4
But yes this will also give the correct results
November 19, 2015 at 1:15 am
anthony.green (11/18/2015)
MAX(DR) = 7
Sure?
create table #test_period (periodid int, customerid int, closebalance decimal (18,2))
insert into #test_period values
(6,1,10.82),
(6,2,14.67),
(6,3,15.90),
(6,4,12345.90),
(6,5,545.86),
(6,6,8456.05),
(6,7,549851.00),
(6,8,564891.02),
(6,9,7845.09),
(6,10,12.23),
(6,11,74.15),
(6,12,98.45),
(6,13,98.99),
(6,14,931.25),
(7,1,54.44),
(7,2,518.54),
(7,3,8253.25),
(7,4,455.90),
(7,5,5575.86),
(7,6,556.05),
(7,7,851.00),
(7,8,891.02),
(7,9,75.09),
(7,10,1.23),
(7,11,7.15),
(7,12,9.45),
(7,13,9.99),
(7,14,91.25),
(3,1,118.82),
(3,2,154.67),
(3,3,155.90),
(3,4,1345.90),
(3,5,45.86),
(3,6,856.05),
(3,7,59851.00),
(3,8,56891.02),
(3,9,785.09),
(3,10,2.23),
(3,11,7.15),
(3,12,8.45),
(3,13,988.99),
(3,14,31.25),
(4,1,174.82),
(4,2,174.67),
(4,3,175.90),
(4,4,127345.90),
(4,5,5745.86),
(4,6,87456.05),
(4,7,5851.00),
(4,8,891.02),
(4,9,75.09),
(4,10,742.23),
(4,11,7444.15),
(4,12,9858.45),
(4,13,99698.99),
(4,14,95231.25),
(5,1,1052.82),
(5,2,12544.67),
(5,3,15245.90),
(5,4,1254345.90),
(5,5,52445.86),
(5,6,84422456.05),
(5,7,5449851.00),
(5,8,56424891.02),
(5,9,784245.09),
(5,10,4212.23),
(5,11,224274.15),
(5,12,922458.45),
(5,13,24532.99),
(5,14,2545.25)
;with cte as
(select
DENSE_RANK() over(order by periodid) AS DR,
row_number() over(partition by periodid order by closebalance desc) as rownum,
periodid, customerid, closebalance
from #test_period
)
select 40-count(*) from cte
where rownum <= 10
and periodid >= (select max(dr)-3 from cte)
-10
November 19, 2015 at 1:24 am
Sorry I had PeriodID in mind when writing the script and that it was an auto-increment number with no gaps, you are correct that with gaps the query doesn't work as intended,
just changing the last comparison in the where clause to
and dr >= (select max(dr)-3 from cte)
so that you comparing the dense rank with the dense rank results in the 40 row output.
November 19, 2015 at 2:03 am
anthony..serg-52
Thank you very much..i have reached what i was looking for.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply