June 13, 2018 at 12:45 pm
Hi, Below the test daaDeclare @Score table(IdScore int, score int, Name varchar(100))
insert into @Score(IdScore,score, name)
select 1,0, 'poor' union all
select 2,1500 , 'Moderate' union all
select 3,3500, 'Good' union all
select 4,6000 , 'Very Good'
--score range: 0 - 1500 -- poor
--score range: 1500 - 3500 -- Moderate
--score range: 3500 - 6000 -- Good
--score range: > 6000 -- Very good
Declare @User table(IDUser int, score int,RecordDt date)
insert into @User(IDUser, score, RecordDt)
select 340,3600,'05/31/2018' union all
select 340,3501,'05/31/2018' union all
select 340,2375,'05/21/2018' union all
select 340,2375,'04/25/2018' union all
select 340,2375,'04/23/2018' union all
select 340,2350,'04/23/2018' union all
select 340,2325,'04/23/2018' union all
select 340,2275,'04/23/2018' union all
select 340,2225,'04/23/2018' union all
select 340,2200,'04/23/2018' union all
select 340,2175,'04/19/2018' union all
select 340,2150,'04/19/2018' union all
select 340,2125,'04/19/2018' union all
select 340,2025,'04/19/2018' union all
select 340,1925,'04/19/2018' union all
select 340,1900,'04/19/2018' union all
select 340,1875,'04/19/2018' union all
select 340,1850,'04/19/2018' union all
select 340,1825,'04/19/2018' union all
select 340,1800,'04/19/2018' union all
select 340,1775,'04/19/2018' union all
select 340,1725,'04/19/2018' union all
select 340,1675,'04/19/2018' union all
select 340,1650,'04/19/2018' union all
select 340,1625,'04/19/2018' union all
select 340,1575,'04/19/2018' union all
select 340,1525,'04/19/2018' union all
select 340,1475,'04/19/2018' union all
select 340,1425,'04/18/2018' union all
select 340,1400,'04/18/2018' union all
select 340,1375,'04/18/2018' union all
select 340,1350,'04/18/2018' union all
select 340,1325,'04/18/2018' union all
select 340,1300,'04/18/2018' union all
select 340,1275,'04/17/2018' union all
select 340,1250,'04/17/2018' union all
select 340,1225,'04/17/2018' union all
select 340,1200,'04/17/2018' union all
select 340,1175,'04/17/2018' union all
select 340,1150,'04/17/2018' union all
select 340,1125,'04/17/2018' union all
select 340,1100,'04/17/2018' union all
select 340,1075,'04/17/2018' union all
select 340,1050,'04/17/2018' union all
select 340,1025,'04/17/2018' union all
select 340,1000,'04/17/2018' union all
select 340,975,'04/17/2018' union all
select 340,950,'04/17/2018' union all
select 340,925,'04/17/2018' union all
select 340,900,'04/17/2018' union all
select 340,875,'04/17/2018' union all
select 340,850,'04/17/2018' union all
select 340,825,'04/17/2018' union all
select 340,800,'04/17/2018' union all
select 340,775,'04/17/2018' union all
select 340,750,'04/17/2018' union all
select 340,725,'04/17/2018' union all
select 340,675,'04/17/2018' union all
select 340,625,'04/17/2018' union all
select 340,600,'04/17/2018' union all
select 340,575,'04/17/2018' union all
select 340,550,'04/17/2018' union all
select 340,525,'04/17/2018' union all
select 340,500,'04/17/2018' union all
select 340,475,'04/17/2018' union all
select 340,450,'04/17/2018' union all
select 340,425,'04/17/2018' union all
select 340,225,'04/17/2018' union all
select 340,200,'04/17/2018' union all
select 340,175,'04/17/2018' union all
select 340,150,'04/17/2018' union all
select 340,100,'04/17/2018' union all
select 340,75,'04/17/2018' union all
select 340,50,'04/17/2018' union all
select 340,0,'04/17/2018' union all
select 340, NULL, null union all
select 340,null, null
expected result :select 340 as IdUser,3600 as Score, 'Good' as Name union all
select 340 as IdUser,2375 as Score, 'moderate' as Name
the requirement here is, i need to get the top 2 latest record based on the rage difference. on the above example, top 2 record falls in same range (3500 - 6000)which means "Good"
so take just most recent one. then next rage(1500-3500) record is 2375 which falls in "Moderate".
Any sample query how to achieve this please
June 14, 2018 at 11:57 am
Here you go:CREATE TABLE #ScoreRange (
IdScore int,
ScoreLow int,
ScoreHigh int,
RangeName varchar(100)
);
INSERT INTO #ScoreRange (IdScore, ScoreLow, ScoreHigh, RangeName)
VALUES (1, 0, 1499, 'Poor'),
(2, 1500, 3499, 'Moderate'),
(3, 3500, 5999, 'Good'),
(4, 6000, 999999999, 'Very Good');
CREATE TABLE #Users (
IDUser int,
score int,
RecordDt date
);
INSERT INTO #Users (IDUser, score, RecordDt)
SELECT 340,3600,'05/31/2018' UNION ALL
SELECT 340,3501,'05/31/2018' UNION ALL
SELECT 340,2375,'05/21/2018' UNION ALL
SELECT 340,2375,'04/25/2018' UNION ALL
SELECT 340,2375,'04/23/2018' UNION ALL
SELECT 340,2350,'04/23/2018' UNION ALL
SELECT 340,2325,'04/23/2018' UNION ALL
SELECT 340,2275,'04/23/2018' UNION ALL
SELECT 340,2225,'04/23/2018' UNION ALL
SELECT 340,2200,'04/23/2018' UNION ALL
SELECT 340,2175,'04/19/2018' UNION ALL
SELECT 340,2150,'04/19/2018' UNION ALL
SELECT 340,2125,'04/19/2018' UNION ALL
SELECT 340,2025,'04/19/2018' UNION ALL
SELECT 340,1925,'04/19/2018' UNION ALL
SELECT 340,1900,'04/19/2018' UNION ALL
SELECT 340,1875,'04/19/2018' UNION ALL
SELECT 340,1850,'04/19/2018' UNION ALL
SELECT 340,1825,'04/19/2018' UNION ALL
SELECT 340,1800,'04/19/2018' UNION ALL
SELECT 340,1775,'04/19/2018' UNION ALL
SELECT 340,1725,'04/19/2018' UNION ALL
SELECT 340,1675,'04/19/2018' UNION ALL
SELECT 340,1650,'04/19/2018' UNION ALL
SELECT 340,1625,'04/19/2018' UNION ALL
SELECT 340,1575,'04/19/2018' UNION ALL
SELECT 340,1525,'04/19/2018' UNION ALL
SELECT 340,1475,'04/19/2018' UNION ALL
SELECT 340,1425,'04/18/2018' UNION ALL
SELECT 340,1400,'04/18/2018' UNION ALL
SELECT 340,1375,'04/18/2018' UNION ALL
SELECT 340,1350,'04/18/2018' UNION ALL
SELECT 340,1325,'04/18/2018' UNION ALL
SELECT 340,1300,'04/18/2018' UNION ALL
SELECT 340,1275,'04/17/2018' UNION ALL
SELECT 340,1250,'04/17/2018' UNION ALL
SELECT 340,1225,'04/17/2018' UNION ALL
SELECT 340,1200,'04/17/2018' UNION ALL
SELECT 340,1175,'04/17/2018' UNION ALL
SELECT 340,1150,'04/17/2018' UNION ALL
SELECT 340,1125,'04/17/2018' UNION ALL
SELECT 340,1100,'04/17/2018' UNION ALL
SELECT 340,1075,'04/17/2018' UNION ALL
SELECT 340,1050,'04/17/2018' UNION ALL
SELECT 340,1025,'04/17/2018' UNION ALL
SELECT 340,1000,'04/17/2018' UNION ALL
SELECT 340,975,'04/17/2018' UNION ALL
SELECT 340,950,'04/17/2018' UNION ALL
SELECT 340,925,'04/17/2018' UNION ALL
SELECT 340,900,'04/17/2018' UNION ALL
SELECT 340,875,'04/17/2018' UNION ALL
SELECT 340,850,'04/17/2018' UNION ALL
SELECT 340,825,'04/17/2018' UNION ALL
SELECT 340,800,'04/17/2018' UNION ALL
SELECT 340,775,'04/17/2018' UNION ALL
SELECT 340,750,'04/17/2018' UNION ALL
SELECT 340,725,'04/17/2018' UNION ALL
SELECT 340,675,'04/17/2018' UNION ALL
SELECT 340,625,'04/17/2018' UNION ALL
SELECT 340,600,'04/17/2018' UNION ALL
SELECT 340,575,'04/17/2018' UNION ALL
SELECT 340,550,'04/17/2018' UNION ALL
SELECT 340,525,'04/17/2018' UNION ALL
SELECT 340,500,'04/17/2018' UNION ALL
SELECT 340,475,'04/17/2018' UNION ALL
SELECT 340,450,'04/17/2018' UNION ALL
SELECT 340,425,'04/17/2018' UNION ALL
SELECT 340,225,'04/17/2018' UNION ALL
SELECT 340,200,'04/17/2018' UNION ALL
SELECT 340,175,'04/17/2018' UNION ALL
SELECT 340,150,'04/17/2018' UNION ALL
SELECT 340,100,'04/17/2018' UNION ALL
SELECT 340,75,'04/17/2018' UNION ALL
SELECT 340,50,'04/17/2018' UNION ALL
SELECT 340,0,'04/17/2018' UNION ALL
SELECT 340, NULL, NULL UNION ALL
SELECT 340, NULL, NULL;
WITH ScoreData AS (
SELECT
U.IDUser,
U.score,
SR.RangeName,
ROW_NUMBER() OVER(PARTITION BY U.IDUser ORDER BY U.RecordDt DESC) AS RowNum
FROM #Users AS U
INNER JOIN #ScoreRange AS SR
ON U.score BETWEEN SR.ScoreLow AND SR.ScoreHigh
WHERE NOT EXISTS (
SELECT 1
FROM #Users AS U2
INNER JOIN #ScoreRange AS SR2
ON U2.score BETWEEN SR2.ScoreLow AND SR2.ScoreHigh
WHERE U2.IDUser = U.IDUser
AND U2.RecordDt = U.RecordDt
AND SR2.RangeName = SR.RangeName
AND U2.score > U.score
)
)
SELECT SD.*
FROM ScoreData AS SD
WHERE SD.RowNum IN (1, 2)
DROP TABLE #ScoreRange;
DROP TABLE #Users;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 14, 2018 at 12:17 pm
Oops... missed a column, and while I'm at it, improved the performance characteristics by analyzing the execution plan:CREATE TABLE #ScoreRange (
IdScore int,
ScoreLow int,
ScoreHigh int,
RangeName varchar(100),
UNIQUE CLUSTERED (ScoreLow ASC, ScoreHigh ASC)
);
INSERT INTO #ScoreRange (IdScore, ScoreLow, ScoreHigh, RangeName)
VALUES (1, 0, 1499, 'Poor'),
(2, 1500, 3499, 'Moderate'),
(3, 3500, 5999, 'Good'),
(4, 6000, 999999999, 'Very Good');
CREATE TABLE #Users (
RowID int IDENTITY(1,1) PRIMARY KEY NONCLUSTERED,
IDUser int,
score int,
RecordDt date,
UNIQUE CLUSTERED (RecordDt DESC, Score DESC, IDUser ASC, RowID ASC)
);
CREATE NONCLUSTERED INDEX NDX_TEMP_Users_score_INCLUDE_RecordDt_IDUser_RowID_ ON #Users
(
score ASC
)
INCLUDE (RecordDt, IDUser, RowID);
GO
INSERT INTO #Users (IDUser, score, RecordDt)
SELECT 340,3600,'05/31/2018' UNION ALL
SELECT 340,3501,'05/31/2018' UNION ALL
SELECT 340,2375,'05/21/2018' UNION ALL
SELECT 340,2375,'04/25/2018' UNION ALL
SELECT 340,2375,'04/23/2018' UNION ALL
SELECT 340,2350,'04/23/2018' UNION ALL
SELECT 340,2325,'04/23/2018' UNION ALL
SELECT 340,2275,'04/23/2018' UNION ALL
SELECT 340,2225,'04/23/2018' UNION ALL
SELECT 340,2200,'04/23/2018' UNION ALL
SELECT 340,2175,'04/19/2018' UNION ALL
SELECT 340,2150,'04/19/2018' UNION ALL
SELECT 340,2125,'04/19/2018' UNION ALL
SELECT 340,2025,'04/19/2018' UNION ALL
SELECT 340,1925,'04/19/2018' UNION ALL
SELECT 340,1900,'04/19/2018' UNION ALL
SELECT 340,1875,'04/19/2018' UNION ALL
SELECT 340,1850,'04/19/2018' UNION ALL
SELECT 340,1825,'04/19/2018' UNION ALL
SELECT 340,1800,'04/19/2018' UNION ALL
SELECT 340,1775,'04/19/2018' UNION ALL
SELECT 340,1725,'04/19/2018' UNION ALL
SELECT 340,1675,'04/19/2018' UNION ALL
SELECT 340,1650,'04/19/2018' UNION ALL
SELECT 340,1625,'04/19/2018' UNION ALL
SELECT 340,1575,'04/19/2018' UNION ALL
SELECT 340,1525,'04/19/2018' UNION ALL
SELECT 340,1475,'04/19/2018' UNION ALL
SELECT 340,1425,'04/18/2018' UNION ALL
SELECT 340,1400,'04/18/2018' UNION ALL
SELECT 340,1375,'04/18/2018' UNION ALL
SELECT 340,1350,'04/18/2018' UNION ALL
SELECT 340,1325,'04/18/2018' UNION ALL
SELECT 340,1300,'04/18/2018' UNION ALL
SELECT 340,1275,'04/17/2018' UNION ALL
SELECT 340,1250,'04/17/2018' UNION ALL
SELECT 340,1225,'04/17/2018' UNION ALL
SELECT 340,1200,'04/17/2018' UNION ALL
SELECT 340,1175,'04/17/2018' UNION ALL
SELECT 340,1150,'04/17/2018' UNION ALL
SELECT 340,1125,'04/17/2018' UNION ALL
SELECT 340,1100,'04/17/2018' UNION ALL
SELECT 340,1075,'04/17/2018' UNION ALL
SELECT 340,1050,'04/17/2018' UNION ALL
SELECT 340,1025,'04/17/2018' UNION ALL
SELECT 340,1000,'04/17/2018' UNION ALL
SELECT 340,975,'04/17/2018' UNION ALL
SELECT 340,950,'04/17/2018' UNION ALL
SELECT 340,925,'04/17/2018' UNION ALL
SELECT 340,900,'04/17/2018' UNION ALL
SELECT 340,875,'04/17/2018' UNION ALL
SELECT 340,850,'04/17/2018' UNION ALL
SELECT 340,825,'04/17/2018' UNION ALL
SELECT 340,800,'04/17/2018' UNION ALL
SELECT 340,775,'04/17/2018' UNION ALL
SELECT 340,750,'04/17/2018' UNION ALL
SELECT 340,725,'04/17/2018' UNION ALL
SELECT 340,675,'04/17/2018' UNION ALL
SELECT 340,625,'04/17/2018' UNION ALL
SELECT 340,600,'04/17/2018' UNION ALL
SELECT 340,575,'04/17/2018' UNION ALL
SELECT 340,550,'04/17/2018' UNION ALL
SELECT 340,525,'04/17/2018' UNION ALL
SELECT 340,500,'04/17/2018' UNION ALL
SELECT 340,475,'04/17/2018' UNION ALL
SELECT 340,450,'04/17/2018' UNION ALL
SELECT 340,425,'04/17/2018' UNION ALL
SELECT 340,225,'04/17/2018' UNION ALL
SELECT 340,200,'04/17/2018' UNION ALL
SELECT 340,175,'04/17/2018' UNION ALL
SELECT 340,150,'04/17/2018' UNION ALL
SELECT 340,100,'04/17/2018' UNION ALL
SELECT 340,75,'04/17/2018' UNION ALL
SELECT 340,50,'04/17/2018' UNION ALL
SELECT 340,0,'04/17/2018' UNION ALL
SELECT 340, NULL, NULL UNION ALL
SELECT 340, NULL, NULL;
WITH ScoreData AS (
SELECT
U.IDUser,
U.score,
U.RecordDt,
SR.RangeName,
ROW_NUMBER() OVER(PARTITION BY U.IDUser ORDER BY U.RecordDt DESC) AS RowNum
FROM #Users AS U
INNER JOIN #ScoreRange AS SR
ON U.score BETWEEN SR.ScoreLow AND SR.ScoreHigh
WHERE NOT EXISTS (
SELECT 1
FROM #Users AS U2
INNER JOIN #ScoreRange AS SR2
ON U2.score BETWEEN SR2.ScoreLow AND SR2.ScoreHigh
WHERE U2.IDUser = U.IDUser
AND U2.RecordDt = U.RecordDt
AND SR2.RangeName = SR.RangeName
AND U2.score > U.score
)
)
SELECT SD.*
FROM ScoreData AS SD
WHERE SD.RowNum IN (1, 2)
DROP TABLE #ScoreRange;
DROP TABLE #Users;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 14, 2018 at 12:20 pm
SELECT TOP (2) IDUser, Score, Name
FROM (
SELECT U.IDUser, U.Score, U.RecordDt, S2.Name,
ROW_NUMBER() OVER(PARTITION BY U.IDUser, S2.Name ORDER BY U.RecordDt DESC) AS row_num
FROM @user U
CROSS APPLY (
SELECT TOP (1) *
FROM @Score S
WHERE U.score >= S.score
ORDER BY S.score DESC
) AS S2
) AS query1
WHERE row_num = 1
ORDER BY RecordDt DESC
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
June 20, 2018 at 5:21 am
select top 2 *
from (
select max(u.iduser) iduser, max(u.score) score, s.rangename name
from #Users u
left join #scorerange s on (u.score between s.scorelow and s.scorehigh)
group by s.rangename
) x
order by 2 desc
O/p -iduser score name
340 3600 Good
340 2375 Moderate
June 20, 2018 at 9:31 am
ranitb - Wednesday, June 20, 2018 5:21 AMDoes this help ?
select top 2 *
from (
select max(u.iduser) iduser, max(u.score) score, s.rangename name
from #Users u
left join #scorerange s on (u.score between s.scorelow and s.scorehigh)
group by s.rangename
) x
order by 2 descO/p -
iduser score name
340 3600 Good
340 2375 Moderate
Be careful with that approach: the MAX(user) may not be from the same row as the MAX(score).
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply