Expresion formula required
I have data fields Date_Req Date_Del Del_Qty What I am wanting to do is write an expression so that if the Date_Del is either more than 3 days eairlier than the Date_Req or more that 3 dates later than the Date_req it would result zero if not it would result Del_Qty Can anyone help
November 12th, 2010 9:55am

For performance reasons this should be written in your sql dataset query rather than a report layer expression, calculations are what the sql engine is designed for. something like: SELECT Date_Req ,Date_Del ,Del_Qty ,New_Field = CASE WHEN ABS(datediff(d, Date_Req, Date_Del)) > 3 THEN 0 ELSE DelQty END FROM MyTable My Blog "Karl Beran's BI Mumble"
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 10:12am

Many thanks. The reason I was trying to avoid doing the calculation in SQL is that this criteria differs for different customers and therefore I was going to build this into the formula
November 12th, 2010 10:18am

Should still be done in SQL, if customer is a parameter in the report pass it through to the CASE statement.My Blog "Karl Beran's BI Mumble"
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 10:28am

so say the customer data field is called cust_no & cust_no 999 is based on the criteria already mentioned and cust_no 888 is 10 days earlier and zero days later how would I use the case?
November 12th, 2010 10:38am

SELECT Date_Req ,Date_Del ,Del_Qty ,New_Field = CASE WHEN cust_no = 999 AND ABS(datediff(d, Date_Req, Date_Del)) > 3 THEN 0 WHEN cust_no = 999 AND ABS(datediff(d, Date_Req, Date_Del)) > 10 THEN 0 ELSE DelQty END FROM MyTable or if you had the values 3 and 10 in a customer table as follows cust_no days 999 3 888 10 then you could do the following: SELECT Date_Req ,Date_Del ,Del_Qty ,New_Field = CASE WHEN ABS(datediff(d, Date_Req, Date_Del)) > c.days THEN 0 ELSE DelQty END FROM MyTable t INNER JOIN MyCustomers c ON t.cust_no = c.cust_no My Blog "Karl Beran's BI Mumble"
Free Windows Admin Tool Kit Click here and download it now
November 12th, 2010 11:29am

Thanks
November 12th, 2010 11:32am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics