sql server mseq: 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

hi willa,

What is happening is, because of "group by" clause on "sail_dat" column each
distinct value of SAIL_DAT is retrieved , therefore values like 1 apr 2004,2
apr 2004 are also retrieved. and while , viewing them you are retrieving
only year and month out of it. So you think values are getting repeated. you
can test this by writing following query.

SELECT
DATENAME(day,SAIL_DAT) AS 'day', --check the day.
DATENAME(YEAR,SAIL_DAT) AS SAIL_YEAR,
DATENAME(MONTH,SAIL_DAT) AS SAIL_MONTH
FROM dbo.IPS_POINTS
GROUP BY DATENAME(YEAR,SAIL_DAT),DATENAME
(MONTH,SAIL_DAT),SAIL_DAT
ORDER BY SAIL_DAT

you can correct your query as follows:

select sail_year, sail_month
from
(SELECT DATENAME(YEAR,SAIL_DAT) AS SAIL_YEAR,
DATENAME(MONTH,SAIL_DAT) AS SAIL_MONTH,
right('0' + cast(month(sail_dat) as varchar(2)),2) 'mnth'
FROM
IPS_POINTS WHERE RES_AGENT_CD = 'PLUMB_N'
GROUP BY DATENAME(YEAR,SAIL_DAT),DATENAME(MONTH,SAIL_DAT),
right('0' + cast(month(sail_dat) as varchar(2)),2)) X
ORDER BY cast(SAIL_YEAR + mnth + '01' as datetime)


--
Vishal Parkar
vgparkar@yahoo.co.in