Solved

Sorting result by year and month

Posted on 2004-04-17
10
449 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now