Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Order by desc for specific date Expand / Collapse
Author
Message
Posted Tuesday, May 27, 2014 11:38 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 15, Visits: 78
There is a table "Marketshare" which has fields say "startdate" and "rate". i need to sort the records of this table and have to take the top 1 latest record on 1 july of each year.
Was trying with Order by along with datepart() didn't able understand how to proceed.

MarketShare<table>

StartDate || Rate
-------------------
22/06/2014 234
28/06/2014 789
29/06/2014 987
02/07/2014 890

I need the first record which has start date before 1 july.

Result :
StartDate || Rate
---------------------
29/06/2014 987
Post #1574902
Posted Tuesday, May 27, 2014 12:23 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 17,843, Visits: 15,789
Please post your query with sample data and we can assist.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1574912
Posted Tuesday, May 27, 2014 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:14 AM
Points: 12,905, Visits: 32,178
something like this help a little?
ORDER BY 
CASE
WHEN datepart(mm,startdate) = 7
AND datepart(dd,startdate) = 1
THEN 1
ELSE 99
END



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1574913
Posted Tuesday, May 27, 2014 1:10 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 15, Visits: 78
Hi ,


MarketShare<table>

StartDate || Rate
-------------------
22/06/2014 234
28/06/2014 789
29/06/2014 987
02/07/2014 890

i need latest start date record on july 1

Result :
StartDate || Rate
---------------------
29/06/2014 987


thanks in advance
Post #1574951
Posted Tuesday, May 27, 2014 1:13 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 8:28 AM
Points: 3,805, Visits: 8,556
Something like this?

DECLARE @date = '20140701'

SELECT TOP 1 *
FROM MarketShare
WHERE StartDate <= @date
ORDER BY StartDate DESC




Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1574953
Posted Tuesday, May 27, 2014 1:21 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 15, Visits: 78
Actually i am using this as a sub query inside my view, Declaring a date won't work because it should work for coming year records too..
Post #1574957
Posted Tuesday, May 27, 2014 1:25 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 17,843, Visits: 15,789
Here's a quick and dirty script that seems to get what you want.

SET DATEFORMAT  DMY
DECLARE @MarketShare TABLE (startdate DATE, rate INT)

INSERT INTO @MarketShare
( startdate, rate )
VALUES (
'22/06/2014', 234),(
'28/06/2014', 789),(
'29/06/2014', 987),(
'02/07/2014', 890)

SELECT TOP 1 *
FROM @MarketShare
WHERE startdate <= '1/7/2014'
ORDER BY startdate DESC





Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1574960
Posted Wednesday, May 28, 2014 2:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, August 8, 2014 7:13 AM
Points: 15, Visits: 78
I achieved it proceeding like this

select startdate, case when startdate>=convert(datetime,'1 July '+convert(varchar,datepart(yyyy,GETDATE())))
then '1 Jan 1900' else startdate end Datecalc from MARKETSHARE order by Datecalc desc

anyway thanks all of you for providing me the idea to proceed
Post #1575081
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse