Removing duplicates from 'over partition by'

Question

Im using over partition by clause to calculate peoples monthly figures.

A short example of my results:

    Date           Person       Team    Daily Figure    Month To Date
    24/09/17       James         2           50            200
    24/09/17       James         2           50            200
    25/09/17       James         2           50            200
    25/09/17       James         2           50            200

I am calculating the monthly figure by partitioning the daily figure over the person and the month e.g.

    CASE
    WHEN 
    MONTH([DATE]) = MONTH(getdate())
    THEN SUM(Figure)
    OVER (PARTITION BY [Name], 
    MONTH(DATE])) 
    ELSE 0
    END AS 'Month To Date'

The main issue im having is i only want to display todays daily figure but with the whole month value. I group the figure for each person and limit to today but to be able to group by person i need to SUM the month to date figure which obviously leaves me with

    Date           Person       Team    Daily Figure    Month To Date
    25/09/17       James         2           100            800

The daily figure is correct but its obviously summing the duplicated rows which gives me an incorrect figure.

The ideal result for today would be:

     Date           Person       Team    Daily Figure    Month To Date
    25/09/17         James         2           100             200

with no duplicated rows.

Has anyone got any advice on this? To basically remove duplicated partitioned rows so i just get the correct month to date figure grouped

UPDATE:

Each row is just an individual figure for each person. Nothing is grouped so each person could have atleast 20 separate rows for figures on each day

I cannot follow what you want to do. What data are you starting with? What is the definition of each row? — Gordon Linoff, Sep 25 '17 at 11:21
posting your query or raw data will be helpful - because from your current required result I think you can achieve using a Group By instead of Window Function — Dawood Awan, Sep 25 '17 at 11:27
The problem is that at the moment i only need daily figure but also their whole month total as well. So group by doesnt work to calculate month to date figure because it only sums it for that day - windowed function seems to only option but it creates duplicated sum for every row that matches that persons name — Ryan Gadsdon, Sep 25 '17 at 11:34
Looks like basic SUM(DailyFigure) and group by everything else. Your table structure is a little bit left of center to me. You have daily data but also summary data in each row. There seems to be be some other normalization challenges here as well. — Sean Lange, Sep 25 '17 at 13:15

score 1 · Accepted Answer · answered Sep 25 '17 at 13:38

Something like this?

declare @t table (Date date, Person varchar(100), Team int,   [Daily Figure] int);
insert into @t values
    ('20170924',       'James',         2,           50),
    ('20170924',       'James',         2,           50),
    ('20170925',       'James',         2,           50),
    ('20170925',       'James',         2,           50),
    ('20170801',       'James',         2,           80),
    ('20170802',       'James',         2,           80);


select Date, Person, Team, sum([Daily Figure]) as [Daily Figure],
       sum(sum([Daily Figure])) over(partition by Person, Team, month(date)) as [month to date figure]
from @t
group by Date, Person, Team;

Removing duplicates from 'over partition by'

1 Answers1