[thelist] SQL Statement problem

[rudy]

> I know in MS SQL Server you can't group by an alias.

hi howard

wacky, eh?

> In this case, your only solution is to use the same function
> in your GROUP BY clause.

maybe not the *only* solution -- i think a view will also work

;o)

seth, in your original query, are you sure you're happy with the default
evaluation sequence for the ANDs and ORs?  looks a little suspicious to
me...

SELECT COALESCE (DATEPART(ww, approved_date)
                , DATEPART(ww, terminate_date)) AS status_date
      , COUNT(approved_date) AS apdate
      , COUNT(terminate_date) AS tdate
   FROM applications
  WHERE company_key = 'LI'
    AND (approved_date BETWEEN '01/01/2001'
                           AND '01/01/2002')
     OR (terminate_date BETWEEN '01/01/2001'
                            AND '01/01/2002')
    AND (terminate_type = 'declined')
  GROUP BY
        status_date

seems to me that this must've been produced by some query tool, because
there really is no need to put those parentheses around simple conditions

<tip type="sql">
remove unnecessary parentheses in the WHERE clause, and insert parentheses
to avoid implicit evaluation precedence for compound conditions
</tip>

what you have is --

      WHERE condition1 AND (condition2) OR (condition3) AND (condition4)

remove the parentheses that are not required, and add the ones that reflect
the default evaluation precedence --

      WHERE ( condition1 AND condition2 ) OR ( condition3 AND condition4 )

are you sure that's what you want?


okay, now as to the view

a simple view involves just renaming columns, and does not
involve WHERE conditions --

  create view sethbaby
        (status_date, apdate, tdate)
    as
      select
        COALESCE (DATEPART(ww, approved_date)
                , DATEPART(ww, terminate_date))
      , approved_date
      , terminate_date
   FROM applications

supply the WHERE and GROUP BY clauses when using the view --

  select status_date
      , COUNT(apdate) as apdate
      , COUNT(tdate) as tdate
   FROM sethbaby
  WHERE company_key = 'LI'
    AND (approved_date BETWEEN '01/01/2001'
                           AND '01/01/2002')
     OR (terminate_date BETWEEN '01/01/2001'
                            AND '01/01/2002')
    AND (terminate_type = 'declined')
  GROUP BY
        status_date

granted, this offers very little in additional benefits over coding the
large expression in the GROUP BY clause, but there are other examples where
defining a view is a much better approach...


rudy