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: Wednesday, July 9, 2014 9:07 AM
Points: 15, Visits: 77
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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:02 PM
Points: 21,342, Visits: 15,017
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1574912
Posted Tuesday, May 27, 2014 12:24 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:22 PM
Points: 12,890, Visits: 31,849
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: Wednesday, July 9, 2014 9:07 AM
Points: 15, Visits: 77
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: Yesterday @ 3:37 PM
Points: 3,374, Visits: 7,300
Something like this?

DECLARE @date = '20140701'

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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

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: Wednesday, July 9, 2014 9:07 AM
Points: 15, Visits: 77
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


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 10:02 PM
Points: 21,342, Visits: 15,017
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


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1574960
Posted Wednesday, May 28, 2014 2:31 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 9:07 AM
Points: 15, Visits: 77
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