Posted by Ed Murphy on 01/21/07 02:46

MC wrote:

> Perhaps I'm missing something here, but T-SQL doesnt support IIF function.
> You should use CASE. Something like:
>
> SELECT c.stock_cat_name, a.stock_code, c.description, a.date,
> CASE WHEN MID(a.type,2,1)="I" THEN SUM(a.quantity) ELSE 0 END AS iqty,
> CASE WHEN MID(a.type,2,1)="O" THEN SUM(a.quantity) ELSE 0 END AS oqty
> FROM stock_tran AS a LEFT JOIN stock AS c ON c.stock_code=a.stock_code
> WHERE a.date>=[from_date] And a.date<=[to_date]

You can write this as
WHERE a.date BETWEEN [from_date] AND [to_date]

> GROUP BY c.stock_cat_name, a.stock_code, c.description, a.date,
> MID(a.type,2,1)
> ORDER BY c.stock_cat_name, a.stock_code, a.date, MID(a.type,2,1);

Did you mean to omit c.description from the ORDER BY?

[Back to original message]