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.
Hi try adding this as a summary filter with the after aggregation button ticked
Quote from: wyconian on 12 Mar 2012 04:26:30 AM
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.