I'm only aiming to retrieve rows before negative total values for each nickname and the same date.
Table :
I don't want to retrieve the orderid 8 and the orderid9 because the above rows for the same nickname and the same day contain negative total value. For the same reason, I don't want to retrieve the row with orderid 7. I don't want to retrieve the orderid 5 and the orderid 6 since they contain negative total value. I'm aiming to retrieve the orderid10 although the above rows for the same nickname contain negative value, because the date has changed.
Expected result: enter image description here
I've tried to solve using with clauses and subqueries but I've failed.
CodePudding user response：
welcome to Stack Overflow.
When you're asking a question like this it's really helpful to provide DDL and DML to properly explain your issue. Please don't use images for this, as they can't be easily copy & pasted. A good way to provide the demo data is:
DECLARE @table TABLE (RowID INT IDENTITY, Value INT, Name NVARCHAR(10))
INSERT INTO @table (Value, Name) VALUES
(1, 'E'),(2, 'D'),(3, 'C'),(4, 'B'),(5, 'A'),
(1, 'V'),(2, 'W'),(3, 'X'),(4, 'Y'),(5, 'Z'),
(1, 'M'),(2, 'N'),(3, 'O'),(4, 'P'),(5, 'Q')
On to your question.
It sounds like what you're looking for is the LAG and LEAD windowed functions. Windowed functions operate on a window which you define:
SELECT *, LAG(Name,1) OVER (PARTITION BY Value ORDER BY RowID) AS PreviousName, LEAD(Name,1) OVER (PARTITION BY Value ORDER BY RowID) AS NextName
FROM @table
These functions take the column name you want, and the number or rows to move (LAG moves backwards, LEAD moves forward). In the OVER we use PARTITION and ORDER BY to define the window. PARTITION basically groups things together and ORDER BY determines the order in that group. If the row moved to doesn't exist (there was no previous, or no next) NULL is returned instead.
RowID Value Name PreviousName NextName
------------------------------------------------
1 1 E NULL V
6 1 V E M
11 1 M V NULL
2 2 D NULL W
7 2 W D N
12 2 N W NULL
3 3 C NULL X
8 3 X C O
13 3 O X NULL
4 4 B NULL Y
9 4 Y B P
14 4 P Y NULL
5 5 A NULL Z
10 5 Z A Q
15 5 Q Z NULL
If you can provide some better example data I can probably answer the other points of your question too.
CodePudding user response：
I undestand you don't want to include rows where negative total values exist with the same Nickname and Day. Use NOT EXISTS:
SELECT t.*
FROM TableName t
WHERE NOT EXISTS
(
SELECT 1 FROM TableName t2
WHERE t1.Nickname = t2.Nickname
AND CAST(t1.Date as date) = CAST(t2.Date as date)
AND t2.Total < 0
)