Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi everyone,

    Firstly, Like the new look, very nice!

    I have a table [dbo].[tbl_Discharge] with a column call [Ward] at the moment the column contains data like F1*WSH and PACU*WSH etc, I am trying to update the column data to all char to the left of the * (* appears in different posistions)

    I only want to carry out this update on rows where [download_date] is > Date()

    Here's where I'm at so far but it's not working to well, I was using the PRINT function to display the results before I commit to executing the code on the actual data.

    Many Thanks

    Code:
    Declare @NewWard nvarchar(15),
    			@Ward nvarchar(15),	  
    			@Date datetime,
    			@Len int,
    			@i int
    
    SET @Ward = (SELECT [Ward] From [dbo].[tbl_Discharge]
    WHERE [Download_Date] >= DATEDIFF(dd,0,GETDATE()))
    
    SET @Date = DATEDIFF(dd,0,GETDATE())
    SET @i = 0
    SET @Len = Len(@Ward)
    
    While @i < @Len + 1
    
    		If Right(Left(@Ward,@i),1) = '*'
    			SET @NewWard = Left(@Ward,@i-1)
    
    			PRINT 'Old Ward Name = ' + @Ward + 
    			', new ward name = ' + @NewWard + 
    			', Download Date = ' @Date
    
    SET @i = @i + 1
    GO
    Regards
    Gerbil (AKA Kevin)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why do you use DATEDIFF here?

    Take a look at the CHARINDEX function.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='761779' date='26-Feb-09 12:02']Why do you use DATEDIFF here?

    Take a look at the CHARINDEX function.[/quote]

    Thanks Hans,

    Thats cut down on some code, but I still can't get it to work, it only works if there is only one row that matches the criteria I can't see to get it to cycle through all the rows that meet the select statement.

    In access I was using rst (recordset) for example; (air code)


    Code:
    Dim strNewWard As String
    Dim rst as DAO.RecordSet
    Dim dbs as DAO.Database
    Dim i As integar
       Set dbs = CurrentDb
       Set rst = dbs.OpenRecordSet(SELECT ([Ward]) FROM tbl_Discharge WHERE [Download_Date] >= Date())
    
       Do While Not rst.EOF
    	   For i = 1 to Len(rst!Ward)
    		 If Right(Left(rst!Ward,i),1)="*" Then
    			strNewWard = Left(rst!Ward,i-1)
    		 End If
    		 rst.Update
    		 rst.Next
    	   Next i

    This is where I'm at now, any ideas where I'm going wrong.


    Code:
    CREATE PROCEDURE [dbo].[sp_Update_tbl_Discharge_Ward] 
    			(@NewWard nvarchar(15),
    			@Ward nvarchar(15),	  
    			@Date datetime)
    
    AS
    
    SET @Ward = (SELECT [Ward] From [dbo].[tbl_Discharge]
    WHERE [Download_Date] >= DATEDIFF(dd,0,GETDATE()))
    
    SET @NewWard = Left(@Ward,CHARINDEX('*',@Ward)-1)
    
    UPDATE [dbo].[tbl_Discharge]
    
    SET [Ward] = @NewWard
    GO
    Regards
    Gerbil (AKA Kevin)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Why not run an update sql statement?

    UPDATE [dbo].[tbl_Discharge] SET [Ward] = Left([Ward],CHARINDEX('*',@Ward)-1) WHERE [Download_Date] >= GETDATE()

  5. #5
    3 Star Lounger
    Join Date
    Jul 2008
    Location
    Suffolk, United Kingdom
    Posts
    308
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='761791' date='26-Feb-09 14:59']Why not run an update sql statement?

    UPDATE [dbo].[tbl_Discharge] SET [Ward] = Left([Ward],CHARINDEX('*',@Ward)-1) WHERE [Download_Date] >= GETDATE()[/quote]

    Many thanks Hans, Works great.
    Regards
    Gerbil (AKA Kevin)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •