find the number of values less than the current row

Question

Post reply

find the number of values less than the current row

water490

SSC Eights!

Points: 954
More actions
March 8, 2024 at 8:37 pm
Go to Answer
#4392083
Hi everyone
I am working on a query where I need to find the number of values that are less than the current row's value going back X days. For this example, let use X =5 but it can be any number.
Sample data:
Explanation:
Start at Day 11. Day 11 value is 42. I need to see how many values for 5 previous days (including Day 11) are less than the current Day 11 value so we would look at values from Day 7 to 11. There are 2 values less than Day 11 value (19 and 0) so the answer is 2.
For Day 10. The Day 10 value is 19. We look back at the 5 previous days so consider Days 6 to 10. There are 2 value (0 and 11) so return 2.
For Day 9. The Day 9 value is 0. We look back at the 5 previous days so consider Days 5 to 9. There are no values so return 0.
For Day 4. The Day 4 value is 98. There are less than 5 days available so return NULL.
How can I implement this type of query?
Thank you
- This topic was modified 8 months, 2 weeks ago by water490.

SoCal_DBD

Hall of Fame

Points: 3382

March 8, 2024 at 11:51 pm

Answer

#4392153

It's Friday afternoon and this was a nice small example. Here's my offering:

-- Create sample data
DROP TABLE IF EXISTS #Example
CREATE TABLE #Example
    (DayNum INT,
     Units INT)

INSERT INTO #Example
VALUES (1, 34),
       (2, 33),
       (3, 45),
       (4, 98),
       (5, 11),
       (6, 11),
       (7, 55),
       (8, 88),
       (9, 0),
       (10, 19),
       (11, 42)

-- Review using LAG
SELECT *,
       DayNumStart = LAG(DayNum, 4, NULL) OVER (ORDER BY DayNum)
FROM #Example

-- Put it together with source data
; WITH DayRange AS
(SELECT *,
        DayNumStart = LAG(DayNum, 4, NULL) OVER (ORDER BY DayNum)
 FROM #Example
)

SELECT FromDayNum = r.DayNumStart,
       ThroughDayNum = r.DayNum, 
       BenchmarkUnits = r.Units,
       CountLessThanBenchmark = SUM(CASE WHEN e.Units < r.Units THEN 1 ELSE 0 END)
FROM DayRange r
CROSS JOIN #Example e
WHERE e.DayNum >= r.DayNumStart
AND e.DayNum <= r.DayNum
GROUP BY r.DayNum, r.DayNumStart, r.Units

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101098 More actions · Answer 1

Need sample data that is directly usable, that is, CREATE TABLE and INSERT statement(s), not just a splat on the screen.

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

water490 SSC Eights! Points: 954 More actions · Answer 2

ScottPletcher wrote:

Need sample data that is directly usable, that is, CREATE TABLE and INSERT statement(s), not just a splat on the screen.

no problem. here you go:

CREATE TABLE [dbo].[TestTable1](
[Day] [tinyint] NOT NULL,
[Units] [tinyint] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (1, 34)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (2, 33)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (3, 45)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (4, 98)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (5, 11)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (6, 11)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (7, 55)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (8, 88)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (9, 0)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (10, 19)
GO
INSERT [dbo].[TestTable1] ([Day], [Units]) VALUES (11, 42)
GO

water490 SSC Eights! Points: 954 More actions · Answer 3

SoCal_DBD wrote:

It's Friday afternoon and this was a nice small example. Here's my offering:

-- Create sample data
DROP TABLE IF EXISTS #Example
CREATE TABLE #Example
    (DayNum INT,
     Units INT)

INSERT INTO #Example
VALUES (1, 34),
       (2, 33),
       (3, 45),
       (4, 98),
       (5, 11),
       (6, 11),
       (7, 55),
       (8, 88),
       (9, 0),
       (10, 19),
       (11, 42)

-- Review using LAG
SELECT *,
       DayNumStart = LAG(DayNum, 4, NULL) OVER (ORDER BY DayNum)
FROM #Example

-- Put it together with source data
; WITH DayRange AS
(SELECT *,
        DayNumStart = LAG(DayNum, 4, NULL) OVER (ORDER BY DayNum)
 FROM #Example
)

SELECT FromDayNum = r.DayNumStart,
       ThroughDayNum = r.DayNum, 
       BenchmarkUnits = r.Units,
       CountLessThanBenchmark = SUM(CASE WHEN e.Units < r.Units THEN 1 ELSE 0 END)
FROM DayRange r
CROSS JOIN #Example e
WHERE e.DayNum >= r.DayNumStart
AND e.DayNum <= r.DayNum
GROUP BY r.DayNum, r.DayNumStart, r.Units

Thank you for this! The values are correct except for the last one. It should be 2 not 4:

Screenshot 2024-03-08 160707

How should the code be modified to account for this?

Thank you

SoCal_DBD Hall of Fame Points: 3382 More actions · Answer 4

This is the output I get. (In fact I did a copy/paste from my post).

SSCExample

water490 SSC Eights! Points: 954 More actions · Answer 5

SoCal_DBD wrote:

This is the output I get. (In fact I did a copy/paste from my post).

my bad. i made a typo when i copied over the formula. the output matches! thank you so much!!!!!

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 6

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count
               from [dbo].[TestTable1] b
              where b.Day between a.Day -@DaysToLookBack + 1 and a.Day
                and b.Units < a.Units) b

Screenshot 2024-03-09 012406

water490 SSC Eights! Points: 954 More actions · Answer 7

Jonathan AC Roberts wrote:

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count
               from [dbo].[TestTable1] b
              where b.Day between a.Day -@DaysToLookBack + 1 and a.Day
                and b.Units < a.Units) b

Screenshot 2024-03-09 012406

Thank you for this.

I modified the code to work with the actual table. The numbers I am seeing are not correct. Here are the changes I made:

where b.TRADE_DATE between dateadd(day,-@DaysToLookBack+1, a.TRADE_DATE) and a.TRADE_DATE
     and b.val1 < a.val1 and a.SYMBOL=b.SYMBOL) b

The example I gave used INT for days but the actual data is a regular date. I made the above changes thinking that is all I need to do but the output I am getting isn't correct when I use the actual data. The data is partitioned by symbol so I added this to your code. What went wrong?

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 8

Has TRADE_DATE got a time part on it? If so is it not always midnight?

Can you supply some test data?

water490 SSC Eights! Points: 954 More actions · Answer 9

Jonathan AC Roberts wrote:

Has TRADE_DATE got a time part on it? If so is it not always midnight?
Can you supply some test data?

np. here you go:

CREATE TABLE [dbo].[TestTable1](
[company] [nvarchar](50) NOT NULL,
[Day] [date] NOT NULL,
[Units] [tinyint] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-16' AS Date), 34)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-20' AS Date), 33)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-21' AS Date), 45)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-22' AS Date), 98)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-23' AS Date), 11)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-26' AS Date), 11)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-27' AS Date), 55)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-28' AS Date), 88)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-29' AS Date), 0)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-03-01' AS Date), 19)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-03-04' AS Date), 42)
GO

there is no time to the date. but there are gaps in the dates. i think the gaps in the dates is an issue.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 10

The gaps in dates shouldn't be an issue.

The data you provided isn't the data you put in your amendment of the query.

The results from the test data you provided look ok to me. What results were you expecting?

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count
               from [dbo].[TestTable1] b
              where b.Day between dateadd(day,-@DaysToLookBack + 1, a.Day) and a.Day
                and b.Units < a.Units) b
 order by a.Day
;

Screenshot 2024-03-09 063902

water490 SSC Eights! Points: 954 More actions · Answer 11

Jonathan AC Roberts wrote:

The gaps in dates shouldn't be an issue.
The data you provided isn't the data you put in your amendment of the query.
The results from the test data you provided look ok to me. What results were you expecting?
declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count
               from [dbo].[TestTable1] b
              where b.Day between dateadd(day,-@DaysToLookBack + 1, a.Day) and a.Day
                and b.Units < a.Units) b
 order by a.Day
;

the data is different but the issue is still there. take a look at Feb 28. The value on this day is 88. The last five days are Feb 22 to Feb 28. The values are 98, 11, 11, 55,88. There are 3 values less than 88 (11,11,55) so the result on Feb 28 should be 3. the query is showing 2. Your screenshot on post March 8, 2024 at 5:23 pm has different values compared to the screenshot in the latest post.

This reply was modified 8 months, 2 weeks ago by water490.

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 12

There aren't 3 values less than 88 within 5 days of (including the reference date) 2024-02-28

2024-02-28 minus 4 days is 2024-02-24.

So there are only 2 rows less than that (11, 55)

Edit: I think the confusion is over the 5 days. Reading your question again I can see that you want the 5 days pervious with the addition of the current day. So you just need to remove the "+ 1" from the query:

declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count, dateadd(day,-@DaysToLookBack, a.Day) LookingBackTo
               from [dbo].[TestTable1] b
              where b.Day between dateadd(day,-@DaysToLookBack, a.Day) and a.Day
                and b.Units < a.Units) b
 order by a.Day
;

This reply was modified 8 months, 2 weeks ago by Jonathan AC Roberts.

water490 SSC Eights! Points: 954 More actions · Answer 13

Jonathan AC Roberts wrote:

There aren't 3 values less than 88 within 5 days of (including the reference date) 2024-02-28
2024-02-28 minus 4 days is 2024-02-24.
So there are only 2 rows less than that (11, 55)
Edit: I think the confusion is over the 5 days. Reading your question again I can see that you want the 5 days pervious with the addition of the current day. So you just need to remove the "+ 1" from the query:
declare @DaysToLookBack int = 5

select *
  from [dbo].[TestTable1] a
 cross apply (select COUNT(*) Count, dateadd(day,-@DaysToLookBack, a.Day) LookingBackTo
               from [dbo].[TestTable1] b
              where b.Day between dateadd(day,-@DaysToLookBack, a.Day) and a.Day
                and b.Units < a.Units) b
 order by a.Day
;

Thanks for this. I re-read my post. I was not clear so my apologies for that. I know I said "days" so it makes sense to assume that means what it says...regular days. I meant records so look at the last 5 records. This is because there are holidays so there will be gaps in the data. I need to make sure each time the calculation is done that 5 periods are always used. I have provided revised test data with expected outcome. Are you able to modify your query? Again, really sorry for not being clear.

Test Data:

CREATE TABLE [dbo].[TestTable1](
[company] [nvarchar](50) NOT NULL,
[Day] [date] NOT NULL,
[Units] [tinyint] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-16' AS Date), 34)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-20' AS Date), 33)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-21' AS Date), 45)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-22' AS Date), 98)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-23' AS Date), 11)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-26' AS Date), 11)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-27' AS Date), 25)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-28' AS Date), 88)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-02-29' AS Date), 0)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-03-01' AS Date), 19)
GO
INSERT [dbo].[TestTable1] ([company], [Day], [Units]) VALUES (N'ABC', CAST(N'2024-03-04' AS Date), 42)
GO

Expected:

Screenshot 2024-03-09 140544

Explanation:

On 2024-03-04, we look at the last 5 periods including today's date so we are looking at periods from 2024-02-27 to 2024-03-04. The values to consider are 42 (today's), 19,0,88,25. The number of values less than 42 are 19,0,25 so the answer is 3.

Thank you again for your help and understanding.