Re: Sorting results by year and date

From: Vishal Parkar (REMOVE_THIS_vgparkar_at_yahoo.co.in)
Date: 04/18/04

  • Next message: David Hepburn III: "query to list field data types" 
    Date: Sun, 18 Apr 2004 14:44:41 +0530

    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
  • Next message: David Hepburn III: "query to list field data types"

    Relevant Pages