How to Replicate 'Having' Clause in Cognos RS

[buddtholomew]

Results Set Expected:
SystemRef Count of Rows
100982 8

Select A.SystemRef,COUNT(*) as Total
from
(Select STY.SystemRef,SUM(Sales) as Sales, STY.ContractDate,P.PartyStartDate from
d_Party P INNER JOIN f_OrderHeader OH ON P.PartySID = OH.PartySID
INNER JOIN d_Stylist STY on P.StylistSID = STY.StylistSID
where STY.SystemRef = '100982'
Group by STY.SystemRef,PartyEventID,STY.ContractDate,P.PartyStartDate
Having SUM(Sales) >=500 and P.PartyStartDate between STY.ContractDate and DATEADD(DAY,99,STY.ContractDate))A
Group by SystemRef

100982   1472.50   2009-02-03 00:00:00.000   2009-02-28 10:00:00.000 100982   660.60   2009-02-03 00:00:00.000   2009-03-12 19:00:00.000 100982   665.40   2009-02-03 00:00:00.000   2009-03-21 15:00:00.000 100982   1545.00   2009-02-03 00:00:00.000   2009-03-27 19:00:00.000 100982   656.00   2009-02-03 00:00:00.000   2009-04-02 19:30:00.000 100982   1826.00   2009-02-03 00:00:00.000   2009-04-03 19:00:00.000 100982   704.00   2009-02-03 00:00:00.000   2009-04-25 11:00:00.000 100982   861.00   2009-02-03 00:00:00.000   2009-04-18 08:00:00.000

I am unable to replicate the above in Cognos RS. I get 6 rows for the 1472.50 total above instead of 1 as the Sales are from multiple orders (6 in this case). This affects the total as Cognos returns 101 and the SQL returns 8.

I appreciate the help.

[wyconian]

Hi try adding this as a summary filter with the after aggregation button ticked

[buddtholomew]

Hi try adding this as a summary filter with the after aggregation button ticked

Thanks for the suggestion but there are other fields in the list that cannot be filtered in this fashion. Does the below syntax appear accurate?

If(
[sales] >=500 and [startdate] between [contractdate] and _add_days([contractdate],99)) then (1) else (0)

This correctly returns a value of 1 for each PartyEventID. The problem surfaces when attempting to count the number of 1s. I should get 8 as there are eight PartyEventIDs with a value of 1. Unfortunately, I get a 1 for each Order within a Party for a total of 101.

[wyconian]

Hi

Looks OK (exceot for the missing bracket which I guess is just a typo).  I think you're counting the 1s and th3 0s.  try summing them