February 24, 2022 at 1:38 pm
Hi Expert,
I wanted to find out last max 2 dates from tabl but unable to do so
i tried
CREATE TABLE [dbo].[Table_1](
[div_id] [nchar](10) NULL,
[customerid] [nchar](10) NULL,
[div_status] [nchar](10) NULL,
[logdate] [date] NULL
) ON [PRIMARY]
GO
Insert Data:
INSERT INTO [dbo].[Table_1]
([div_id]
,[customerid]
,[div_status]
,[logdate])
VALUES
('495','-1','1','2021-02-14'),
('495','-1' ,'1','2021-02-14'),
('495','-1' ,'1','2021-02-14'),
('502' ,'-1', '1','2021-02-14'),
('513', '-1','1','2021-02-14'),
('538', '-1','1','2021-02-14'),
('545', '-1','1','2021-02-14'),
('563', '-1','1','2021-02-14'),
('577', '-1','1','2021-02-14');
SELECT TOP(2) * FROM TABLE_1 WHERE LOGDATE<(SELECT MAX(LOGDATE) FROM TABLE_1)
but i do not want to add top2 condition any other condition for previous date
Shree
February 24, 2022 at 2:00 pm
I am not sure I understand your question. First the log dates look like they are all '2021-02-14'.
SELECT TOP(2) * FROM TABLE_1 WHERE LOGDATE<(SELECT MAX(LOGDATE) FROM TABLE_1)
Since all your Log Dates are '2021-02-14' there are no records less than '2021-02-14'. Therefore nothing is returned.
SELECT TOP(2) * FROM TABLE_1 ORDER BY LOGDATE DESC
This will get the top 2 rows but there is no guarantee as to which two are returned if the dates are the same.
February 24, 2022 at 2:12 pm
Sorry, here is input data for last 2 max days
INSERT INTO [dbo].[Table_1]
([div_id]
,[customerid]
,[div_status]
,[logdate])
VALUES
('563', '-1','1','2021-02-13'),
('577', '-1','1','2021-02-12'),
('577', '-1','1','2021-02-10');
February 24, 2022 at 2:22 pm
SELECT TOP(2) * FROM TABLE_1
WHERE LOGDATE<(SELECT MAX(LOGDATE) FROM TABLE_1)
ORDER BY LOGDATE DESC
Returns
('563', '-1','1','2021-02-13'),
('577', '-1','1','2021-02-12'),
February 24, 2022 at 2:36 pm
Please do not use top(2) . i can not use in my existing query ..help me with alternatives
February 24, 2022 at 2:53 pm
Please do not use top(2) . i can not use in my existing query ..help me with alternatives
Please explain why TOP (2) is not to be used.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 24, 2022 at 3:47 pm
can not select top2 logdate along with other columns when really not required
February 24, 2022 at 3:59 pm
can not select top2 logdate along with other columns when really not required
That is confusing.
Based on your sample data, please provide a screenshot of the results you would like to see.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 24, 2022 at 5:44 pm
here is the query
SELECT Div_ID,
CustomerID,
Div_Status,
LogDate ,
LogDate
FROM ( SELECT Div_ID,
CustomerID,
Div_Status,
LogDate,
LAG(Div_Status,1,0) OVER (PARTITION BY Div_ID ORDER BY logdate) AS PreviousValue,
LEAD (Div_Status,0,1) OVER (PARTITION BY Div_ID ORDER BY logdate) as NextValue
FROM dbo.Table_1
) AS t
WHERE (PreviousValue = 0 AND Div_Status = 1 AND NextValue = 1 ) OR
(PreviousValue = 0 AND Div_Status = 0 AND NextValue = 1 ) OR
(PreviousValue = 0 AND Div_Status = 1 AND NextValue = 0 ) OR
(PreviousValue = 1 AND Div_Status = 0 AND NextValue = 0 )
condition is where the Div_Status =0. it should also take previous date where the last value was 0 . You can add some sample data in order get record with previous value is 0
February 24, 2022 at 5:57 pm
or please share me any other query which will show max of last 2 days
February 24, 2022 at 8:10 pm
I don't need to see your query. What we need to see are your desired results, based on the sample data provided. You seem unwilling to do this.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 24, 2022 at 8:15 pm
Here is required output
('577', '-1','1','2021-02-14');
('563', '-1','1','2021-02-13')
February 24, 2022 at 8:25 pm
Here is required output
('577', '-1','1','2021-02-14');
('563', '-1','1','2021-02-13')
DROP TABLE IF EXISTS #Table_1;
CREATE TABLE #Table_1
(
div_id NCHAR(10) NULL
,customerid NCHAR(10) NULL
,div_status NCHAR(10) NULL
,logdate DATE NULL
);
INSERT #Table_1
(
div_id
,customerid
,div_status
,logdate
)
VALUES
('577', '-1', '1', '2021-02-14')
,('563', '-1', '1', '2021-02-13')
,('577', '-1', '1', '2021-02-12')
,('577', '-1', '1', '2021-02-10');
SELECT TOP (2)
t.div_id
,t.customerid
,t.div_status
,t.logdate
FROM #Table_1 t
ORDER BY t.logdate DESC;
But you already know this.
Now, will you take the time to provide DDL and sample data in a form which highlights why you cannot use TOP (2) to produce the results you need?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
February 25, 2022 at 9:23 am
A slightly different approach:
WITH LastTwoDates AS (
SELECT DISTINCT TOP(2)
LOGDATE
FROM #TABLE_1
WHERE LOGDATE<(SELECT MAX(LOGDATE) FROM #TABLE_1)
ORDER BY LOGDATE DESC
)
SELECT t1.*
FROM #TABLE_1 t1
INNER JOIN LastTwoDates l2d ON l2d.LOGDATE = t1.LOGDATE
ORDER BY div_id
The output is the same using your test data, but the logic is a bit different.
February 25, 2022 at 9:37 am
Btw, the same can be achieved by utilizing TOP's big brother, the FETCH filtering:
WITH LastTwoDates AS (
SELECT DISTINCT
LOGDATE
FROM #TABLE_1
ORDER BY LOGDATE DESC
OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
)
SELECT t1.*
FROM #TABLE_1 t1
INNER JOIN LastTwoDates l2d ON l2d.LOGDATE = t1.LOGDATE
ORDER BY div_id
The OFFSET 1 can eliminate the need for the subquery, since that'll remove the latest date.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply