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 3:47 pm
can not select top2 logdate along with other columns when really not required
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.
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?
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 18 total)
You must be logged in to reply to this topic. Login to reply