May 2, 2008 at 10:14 am
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.
May 2, 2008 at 1:11 pm
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]
May 2, 2008 at 1:42 pm
whether its positive or negative ........
May 2, 2008 at 2:12 pm
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
June 24, 2008 at 7:25 pm
So, Bob! Just curious... did Jack's code work or what?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply