Sum values between two dates

  • I need to sum all a customers debits and credits from the day they became a customer to todays date minus 10 days but I also need to add any payments received in that 10 days.

    Here is the code:

    select

    RTRIM(LTRIM(cast(contact.id as varchar(20))))+'--'+cast(salescontract.contractid as varchar(20)),

    contact.fNAME +' '+contact.lNAME,sum(mdtrans.amount),salescontract.resortid

    from contact

    join salescontract

    on contact.id = salescontract.ownerid

    join mdtrans

    on salescontract.id = mdtrans.contractid

    where salescontract.canceldate is null and DATEDIFF("dd",mdtrans.transdate,GETDATE()) > 10

    group by contact.FNAME,salescontract.id,contact.LNAME, contact.id,salescontract.canceldate, salescontract.contractid,salescontract.resortid

    as you may have quickly figured out... the payments received in the last 10 days isnt functional here.... cant make it work. Any advice would be so cool. Thanks

    Bob Fernandez

    "Beer is proof that God loves us and wants us to be happy."

    Benjamin Franklin.

  • And how can we tell the difference between debits, credits and payments?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • whether its positive or negative ........

  • How about this:

    [font="Courier New"]SELECT

       RTRIM(LTRIM(CAST(CONTACT.ID AS VARCHAR(20))))+'--'+CAST(SALESCONTRACT.CONTRACTID AS VARCHAR(20)),

       CONTACT.FNAME +' '+CONTACT.LNAME,

       SUM(CASE

               WHEN MDTRANS.TRANSDATE > DATEADD(DAY, -10, GETDATE()) AND MDTRANS.AMOUNT > 0, MDTRANS.AMOUNT

               WHEN MDTRANS.TRANSDATE <= DATEADD(DAY, -10, GETDATE()), MDTRANS.AMOUNT

               ELSE 0

           END ),

       SALESCONTRACT.RESORTID

    FROM

       CONTACT JOIN

       SALESCONTRACT ON

           CONTACT.ID = SALESCONTRACT.OWNERID JOIN

       MDTRANS ON

           SALESCONTRACT.ID = MDTRANS.CONTRACTID

    GROUP BY

       CONTACT.FNAME,

       SALESCONTRACT.ID,

       CONTACT.LNAME,

       CONTACT.ID,

       SALESCONTRACT.CANCELDATE,

       SALESCONTRACT.CONTRACTID,

       SALESCONTRACT.RESORTID

    [/font]

    I did not have any data to test this on but I think it will work for you.

  • So, Bob! Just curious... did Jack's code work or what?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply