Stored Procedure Select Between Dates Problem RSS

• • Reply
  

  Taonias

  Member

  351 Points

  276 Posts

  Stored Procedure Select Between Dates Problem

  Apr 30, 2012 10:38 AM|LINK

  Hello all

  I have a stored procedure that returns a number of rows depending on a datetime range.

  The db table it searches has 2 attributes (dateStart and dateEnd) for each entry.

  At the moment my stored procedure returns all the rows that dateStart > requested Date to start and dateEnd < requested Date to end

  and I need to change that so it will return all the rows where either dateStart > requested Date to start or dateEnd < requested Date

  so if a row in the db has dateStart 2/2/2012 and dateEnd 4/2/2012 and the requested dates are: 10/1/2012 and 3/2/2012 then this row will be returned

  and

  if another set of requested dates are: 10/1/2012 and 1/2/2012 then this row will not be returned

  this is my stored procedure:

  CREATE PROCEDURE [dbo].[pcOfferDetails_SelectBSearchCriteriaPagingOBSortExpresion]
  	@finalQuery [nvarchar](max),
  	@dateFrom [datetime],
  	@dateTo [datetime],
  	@PageIndex [int],
  	@PageSize [int],
  	@sortExpresion [nvarchar](max)
  AS
  SET NOCOUNT ON
  
  DECLARE @sql nvarchar(max);
  
  if (@finalQuery = '')
  	SET @sql = 'SELECT *
  	FROM (
  	   SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpresion + ')
  		AS RowNum, od.[id], od.[price], od.[isInFirstPage], od.[xtraInfo], od.[subject], od.[offerText],
  			od.[isNewProduct], od.[isImageEdited], od.[isScriptEdited], od.[withVAT], od.[dateCapture],
  			od.[posId], od.[offerProductId], od.[offerTypeId], od.[promotionToolId], od.[dateStart],
  			od.[dateEnd], od.[seasonality], od.[updateBy], od.[dateLastUpdate], od.[addedBy], od.[dateAdded]
  	   FROM OfferDetails od
  		LEFT JOIN OfferProduct op ON op.id = od.offerProductId
  		LEFT JOIN PromotionTools pt ON pt.id = od.promotionToolId
  		LEFT JOIN Channel c ON c.id = pt.channelId
  		LEFT JOIN Source s ON s.id = c.sourceId
  		LEFT JOIN Supplier su ON su.id = op.supplierId
  		LEFT JOIN PointOfSale pos ON pos.id = od.posId
  		LEFT JOIN RetailerWholeSaler rw ON rw.id = pos.retailerWholeSalerId
  		LEFT JOIN Structure st ON st.id = op.structureId
  		LEFT JOIN Brand b ON b.id = op.brandId
  	   WHERE (DATEDIFF(dd, ''' + CONVERT(VARCHAR, @dateFrom, 120) + ''', od.[dateStart]) >= 0 AND
  		DATEDIFF(dd, od.[dateEnd], ''' + CONVERT(VARCHAR, @dateTo, 120) + ''') >= 0)
  	) AS OfferDetailsPage
  	WHERE OfferDetailsPage.RowNum BETWEEN ' + CAST(@PageIndex * @PageSize + 1 AS VARCHAR(10))+ ' AND ' + CAST((@PageIndex + 1) * @PageSize AS VARCHAR(10))
  else
  	SET @sql = 'SELECT *
  	FROM (
  	   SELECT ROW_NUMBER() OVER (ORDER BY ' + @sortExpresion + ')
  		AS RowNum, od.[id], od.[price], od.[isInFirstPage], od.[xtraInfo], od.[subject], od.[offerText],
  			od.[isNewProduct], od.[isImageEdited], od.[isScriptEdited], od.[withVAT], od.[dateCapture],
  			od.[posId], od.[offerProductId], od.[offerTypeId], od.[promotionToolId], od.[dateStart],
  			od.[dateEnd], od.[seasonality], od.[updateBy], od.[dateLastUpdate], od.[addedBy], od.[dateAdded]
  	   FROM OfferDetails od
  		LEFT JOIN OfferProduct op ON op.id = od.offerProductId
  		LEFT JOIN PromotionTools pt ON pt.id = od.promotionToolId
  		LEFT JOIN Channel c ON c.id = pt.channelId
  		LEFT JOIN Source s ON s.id = c.sourceId
  		LEFT JOIN Supplier su ON su.id = op.supplierId
  		LEFT JOIN PointOfSale pos ON pos.id = od.posId
  		LEFT JOIN RetailerWholeSaler rw ON rw.id = pos.retailerWholeSalerId
  		LEFT JOIN Structure st ON st.id = op.structureId
  		LEFT JOIN Brand b ON b.id = op.brandId
  	   WHERE (' + @finalQuery + ') AND
  		(DATEDIFF(dd, ''' + CONVERT(VARCHAR, @dateFrom, 120) + ''', od.[dateStart]) >= 0 AND
  		DATEDIFF(dd, od.[dateEnd], ''' + CONVERT(VARCHAR, @dateTo, 120) + ''') >= 0)
  	) AS OfferDetailsPage
  	WHERE OfferDetailsPage.RowNum BETWEEN ' + CAST(@PageIndex * @PageSize + 1 AS VARCHAR(10))+ ' AND ' + CAST((@PageIndex + 1) * @PageSize AS VARCHAR(10))
  
  print @sql
  
  EXECUTE sp_executesql @sql;

  
  
  

  Thank you

  • Edited by Taonias on Apr 30, 2012 10:38 AM
  • Edited by Taonias on Apr 30, 2012 10:40 AM
  • Edited by Taonias on Apr 30, 2012 10:40 AM
  • Edited by Taonias on Apr 30, 2012 11:11 AM
  • Edited by Taonias on Apr 30, 2012 11:11 AM
• • Reply
  

  gimimex

  Participant

  1052 Points

  157 Posts

  Re: Stored Procedure Select Between Dates Problem

  Apr 30, 2012 01:08 PM|LINK

  Hi,

  Try something like this:

  where
      ( (@dateFrom >= od.[dateStart]) and (@dateFrom <= od.[dateEnd]) ) or
      ( (@dateTo >= od.[dateStart]) and (@dateTo <= od.[dateEnd]) )

  Hope this helps. 

  http://www.imoveisemexposicao.com.br/imoveis-em-guarulhos
  • Edited by gimimex on Apr 30, 2012 01:08 PM
• • Reply
  

  TabAlleman

  All-Star

  15571 Points

  2700 Posts

  Re: Stored Procedure Select Between Dates Problem

  Apr 30, 2012 02:01 PM|LINK

  Actually, assuming you would also want to return rows where the requested dates are entirely contained within the range in the database, all you need is this:

  WHERE dateEnd >= @dateFrom OR dateStart <= @dateTo

   

  -Tab Alleman
  • Marked as answer by Chen Yu - MSFT on May 04, 2012 09:25 AM