I am trying to create a dropdown box with the MONTH YEAR (January 2004) displayed and I needing it displayed in a YEAR desc and MONTH desc order however I do NOT want the current MONTH YEAR (February 2004) to be shown. I have it where I can list my months and year in a DESC order (i.e., January 2004, December 2003, etc.) but what is weird is that MARCH 2003 is missing. I cannot see why. Been looking at this too long. ANY help or direction would be appreciated. Thank you!

Here is the contents of my SP:

DECLARE @option datetime,
    @now datetime

CREATE TABLE #Temp (access_month_num int, access_month varchar(50), access_year varchar(50))

SET @now = getdate()

SET @option = (SELECT MIN(access_date) FROM web_log )

WHILE @option < @now
BEGIN
    INSERT INTO #Temp
        SELECT MONTH(@option) as access_month_num,
        CASE
            WHEN MONTH(@option) = 1 THEN 'January'
            WHEN MONTH(@option) = 2 THEN 'February'
            WHEN MONTH(@option) = 3 THEN 'March'
            WHEN MONTH(@option) = 4 THEN 'April'
            WHEN MONTH(@option) = 5 THEN 'May'
            WHEN MONTH(@option) = 6 THEN 'June'
            WHEN MONTH(@option) = 7 THEN 'July'
            WHEN MONTH(@option) = 8 THEN 'August'
            WHEN MONTH(@option) = 9 THEN 'September'
            WHEN MONTH(@option) = 10 THEN 'October'
            WHEN MONTH(@option) = 11 THEN 'November'
            WHEN MONTH(@option) = 12 THEN 'December'
        END
        AS access_month, YEAR(@option) AS access_year

    SET @option = DATEADD(mm,1,@option)
END

SELECT * FROM #Temp WHERE (access_month_num + access_year <> month(getdate()) + year(getdate())) ORDER BY access_year desc, access_month_num desc

DROP TABLE #Temp

Doing this will misuse your server resources by creating a temp table on the fly.
Do you have any items for March 2003?


Just create a static table with the values that you need , then you can query the table easier. Add values to the table at the end of the month with an sp. Run it with a job.



Sal

Sal,

Thanks for the response. I do have items in March 2003.

See my comments, something does notmake sense

DECLARE @option datetime,
    @now datetime




CREATE TABLE #Temp (access_month_num int, access_month varchar(50), access_year varchar(50))

SET @now = getdate() // Feb 4 2004

SET @option = (SELECT MIN(access_date) FROM web_log ) // let's say March 3 2003

WHILE @option < @now // @option is < Now
BEGIN
    INSERT INTO #Temp
        SELECT MONTH(@option) as access_month_num,
                   // = 3
        CASE
            WHEN MONTH(@option) = 1 THEN 'January'
            WHEN MONTH(@option) = 2 THEN 'February'
            WHEN MONTH(@option) = 3 THEN 'March'
            WHEN MONTH(@option) = 4 THEN 'April'
            WHEN MONTH(@option) = 5 THEN 'May'
            WHEN MONTH(@option) = 6 THEN 'June'
            WHEN MONTH(@option) = 7 THEN 'July'
            WHEN MONTH(@option) = 8 THEN 'August'
            WHEN MONTH(@option) = 9 THEN 'September'
            WHEN MONTH(@option) = 10 THEN 'October'
            WHEN MONTH(@option) = 11 THEN 'November'
            WHEN MONTH(@option) = 12 THEN 'December'
        END
        AS access_month, YEAR(@option) AS access_year
       // March = 2003

    SET @option = DATEADD(mm,1,@option) //now it is @option = April 3 2003
END

SELECT * FROM #Temp WHERE (access_month_num + access_year <> month(getdate()) + year(getdate()))
                   //3 + 2003 <> 2 + 2004
ORDER BY access_year desc, access_month_num desc


DROP TABLE #Temp


3 2003 will never equal 2 2004



Sal

Thanks for pointing that out. I will work on that bad. Thanks.

Another colleague had time to look at my logic and reminded me about the concatenation regarding strings whereby my variables were identified as INT and not varchar. That fixed the concatenation and my logic problem as well as fix my exclusion of the current month/year activities. The problem was in the SELECT statement of my SP and here is the fix:

SELECT * FROM #Temp WHERE
    cast(cast(access_year as varchar(4)) + '-' + cast(access_month_num as varchar(2)) + '-1' as datetime)
    <
     cast(cast(year(getdate()) as varchar(4)) + '-' + cast(month(getdate()) as varchar(2)) + '-1' as datetime)
ORDER BY access_year desc, access_month_num desc