Home Forums SQL Server 2014 Development - SQL Server 2014 What is the best way to SELECT SUM while using PARTITION BY and WHERE and GROUP BY? RE: What is the best way to SELECT SUM while using PARTITION BY and WHERE and GROUP BY?

  • Firewolf, it may seem frustrating to ask a question and get sent a link to an article about asking questions, but there is a reason for it. We don't need to READ your input data, we need a script that creates and populates the table(s) so that we can work with it and test with it. Like this:

    CREATE TABLE #SampleData

    (SalesRep varchar(20), Company varchar(20), TransactionDate date

    , PYear smallint, Pmonth tinyint, NumberEmployees int

    , InceptionDate date, TermDate date)

    INSERT INTO #SampleData

    VALUES

    ('Allen','Acme','12/15/2015','2015','12','246','1/1/2014','1/21/2016')

    ,('Allen','Acme','11/3/2015','2015','11','246','1/1/2014','1/21/2016')

    etc

    The only thing we need to read is what you would expect the results to be from the sample data you provide.

    In the time it took me to write that script above, I could have been working on solving your problem. I promise that if you will take the time to do a script like that with all your sample data, more people will jump in to help you out and offer you TESTED SOLUTIONS. You get a proven answer faster. It's really worth your while to help us help you. Now if you will just finish out that values clause with the rest of your data, we'll all get to work. 😉

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills