CODEWITHSUNDEEP
sqlsql-serverduplicatespartition-by
Ryan Gadsdon
Ryan Gadsdon

Reputation: 2378

Removing duplicates from 'over partition by'

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

Upvotes: 0

Views: 2295

Answers (1)

sepupic
sepupic

Reputation: 8687

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;

Upvotes: 1

Related Questions