Sorting result by year and month

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
LVL 1
willa666Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
>>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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
willa666Author Commented:
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
 
willa666Author Commented:
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
 
Anthony PerkinsCommented:
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
 
Anthony PerkinsCommented:
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
 
willa666Author Commented:
:)

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
 
Anthony PerkinsCommented:
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
 
willa666Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.