Select record with Date difference in where clause in SQL Server

sat
 
on May 10, 2021 10:42 PM
413 Views

Hi

My Sample table in MSSql database is as under:

Com                       DC

Company1          4/5/2021

Company2          5/6/2021

Company3          5/6/2021

Company3          5/8/2021

Company5          5/9/2021

Company4          5/9/2021

Company2          5/9/2021

Company6          3/9/2021

Company7          2/9/2021

Company8          1/9/2021

Company9          11/9/2020

Company8          5/10/2021

I am trying to derive a gridview with list of companies from above database where MAX(DC) is >= 120 days.

My Select Statement is:

SELECT Com, MAX(DC) DC FROM DSR Where DATEDIFF(Day,DC,GETDATE()) >= 120 Group By Com Order By DC ASC

Its deriving as under:

 Com           DC

Company9      11/09/2020

Company8      01/09/21

Ideally it should have given me only one entry [Company9 11/09/2020] as Last entry in DB [Company8 5/10/2021 is MAX(DC) for Company8] which doesn't match above condition. It’s picking up Company8 01/09/21 I don't know why!

My condition says MAX(DC).

Please help and let me know what could be the problem in my select statement.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
sat
 
on May 10, 2021 10:57 PM

Hi.

I resolved it by changing my select statement as under:

Select * From (Select Com, MAX(DC) DC FROM DSR Group By Com) As DSR Where DATEDIFF(Day,DC,GETDATE()) >= 120 Order By DC ASC

I used subquery and it worked well.

Thanks again.

dharmendr
 
on May 11, 2021 12:23 AM

You can do it with where condition.

Refer below query.

SQL

DECLARE @DSR AS TABLE(Com VARCHAR(15),DC VARCHAR(10))

INSERT INTO @DSR VALUES('Company1','4/5/2021')
INSERT INTO @DSR VALUES('Company2','5/6/2021')
INSERT INTO @DSR VALUES('Company3','5/6/2021')
INSERT INTO @DSR VALUES('Company3','5/8/2021')
INSERT INTO @DSR VALUES('Company5','5/9/2021')
INSERT INTO @DSR VALUES('Company4','5/9/2021')
INSERT INTO @DSR VALUES('Company2','5/9/2021')
INSERT INTO @DSR VALUES('Company6','3/9/2021')
INSERT INTO @DSR VALUES('Company7','2/9/2021')
INSERT INTO @DSR VALUES('Company8','1/9/2021')
INSERT INTO @DSR VALUES('Company9','11/9/2020')
INSERT INTO @DSR VALUES('Company8','5/10/2021')

SELECT * FROM @DSR WHERE DC IN (SELECT MAX(DC) DC FROM DSR GROUP BY Com) AND DATEDIFF(DAY,DC,GETDATE()) >= 120 ORDER BY DC ASC

Output

    Com         DC

Company9 11/9/2020