Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
AdamWest
Constraint Violating Yak Guru
360 Posts |
Posted - 2013-08-09 : 13:01:32
|
This is Crystal Reports SQL. I have to made a change and want to see it this is possible. The column IDPRT# is used here as MAX. THe user wants to exclude a few of the items which skews the average. Can i also select the IDPRT# a second time? and then in the CRystal reports I can select not =? but will this mess up the MAX line in any way?SELECT "OEIND94"."IDDOCD" AS INV_DATE, "OEIND94"."IDORD#" AS ORD_NUM, "OEIND94"."IDORDT" AS ORD_TYPE, "OEIND94"."IDPRLC" AS PROD_FAMILY, "OEIND94"."IDPR$C" AS PRICE_CODE, "OEIND94"."IDCOM#", MAX("OEIND94"."IDPRT#") AS ITEM_REF, "ICPRT1"."IARCC4" AS PROD_TYPE, "ICPRT1"."IARC11" AS PROD_CLASS, "ICPRT1"."IACMQC" AS CUM_CODE, SUM("OEIND94"."IDSHP#") AS NUM_UNITS, SUM("OEIND94"."IDSHP#" * "OEIND94"."IDCSU$") AS TOT_COST, SUM("OEIND94"."IDSHP#" * "OEIND94"."IDNTU$") AS TOT_SLS FROM "S1047N4M"."ASTDTA"."OEIND94" "OEIND94" INNER JOIN "S1047N4M"."ASTDTA"."ICPRT1" "ICPRT1" ON "OEIND94"."IDPRT#"="ICPRT1"."IAPRT#" WHERE "OEIND94"."IDCOM#"='001' AND "OEIND94"."IDDOCD" >= {?FromDate} AND "OEIND94"."IDDOCD" <= {?ToDate} AND "ICPRT1"."IARCC4"='FIN' GROUP BY "OEIND94"."IDDOCD", "OEIND94"."IDORD#", "OEIND94"."IDORDT", "OEIND94"."IDPRLC", "OEIND94"."IDPR$C", "OEIND94"."IDCOM#", "ICPRT1"."IARCC4" , "ICPRT1"."IARC11", "ICPRT1"."IACMQC" ORDER BY "OEIND94"."IDDOCD", "OEIND94"."IDORD#", "OEIND94"."IDORDT", "OEIND94"."IDPRLC", "OEIND94"."IDCOM#" |
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2013-08-19 : 04:13:38
|
You can use HAVING Clause to filter the values of "IDPRT#"HAVING MAX("OEIND94"."IDPRT#") not in <some value>MadhivananFailing to plan is Planning to fail |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-08-19 : 06:37:07
|
another way is this if you just want filter to be applied for aggregation alone and not for the entire query..MAX(CASE WHEN "OEIND94"."IDPRT#" NOT IN (some values...) THEN "OEIND94"."IDPRT#" END) AS ITEM_REF.. ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|