?
Solved

Sorting result by year and month

Posted on 2004-04-17
10
Medium Priority
?
502 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
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 1000 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

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

830 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