Min Date - two columns - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Min Date - two columns

Author

Topic

zari
Starting Member

4 Posts

Posted - 2012-09-12 : 12:50:20

The start of my query looks like this:

SELECT DISTINCT cars.Name0 AS 'CAR_NAME',DATEDIFF(day, mu.LastUsage, GETDATE()) AS Date_Last_Used

Problem: Regal shows up twice, and I just want the most recent date.

TABLE
CAR_NAME Date_Last_Used
Regal 5
Regal 8
GTO 6
Firefly 0
Focus 8

ANy help is appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 12:53:52

[code]
SELECT cars.Name0 AS 'CAR_NAME',DATEDIFF(day, MAX(mu.LastUsage), GETDATE()) AS Date_Last_Used
FROM...
GROUP BY cars.Name0
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zari
Starting Member

4 Posts

Posted - 2012-09-12 : 13:08:26

Regal is still showing up twice :-(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 13:15:14

so what are you expecting? one record for Regal with value as 13?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zari
Starting Member

4 Posts

Posted - 2012-09-12 : 13:25:33

I just want Regal to show up once based on the most recent date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 13:50:24

but you're not showing date isnt it?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 13:52:36

or may be this is what you're after?


select *
from
(
select cars.Name0 AS 'CAR_NAME',DATEDIFF(day, mu.LastUsage, GETDATE()) AS Date_Last_Used ,
row_number() over (partition by cars.Name0 order by mu.LastUsage desc) AS Seq
FROM...
)t
where seq=1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

zari
Starting Member

4 Posts

Posted - 2012-09-12 : 14:12:16

Excellent!!!!!!!!!!

Now it would be nice to explain the logic of this query

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-12 : 14:16:07

it basically groups your date based on Name0 values and within each group orders data based on descending value of lastUsage field. so record with latest LastUsage value for the Name0 gets 1, next latest 2 etc. then its just a matter of filtering on sequence number generated = 1 to get the latest records corresponding to each Name0

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-12 : 14:39:39

Both of visakh's queries should return the same result. Was there a cut-n-paste error or something else going on?

DECLARE @Foo TABLE (Name0 VARCHAR(20), LastUsage DATETIME2(2))

INSERT @Foo
VALUES
('Regal', DATEADD(DAY,-5, SYSDATETIME())),
('Regal', DATEADD(DAY,- 8, SYSDATETIME())),
('GTO', DATEADD(DAY,-6, SYSDATETIME())),
('Firefly', DATEADD(DAY,-0, SYSDATETIME())),
('Focus', DATEADD(DAY,-8, SYSDATETIME()))

-- Original
SELECT DISTINCT Name0 AS 'CAR_NAME',DATEDIFF(day, LastUsage, GETDATE()) AS Date_Last_Used 
FROM @Foo

-- visakh16 query 1
SELECT Name0 AS 'CAR_NAME',DATEDIFF(day, MAX(LastUsage), GETDATE()) AS Date_Last_Used 
FROM @Foo
GROUP BY Name0

-- visakh16 query 2
select *
from
(
select Name0 AS 'CAR_NAME',DATEDIFF(day, LastUsage, GETDATE()) AS Date_Last_Used ,
row_number() over (partition by Name0 order by LastUsage desc) AS Seq
FROM @foo
)t
where seq=1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources