Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789

    Unanswered: Recent dates only

    I have two tables totally unrelated but give the same information, the difference is the duration. I need to create a stored procedure that will give the recent issue dates only, accept if they have already expired. I'm not exactly sure how to do that. We only want the employees to see the current issue date as long as the exclusion has not expired. Can anyone help please


    Code:
    ALTER PROCEDURE [dbo].[SrchIssueDateLstNm_RecntIssueDte]
    @StartIssueDate datetime = null,
    @EndIssueDate datetime = null,
    @Enter_LastName nvarchar(10)  = null
    
    AS 
    SELECT      [dbo].[30 Day exclusion].[First Name], [dbo].[30 Day exclusion].[Last Name], [dbo].[30 Day exclusion].[Issue Date],
                            [dbo].[Extended Exclusions].[First Name], [dbo].[Extended Exclusions].[Last Name], [dbo].[Extended Exclusions].[Issue Date]
    FROM							[dbo].[30 Day exclusion] INNER JOIN 
                            [dbo].[30 Day exclusion].[id] ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
            
    WHERE  (@StartIssueDate is null or [Issue Date] >= @StartIssueDate) 
    AND (@EndIssueDate is null or [Issue Date] <= @EndIssueDate)
    AND (@Enter_LastName is null or [Last Name] = @Enter_LastName)
    ORDER BY [Last Name]

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your problem is right here --
    Code:
      FROM [dbo].[30 Day exclusion] 
    INNER 
      JOIN [dbo].[30 Day exclusion].[id] 
        ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
    try this instead --
    Code:
      FROM [dbo].[30 Day exclusion] 
    INNER 
      JOIN [dbo].[Extended Exclusions] 
        ON [dbo].[Extended Exclusions].[ID] = [dbo].[30 Day exclusion].[id]
    this would be easier to spot if you adopted a consistent indentation style

    also, remember, always test your query outside the stored proc before attempting to parameterize it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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