Solved

Sorting result by year and month

Posted on 2004-04-17
10
492 Views
Last Modified: 2012-05-04
I Have a simple query that i am running. but i need the results to only show each month only once, this one has stumpted me! :)
I know why it is happening but i cant solve it

SO should i be using a cast command or somthing else to get around this problem?

Below is my query and the result of when it is run.

Willa

SELECT

DATENAME(YEAR,SAIL_DAT) AS SAIL_YEAR,
DATENAME(MONTH,SAIL_DAT) AS SAIL_MONTH

FROM dbo.IPS_POINTS

WHERE RES_AGENT_CD = 'PLUMB_N'

GROUP BY DATENAME(YEAR,SAIL_DAT),DATENAME(MONTH,SAIL_DAT),SAIL_DAT

ORDER BY SAIL_DAT


2004      January
2004      April
2004      April
2004      May
2004      May
2004      June
2004      June
2004      June
2004      June
2004      July
2004      August
2004      November
2004      November
2004      December
2005      February
2005      May
0
Comment
Question by:willa666
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
10 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10851730
Change this:
GROUP BY DATENAME(YEAR,SAIL_DAT),DATENAME(MONTH,SAIL_DAT),SAIL_DAT

To:
GROUP BY DATENAME(YEAR,SAIL_DAT),DATENAME(MONTH,SAIL_DAT)


0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10851751
But I suspect this is what you are after:

SELECT SAIL_YEAR,
            DATENAME(MONTH, SAIL_MONTH) AS SAIL_MONTH
FROM    (SELECT    DATEPART(YEAR, SAIL_DAT) AS SAIL_YEAR,
                             DATEPART(MONTH, SAIL_DAT) AS SAIL_MONTH
              FROM       dbo.IPS_POINTS
              WHERE     RES_AGENT_CD = 'PLUMB_N'
              GROUP BY DATEPART(YEAR, SAIL_DAT),
                              DATEPART(MONTH, SAIL_DAT)) t
ORDER BY SAIL_YEAR, SAIL_MONTH
0
 
LVL 1

Author Comment

by:willa666
ID: 10851754
BUt if i remove the SAIL_DAT then the recordset is return with out the months being in order, becase i also have to remove the ORDER BY SAIL_DAT part as well or i get a "Server: Msg 8127, Level 16, State 1, Line 1
Column name 'dbo.IPS_POINTS.SAIL_DAT' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause."Error message.  UC :)

here is thew results of the query with both oarts removed

2004      April
2004      August
2004      December
2004      January
2004      July
2004      June
2004      May
2004      November
2005      February
2005      May
0
Business Impact of IT Communications

What are the business impacts of how well businesses communicate during an IT incident? Targeting, speed, and transparency all matter. Find out more in this infographic.

 
LVL 1

Author Comment

by:willa666
ID: 10851758
I just get this

2004      January
2004      January
2004      January
2004      January
2004      January
2004      January
2004      January
2004      January
2005      January
2005      January
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10851780
Sorry, not thinking right:

SELECT SAIL_YEAR,
            DATENAME(Month, DATEADD(Month, ORDER_MONTH, '2004-12-31'))       -- Hack: Ignore the date I used I just want to get the month
FROM    (SELECT    DATEPART(YEAR, SAIL_DAT) AS SAIL_YEAR,
                             DATEPART(MONTH, SAIL_DAT) AS SAIL_MONTH
              FROM       dbo.IPS_POINTS
              WHERE     RES_AGENT_CD = 'PLUMB_N'
              GROUP BY DATEPART(YEAR, SAIL_DAT),
                              DATEPART(MONTH, SAIL_DAT)) t
ORDER BY SAIL_YEAR, SAIL_MONTH
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10851791
Sorry for the multiple posts, brain is deffinitely dead:

SELECT SAIL_YEAR,
            DATENAME(Month, DATEADD(Month, SAIL_NUM_MONTH, '2004-12-31')) AS SAIL_MONTH
FROM    (SELECT    DATEPART(YEAR, SAIL_DAT) AS SAIL_YEAR,
                             DATEPART(MONTH, SAIL_DAT) AS SAIL_NUM_MONTH
              FROM       dbo.IPS_POINTS
              WHERE     RES_AGENT_CD = 'PLUMB_N'
              GROUP BY DATEPART(YEAR, SAIL_DAT),
                              DATEPART(MONTH, SAIL_DAT)) t
ORDER BY SAIL_YEAR, SAIL_NUM_MONTH

Let me know, if you need an explanation for all of this shenanigans.
0
 
LVL 1

Author Comment

by:willa666
ID: 10851812
:)

I was just writing back as well!

OK THATS FAB!! IT WORKS!!!!!!!!!!

SO i guess that the query is being run against a sub query, the sub query creates a table that driulls down to year and month and then the main query takes the table, converts the SAIL_NUM_MONTH field to the DD/MM/YYYY format and orders it by SAIL_YEAR and  SAIL_NUM_MONTH.

RIGHT?

:)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 10851817
This may be clearer:

SELECT      RIGHT(YYYYMM, 4) AS SAIL_YEAR,
            DATENAME(Month, YYYYMM + '-01') AS SAIL_MONTH
FROM      (SELECT      CONVERT(varchar(7), SAIL_DAT, 120) AS YYYYMM
            FROM      dbo.IPS_POINTS
            WHERE      RES_AGENT_CD = 'PLUMB_N'
            GROUP BY      CONVERT(varchar(7), SAIL_DAT, 120)) t
ORDER BY       YYYYMM
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 250 total points
ID: 10851828
>>SO i guess that the query is being run against a sub query<<
Actually this is called a derived table.  But in esence yes:  The derived table gives the correct data the main Select gives it the format.  

I suspect there may be a better way, but I am not thinking too straight.
0
 
LVL 1

Author Comment

by:willa666
ID: 10851837
Hey Buddy

This solve my issuse so to me mate this is the solution that will work for me!

You've got points!

YOUR A STAR MATE! :)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question