Calendar

  • Here is the challenge: I have a calendar table in my DB

    CREATE TABLE [dbo].[testcal] (

    [calid] int IDENTITY(1,1) NOT NULL,

    [cal_Date] datetime NULL,

    [cal_Description]text NULL

    )

    ON [PRIMARY]

    GO

    Here is the code for getting some initial data into this table:

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('10/1/2009', 'Pack Meeting')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('10/15/2009', 'Den Meeting')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('11/1/2009', 'Pack Meeting')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('11/25/2009', 'Social')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('11/26/2009', 'Popcorn Rally')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('12/1/2009', 'Pack Meeting')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('12/20/2009', 'Den Meeting')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('12/23/2009', 'Christmas Caroling')

    GO

    INSERT INTO [dbo].[testcal]([cal_Date], [cal_Description])

    VALUES('1/15/2010', 'New Years Pack Bash')

    GO

    Here is some sample code for pulling records from this table:

    SELECT

    'Date: ' + CONVERT(varchar(11), cal_date) + ' Description: ' + CONVERT(varchar(250), cal_Description) AS [Calendar Listing]

    FROM testcal

    ORDER BY cal_Date ASC

    Here of course is the output:

    Calendar Listing

    --------------------------------------------------

    Date: Oct 1 2009 Description: Pack Meeting

    Date: Oct 15 2009 Description: Den Meeting

    Date: Nov 1 2009 Description: Pack Meeting

    Date: Nov 25 2009 Description: Social

    Date: Nov 26 2009 Description: Popcorn Rally

    Date: Dec 1 2009 Description: Pack Meeting

    Date: Dec 20 2009 Description: Den Meeting

    Date: Dec 23 2009 Description: Christmas Caroling

    Date: Jan 15 2010 Description: New Years Pack Bash

    HOWEVER what I would like is the following output.

    Calendar Listing

    --------------------------------------------------

    October 2009 - Calendar Events

    Date: Oct 1 2009 Description: Pack Meeting

    Date: Oct 15 2009 Description: Den Meeting

    November 2009 - Calendar Events

    Date: Nov 1 2009 Description: Pack Meeting

    Date: Nov 25 2009 Description: Social

    Date: Nov 26 2009 Description: Popcorn Rally

    December 2009 - Calendar Events

    Date: Dec 1 2009 Description: Pack Meeting

    Date: Dec 20 2009 Description: Den Meeting

    Date: Dec 23 2009 Description: Christmas Caroling

    January 2010 - Calendar Events

    Date: Jan 15 2010 Description: New Years Pack Bash

    The idea is to output "Month Year - Calendar Events" based on the records

    Any help would help. Thanks

  • Look up UNION in BOL.

    Greg
    _________________________________________________________________________________________________
    The glass is at one half capacity: nothing more, nothing less.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply