Conditional Where

• ringovski

  Hall of Fame

  Points: 3521

  More actions

  June 29, 2011 at 12:19 am

  #99738

  I am trying to get records for the previous day which is fine apart from running it on Monday which will need Fridays records.

  The first part of the where with the case statements is not working?

  SELECT *

  FROM invoice_head INNER JOIN

  invoice_lines ON invoice_head.Invoice_id = invoice_lines.Invoice_id INNER JOIN

  companies ON invoice_head.company_id = companies.company_id AND invoice_head.Optional_1 = companies.companyname

  WHERE (invoice_line.ApproveDate >= (CASE WHEN datepart(dw, getdate())=2 THEN (getdate()-3) ELSE (getdate()-1)))

  AND (invoice_lines.ApproveDate < DATEADD(day, DATEDIFF(day, 0,GETDATE()), 0))

  AND (companies.company_id = 44)

  ORDER BY companies.companyname

• Jayanth_Kurup

  SSC-Insane

  Points: 22967

  More actions

  June 29, 2011 at 12:57 pm

  #1346156

  CASE input_expression

  WHEN when_expression THEN result_expression

  [ ...n ]

  [

  ELSE else_result_expression

  ]

  END

  where is the end

  Jayanth Kurup[/url]