March 31, 2014 at 1:23 pm
Hello,
I have the two fields below and I would like to always sort by looking at the last twelve months. So if I'm running this in April, I want to get the data in this order (Apr, May, Jun, Jul Aug, Sep, Oct, Nov, Dec, Jan, Feb, Mar).
Or If I'm running this in May, I expect to show (May, Jun, Jul, Aug, Sep, Oct,...Mar, Apr)
I would really appreciate your help.
Thank you
MonthTrades
Jan2019
Feb2941
Mar2968
Apr1523
May1983
Jun2398
Jul2347
Aug1348
Sep2772
Oct2289
Nov1765
Dec1256
March 31, 2014 at 1:40 pm
You need to have the complete date value to order the months correctly or you could have another value to order by.
Examples:
DECLARE @Date date;
SET @Date = '20140405'
SELECT LEFT( DATENAME( MM, DATEADD(MM, DATEDIFF(MM, 0, @Date)-N, 0)), 3) Month_Name
,DATEADD(MM, DATEDIFF(MM, 0, @Date)-N, 0) Month_Date
,N
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))Numbers(N)
ORDER BY Month_Date
SELECT LEFT( DATENAME( MM, DATEADD(MM, DATEDIFF(MM, 0, @Date)-N, 0)), 3)Month_Name
,DATEADD(MM, DATEDIFF(MM, 0, @Date)-N, 0) Month_Date,
N
FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12))Numbers(N)
ORDER BY N DESC
March 31, 2014 at 1:40 pm
Not enough information to be of much help. Please provide the DDL (CREATE TABLE statement(s)) for the table(s) involved, sample data as INSERT INTO statement(s) for the table(s) involved, and the expected results based on the sample data.
For help on this, please read the first article I reference below in my signature block. It will show you what you need to post and how to post it so that you get the best possible answers and tested code.
March 31, 2014 at 1:42 pm
Create and populate sample table:
CREATE TABLE dbo.T1
(
m TINYINT NOT NULL,
val INT NOT NULL
)
GO
INSERT INTO dbo.T1
VALUES(1,2019),(2,2941),(3,2968),(4,1523),(5,1983),(6,2398),
(7,2347),(8,1348),(9,2772),(10,2289),(11,1765),(12,1256)
GO
Here is the query:
SELECT *
FROM dbo.T1
ORDER BY CASE WHEN m < MONTH(GETDATE()) THEN 12 + m ELSE m END;
___________________________
Do Not Optimize for Exceptions!
March 31, 2014 at 1:51 pm
Ok so I added another field that has the numbers of the month to help with sorting. How should I write the function now?
Thank you.
MonthTrades Number
Jan20191
Feb29412
Mar29683
Apr15234
May19835
Jun23986
Jul23477
Aug13488
Sep27729
Oct228910
Nov176511
Dec125612
March 31, 2014 at 1:59 pm
That is actually how the data is stored in your database? How do you know what year the month exists? What you have posted still doesn't really help.
March 31, 2014 at 2:09 pm
What if the data is March 2014, February 2014,January 2014, December 2013, November 2013 ... April 2013. Would you be able to sort that?
March 31, 2014 at 2:11 pm
marsidbomba (3/31/2014)
What if the data is March 2014, February 2014,January 2014, December 2013, November 2013 ... April 2013. Would you be able to sort that?
Only if you cast them as dates.
SELECT CAST( 'March 2014' AS date)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply