Post reply

Creating a daily Diary

  • July 11, 2007 at 6:22 am

    #153671

    Hi There im Trying to create a daily diary

    and im wondering if there is anything I can do to concatenate the enteries for the same day in time order into one field

    my database stores the date and time in one field and details my appointments details place etc and who im meeting in another field

    What id like is to be able to query by month to show the individual days and inorder the items I have to do

    ie

    Jan 11 11:30 AM Meet Mum for lunch

    Jan 11  12:30 PM Meet Chris for bowling

    Jan 11   17:30 PM Work on Mortage figures

    Jan 12 09:00 AM Continue Job hunting

    Jan 12 16:30 PM Meet Celeste for interview

     

    Would become

    Jan 11 11:30 - Meet Mum for Lunch 12:30 Meet Chris for Bowling 17:30 Work on Mortgage figures

    Jan 12 09:00 - Continue job hunting 16:30 Meet Celeste for interview

     

    Im hoping this can be done without the use of a cursor

    using SQL 2000

    thanks

  • July 11, 2007 at 6:32 am

    #718578

    I can't think of anything without using cursors. You will havt to use them anyhow.

    Cheers,
    Sugeshkumar Rajendran
    SQL Server MVP
    http://sugeshkr.blogspot.com

  • July 11, 2007 at 6:57 am

    #718589

    i think your requirement is similar to these

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=376899

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=379131

    please let us know, if that is not the case so that we can get a crack on it

    Everything you can imagine is real.

  • July 11, 2007 at 6:58 am

    #718591

    You do NOT need a cursor to do this... but I need a bit more information...

    1. Are you the only person who will store this type of data in the database?
    2. Why do you need to concatenate all the appointments into a single column?  I ask because it may make a difference in how it is done.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 11, 2007 at 7:19 am

    #718602

    Essentially What Ive done is actually try and simplify things in my question:

    what im trying to do is concatenate rows together where the date is the same.

    and then the appointments for that same day to appear in chronological order. I am not the only person writing these records to the database.

    I would like the rows concatenated in this way so that I can create a calendar type view within excel. but at the same time I dont really want there to be too much VBA within the calendar.

  • July 11, 2007 at 7:24 am

    #718605

    Yes bledu Very similar to

    http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=338&messageid=379131

     

  • July 11, 2007 at 7:38 am

    #718620

    So... post the actual name of the column that identifies individuals along with a bit of sample data for those, the actual names of the other two columns, the datatypes of the 3 columns, and we might even be able to come up with a complete answer

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 11, 2007 at 8:15 am

    #718642

    CREATE TABLE #DIARY (

    MYUSER nvarchar(50),

    STARTDATE Datetime,

    ACTIVITY nvarchar(255)

    )

     

    INSERT INTO #DIARY VALUES('RBOTT','11 Jan 2006 10:00','Meet Mum')

    INSERT  INTO #DIARY VALUES('RBOTT','11 Jan 2006 14:00','Meet Nan')

    INSERT INTO #DIARY VALUES('MCOLL','13 Jan 2006 10:00','Change Washer')

    INSERT  INTO #DIARY VALUES('MCOLL','14 Jan 2006 15:00','Find Shoes')

    INSERT INTO #DIARY VALUES('RBOTT','15 Jan 2006 09:00','Meet Nan')

    INSERT  INTO #DIARY VALUES('RBOTT','15 Jan 2006 15:00','Cook Tea')

    INSERT INTO #DIARY VALUES('RBOTT','15 Jan 2006 17:00','Eat Tea')

     

    how about that

  • July 11, 2007 at 5:55 pm

    #718826

    Just about perfect... I'll be back in a few...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • July 11, 2007 at 7:50 pm

    #718836

    Heh... see what happens when you provide just the right amount of data and a table definition to go along with a good description ?

    --=============================================================================
--      Setup the environment and a test table with data.
--      This section is NOT part of the solution, but you may want to check
--      out the table and the covering index that was used.
--=============================================================================
--===== Identify and use a "safe" database to create a "real" table
     -- for testing because a function cannot access a temp table.
    USE TempDb
    --===== Supress the autodisplay of rowcounts
    SET NOCOUNT ON
    --===== If the test table exists, drop it
     IF OBJECT_ID('dbo.Diary') IS NOT NULL
        DROP TABLE dbo.Diary
    --===== Create the test table to hold sample data
 CREATE TABLE dbo.Diary 
        (
        EntryNumber INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
        MyUser      NVARCHAR(50),
        StartDate   DATETIME,
        Activity    NVARCHAR(255)
        )
GO
--===== Populate the test table with some data
 INSERT INTO dbo.Diary
        (MyUser,StartDate,Activity)
 SELECT'RBOTT','11 Jan 2006 10:00','Meet Mum'      UNION ALL
 SELECT'RBOTT','11 Jan 2006 14:00','Meet Nan'      UNION ALL
 SELECT'MCOLL','13 Jan 2006 10:00','Change Washer' UNION ALL
 SELECT'MCOLL','14 Jan 2006 15:00','Find Shoes'    UNION ALL
 SELECT'RBOTT','15 Jan 2006 09:00','Meet Nan'      UNION ALL
 SELECT'RBOTT','15 Jan 2006 15:00','Cook Tea'      UNION ALL
 SELECT'RBOTT','15 Jan 2006 17:00','Eat Tea'
GO
--===== Add a covering index for max performance of the function
 CREATE INDEX IX_Diary_MyUser_StartDate_Activity
     ON dbo.Diary (StartDate,MyUser,Activity)
GO
     
    --=============================================================================
--      Create a function to eliminate the need for a cursor.
--      The function would be a permanent installation on your server and in
--      the correct database (same DB as the Diary table).
--=============================================================================
--===== If the function exists, drop it
     IF OBJECT_ID('dbo.ConcatCalendarDay') IS NOT NULL
        DROP FUNCTION dbo.ConcatCalendarDay
GO
--===== Create the function that concatenates activities for the Diary table
 CREATE FUNCTION dbo.ConcatCalendarDay
/******************************************************************************
 Purpose:
 This function concatenates all activities for a given user on a
 given date.
******************************************************************************/
--===== Declare the I/O parameters
        (
        @pDate DATETIME,
        @pUser NVARCHAR(50)
        )
RETURNS NVARCHAR(4000)
     AS 
  BEGIN -----------------------------------------------------------------------
        --===== Declare the result variable to concatenate to
        DECLARE @Result NVARCHAR(4000)
            --===== Strip the time off the given date parameter
            SET @pDate = DATEADD(dd,DATEDIFF(dd,0,@pDate),0)
            --===== Add the Month and day to the result
            SET @Result = CONVERT(CHAR(6),@pDate,107)
            --===== Concatenate each time and activity for the given date and user 
             -- to the result using a "set based loop"
         SELECT @Result = @Result
                        + ' - ' + RIGHT(CONVERT(VARCHAR(26),StartDate,100),7)
                        + ' ' + Activity
           FROM dbo.Diary
          WHERE MyUser     = @pUser
            AND StartDate >= @pDate
            AND StartDate  < @pDate + 1
          ORDER BY StartDate
            --===== Return the concatenated result or an error if too big
             IF DATALENGTH(@Result) = 8000
            SET @Result =  '** TRUNCATED ** ' + @Result
         RETURN @Result
        END -----------------------------------------------------------------------
GO
     
    --=============================================================================
--      Demo the solution... note that you could easily turn this into a 
--      stored proc with parameters for a StartDate range and the user.
--=============================================================================
 SELECT DISTINCT
        MyUser, dbo.ConcatCalendarDay(StartDate,MyUser) AS Activities
   FROM dbo.Diary
  ORDER BY MyUser, Activities
GO

    --Jeff Moden 
    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    Change is inevitable... Change for the better is not.
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
     
      
    •  
  •   
     
     
     July 12, 2007 at 12:34 am 
     
      #718873  
          
         
     
    Jeff,
    seeing that there has been a couple of similar requests,based on this response why don't you do an article about it. 
    Ta
    Everything you can imagine is real.
     
      
    •  
  •   
     
     
     July 12, 2007 at 7:05 pm 
     
      #719145  
          
         
     
    So, Hickymanz... any feedback on the code I provided?
    --Jeff Moden 
    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    Change is inevitable... Change for the better is not.
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
     
      
    •  
  •   
     
     
     July 12, 2007 at 7:06 pm 
     
      #719146  
          
         
     
    Thanks for the confidence, Bledu... not sure it would do any good, though... if I were to give a general example, someone would fail to apply the knowledge to a specific example.  
    --Jeff Moden 
    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    Change is inevitable... Change for the better is not.
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
     
      
    •  
  •   
     
     
     July 16, 2007 at 4:06 am 
     
      #719610  
          
         
     
    Thanks Jeff Sorry i didnt Reply was doing some testing on soemthing else the daty before tand then had a crazy day off!
     
    Works beautifully althought Ive never used select distinct to query in this way. but im glad its there.
    Thank you very much 
     
      
    •  
  •   
     
     
     July 16, 2007 at 5:59 am 
     
      #719644  
          
         
     
    You bet... let us know if you need anything else...
    --Jeff Moden 
    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    Change is inevitable... Change for the better is not.
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
     
      
    •  
Viewing 15 posts - 1 through 14 (of 14 total)
 
You must be logged in to reply to this topic. Login to reply