sql query

• in2nat

  SSC Veteran

  Points: 268

  More actions

  July 12, 2005 at 9:08 pm

  #167726

  Have asked previous but not helped...so thought I would give it another 

  go.  Using Reporting Services and need to run this report urgently.

  I have a query with a number of inner joins and left outer joins.  The 

  where clause has numerous "and" clauses and one of these I need to be where a

  endate is greater than the current date less 6 months (ie anything in the

  last six months).

  where (a.DeletedDate IS NULL) and (stli.id = 1) and (fa.regionareaid 

  between 1 and 9)

  and (rpn.enddate > '01/01/2005') or (rpn.enddate is null)

  at present as you can see I have stipulated the date as 01/01/2005 however 

  i need the report to run every week (schedule will be set up for running and

  emailing) and I need the report to have the correct "and" in place.  Any 

  help would be greatly appreciated.  Please note that the rpn.enedate can either have a date or

  be null.

  hopefully this is a bit more information.

• philcart

  SSC-Forever

  Points: 47794

  More actions

  July 12, 2005 at 9:24 pm

  #573757

  Are you specifying the query text in the report designer, or are you calling a stored procedure?

  Iis the report running for the previous week, or just the past seven days? Does it matter if it runs on a Monday, Tuesday, etc...

  For something a simple a showing the last seven days you can use the DATEADD function.

  EG: rpn.enddate > DATEADD(dd, -7, GetDate())

   

  --------------------
  Colt 45 - the original point and click interface