filter by Sum without Grouping

Question

i have a resultset that i generate from a query that Looks like this:

  Select Employee, Month, (select case when Status = '---' then 0 Else 1 end) as PlaningValue      
  From   PlanningTable PT
  Where  Month >= @From Month and Month <= @ToMonth

The Result of this Looks something like this:

|Employee|  Month  | PlaningValue |
|George  | 2014-01 |            1 |
|George  | 2014-02 |            1 |
|George  | 2014-03 |            0 |
|Andrew  | 2014-01 |            0 |
|Andrew  | 2014-02 |            1 |
|Andrew  | 2014-03 |            0 |
|Howard  | 2014-01 |            1 |
|Howard  | 2014-02 |            1 |
|Howard  | 2014-03 |            1 |

Now what i want is the following:

Filter out Employee's who, over the three month period, have a total planing Value of 3, in the example above, Howard would be filtered out.

Is there a way to do this nicely or is it all just impossible to even thin ?

(Remark: Since i am going to use the Query on Reporting Services, i can't use the OVER function)

Thank you all for your help

Weird CASE syntax. Why you selecting it? – Alexander Feb 13 '14 at 13:39 — Alexander, Feb 13 '14 at 13:39

score 3 · Accepted Answer · answered Feb 13 '14 at 13:39

3

This looks to be SQL Server syntax, as such I you can use windowed functions:

WITH CTE AS
(   SELECT  Employee,
            Month,
            PlanningValue = CASE WHEN Status = '---' THEN 0 ELSE 1 END,
            Total = SUM(CASE WHEN Status = '---' THEN 0 ELSE 1 END) 
                        OVER (PARTITION BY Employee)
    FROM    PlanningTable
    WHERE   Month >= @FromDate
    AND     Month <= @ToMonth
)
SELECT  Employee, Month, PlanningValue
FROM    CTE
WHERE   Total != 3;

Simplified Example on SQL Fiddle

answered Feb 13 '14 at 13:39

GarethD

68,045
10
83
123

1

I think the OP wants `WHERE Total <> 3` – T I Feb 13 '14 at 13:42
Thanks for the Answer, the issue i am having now is that i am not able to Unionize the Results with another query, i have added a semicolon to make the above answer work before WITH, but i can't UNION the results with another query i have, is there something i can do ? – gberisha Feb 13 '14 at 13:58
and i have just realised that i can't use windowed functions in Reporting Services It says the OVER function is not supported – gberisha Feb 13 '14 at 14:02
SSRS does support the use of OVER, although the comment beneath [this answer](http://stackoverflow.com/a/9034758/1048425) would indicate SSRS query studio can cause errors with it. There is no reason you shouldn't be able to use UNION with a CTE, the CTE just has to come first, but if it is a problem just move it to a subquery - [Demo on SQL Fiddle](http://sqlfiddle.com/#!3/fd13d/8) – GarethD Feb 13 '14 at 14:29

score 0 · Answer 2 · answered Feb 13 '14 at 15:27

Try:

select pt.employee, pt.month, pt.planningvalue
from planningtable pt
   join planningtable pt2 on pt.employee = pt2.employee
   join planningtable pt3 on pt.employee = pt3.employee
   join planningtable pt4 on pt.employee = pt4.employee
where month >= @mofrom and month <= @tomonth
   and pt2.month = @tomonth
   and pt3.month in (select month from planningtable where month > @mofrom and month < @tomonth)
   and pt4.month = @mofrom
   and pt2.planningvalue + pt3.planningvalue + pt4.planningvalue <> 3

filter by Sum without Grouping

2 Answers2