Help on query needed

• v_krishnan

  Say Hey Kid

  Points: 676

  More actions

  August 6, 2003 at 12:23 am

  #81364

  Hi,

  I have a table ORDERS which has a field called ORDERDATE. This table contains records of when an order was placed and what item (ITEMID) was ordered. Now I want to execute a query which gives me the orders placed on a particular item in the user-specified time period. If there are no orders placed for that item in given time period, then I want to show when was the last order placed on this item.

  How can I make a query which gives me the above result?

  Thanks,

  Krishnan

• mccork

  SSC-Insane

  Points: 22288

  More actions

  August 6, 2003 at 1:35 am

  #468754

  Try the following code. I tested it out in Northwind (hence the use of the OrderId column). To test the 2 scenarios, run as is, then change @latest to '1998-05-03' to test out the scenario when nothing falls within the range.

   
  
  DECLARE @latest DATETIME, @earliest DATETIME
  
  SET @latest = '1998-05-05'
  
  SET @earliest = '1998-05-02'
  
  
  
  SELECT * FROM orders
  
  WHERE OrderDate <= @latest
  
  AND (OrderDate >= @earliest OR 
  
     OrderID = (SELECT TOP 1 OrderId FROM orders WHERE OrderDate <= @latest ORDER BY orderdate DESC))
  
  ORDER BY orderdate DESC

  Cheers,

  - Mark

  
  Cheers,
  - Mark

• brokenrulz

  SSCrazy

  Points: 2261

  More actions

  August 7, 2003 at 3:13 am

  #468941

  Try something like this, The only problem is that we will get 2 recordset wjere the no data is returned for the date passed..

  select productid,orderdate from invoices where orderdate=@orderdate and productid=@productid

  if @@rowcount=0

  select top 1 productid,orderdate from invoices where productid=@productid order by orderdate desc

  Happy database coding

  
  He who knows others is learned but the wise one is one who knows himself.

• DAVNovak

  Hall of Fame

  Points: 3120

  More actions

  August 7, 2003 at 9:38 am

  #469019

  DECLARE @vcItem varchar(50), @dtStart datetime, @dtEnd datetime

  SET @vcItem = <Item to Search For>

  SET @dtStart = <Start DateTime>

  SET @dtEnd = <End DateTime>

  IF EXISTS (SELECT NULL FROM Orders WHERE Item = @vcITem AND OrderDate BETWEEN @dtStart AND @dtEnd)

  SELECT * FROM Orders WHERE Item = @vcITem AND OrderDate BETWEEN @dtStart AND @dtEnd

  ELSE

  SELECT TOP 1 * FROM Orders WHERE Item = @vcItem ORDER BY OrderDate DESC