alright so here is my edit, that works. (Had an idea) Is it a good idea?

SELECT InvoicePayment.DateCreated AS InvoicePaymentDate,
InvoicePayment.InvoiceID,
InvoicePayment.Amount,
Customer.Name,
Invoice.CustomerID,
Invoice.InvoiceNumber
FROM (Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment INNER JOIN
CustomerAccountPaymentDetail ON CustomerAccountPayment.ID = CustomerAccountPaymentDetail.CustomerAccountPaymentID RIGHT OUTER JOIN
InvoicePayment INNER JOIN
Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID ON CustomerAccountPaymentDetail.InvoicePaymentID = InvoicePayment.ID
WHERE (Customer.ID = @CustomerID)
AND (CustomerAccountPaymentDetail.ID IS NULL)
AND (Invoice.Void = 'False')
AND (PaymentMethod.IsCredit = 'True')
and (InvoicePayment.DateCreated <= @EndDate)
union
SELECT InvoicePayment.DateCreated AS InvoicePaymentDate,
InvoicePayment.InvoiceID,
InvoicePayment.Amount - SUM(case CustomerAccountPayment.Void
when 'True' then 0
when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
end) AS Amount,
Customer.Name,
Invoice.CustomerID,
Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID INNER JOIN
PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN
(Select * from CustomerAccountPayment where CustomerID = @CustomerID and EntryDate <=@EndDate) as CustomerAccountPayment ON CustomerAccountPaymentDetail.CustomerAccountPaymentID = CustomerAccountPayment.ID
WHERE (Invoice.Void = 'False')
AND (PaymentMethod.IsCredit = 'True')
and Invoice.CustomerID = @CustomerID
and InvoicePayment.DateCreated <= @EndDate
GROUP BY InvoicePayment.DateCreated, InvoicePayment.InvoiceID, Invoice.InvoiceNumber, InvoicePayment.Amount, InvoicePayment.DateCreated, Customer.Name,
Invoice.CustomerID
HAVING (SUM(case CustomerAccountPayment.Void
when 'True' then

I thought i had it now i know i don't. What i need to do is get all outstanding invoice payments and thier amounts owing for a end date. So I want to know the status of the account for October 31, 2009. Here is how i am able to get it for what ever is in the database, now i just need to edit it for an end date.

SELECT InvoicePayment.DateCreated AS InvoicePaymentDate,
InvoicePayment.InvoiceID,
InvoicePayment.Amount,
Customer.Name,
Invoice.CustomerID,
Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID LEFT OUTER JOIN
CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID
WHERE Customer.ID = @CustomerID
and (CustomerAccountPaymentDetail.ID IS NULL)
AND (Invoice.Void = 'False')
AND (PaymentMethod.IsCredit = 'True')
union
SELECT InvoicePayment.DateCreated AS InvoicePaymentDate,
InvoicePayment.InvoiceID,
InvoicePayment.Amount - SUM(case CustomerAccountPayment.Void
when 'True' then 0
when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
end) AS Amount,
Customer.Name,
Invoice.CustomerID,
Invoice.InvoiceNumber
FROM InvoicePayment INNER JOIN
Invoice ON InvoicePayment.InvoiceID = Invoice.ID INNER JOIN
Customer ON Invoice.CustomerID = Customer.ID INNER JOIN
CustomerAccountPaymentDetail ON InvoicePayment.ID = CustomerAccountPaymentDetail.InvoicePaymentID INNER JOIN
PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN
CustomerAccountPayment ON CustomerAccountPaymentDetail.CustomerAccountPaymentID = CustomerAccountPayment.ID
WHERE (Invoice.Void = 'False')
AND (PaymentMethod.IsCredit = 'True')
and Invoice.CustomerID = @CustomerID
GROUP BY InvoicePayment.DateCreated, InvoicePayment.InvoiceID, Invoice.InvoiceNumber, InvoicePayment.Amount, InvoicePayment.DateCreated, Customer.Name,
Invoice.CustomerID
HAVING (SUM(case CustomerAccountPayment.Void
when 'True' then 0
when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
end) <> InvoicePayment.Amount)
ORDER BY Customer.Name

This returns a list of all invoice payments that have amounts due and thier amount that is due. The first query gets all invoice charges

Yes for charges that is fine to get what has been charged. And I can do this for the customer payments on their account.

SELECT sum(CustomerAccountPayment.Amount + CustomerAccountPayment.FloatingAmount) as TotalPaid
FROM CustomerAccountPayment Inner JOIN
CustomerAccountPaymentDetail on CustomerAccountPayment.Id = CustomerAccountPaymentDetail.CustomerAccountPaymentID
Where CustomerAccountPayment.Void = 0
and CustomerAccountPayment.EntryDate BETWEEN @StartDate and @EndDate

So now i have a total for a date range. Which is fine. But now i need to re-create the account. and show a ...i think i just figured it out. I have been over complicating this thing for too long. Thanks,

Review the "Math" library. There is a "Floor" and "Ceiling" function. Ceiling rounds 1.1 to 2 and Floor makes 1.9 to 1. You will need to make sure a and b are "double" but will be fine.

double a = 123456.789;
double b = 7;
double c = Math.Floor(a/b);

Given below is is my implementation, where should i add [Browsable(false)] or [Browsable(true)]

PropertyGrid pgGrid = new PropertyGrid();
pgGrid.CommandsVisibleIfAvailable = true;
pgGrid.Height = 400;
pgGrid.Width = 250;
pgGrid.Location = new Point(this.Width - (pgGrid.Width + 10), 0);
pgGrid.Dock = DockStyle.Fill;
this.Controls.Add(pgGrid);
pgGrid.Visible = true;
pgGrid.SelectedObject = label1; <--- here!

Have you tried casting the object? If the object is different everytime and you can't edit the objects to use an interface with "allowEdit" then maybe you can try using "reflection" methods to get access. Look up those things then ask for specific help.

You could try xml also (sql 2005 and later i believe), its serializable and shouldn't be to difficult, but a table is the best way like the first response.

First off, I inherited this database and its database structure, Second I am mainly a C# programmer and my database query skills are lacking and could use improvement I know that. I have avoided this bugger for a too long so now I ask the community for help. I'll break this down to 3 parts, 1: What my DB looks like (the important stuff), 2. What I want to accomplish, 3: Where I am at. 1: Tables & relations (I can supply you with a create script and sample data, only needed fields are seen) TABLE:CustomerAccountPayment ID, PK, int CusotmerID, FK, int EntryDate, DateTimeAmount, numeric(18,2) Void, bit FloatingAmount, numeric(18,2) (Positive Only, means they overpaid for some reason) Amount = sum(CustomerAcountPaymentDetail.Amount) Total = Amount + FloatingAmount TABLE:CustomerAccountPaymentDetail ID, PK, int CustomerAccountPaymentID, FK, int (CusotmerAccountPayment.ID) InvoicePaymentID, FK, int (InvoicePayment.ID) Amount, numeric(18,2) IsFloat, bit (Is true when the paymentdetail is covered under the customeraccountpayment.FloatingAmount) TABLE:InvoicePayment ID, PK, int InvoiceID, FK, int PaymentMethodID, FK, int (PaymentMethod.ID) Amount, numeric(18,2) DateCreated, datetime TABLE: PaymentMethod ID, PK, int Name, nvarchar(50) IsCredit, bit (Identifies it is a credit charge type to account when set to 'true') 2: What i need I have been developing a report that is a Customer account balance sheet, it shows thier current customer account balance (sum(InvoicePayment.Amount) - sum(CustomerAcocuntPayment.Total) - and what they have paid (CustomerAccountPayment) for a date range, and the age of outstanding the charges (InvoicePayments). I need to retreive thier balance before the StartDate. I need to retreive What they have charged and paid between the start and enddate. I need to retreive the remainder, after EndDate. This is all for a particular customer and the PaymentMethod.IsCredit = true. That being true means it was charged to their "Account". 3:What i got. To determine thier current balance i do these 2 queries. TotalCharged - TotalPaid. TotalCharged

SELECT SUM(InvoicePayment.Amount) AS TotalCharged
FROM InvoicePayment INNER JOIN
PaymentMethod ON InvoicePayment.PaymentMethodID = PaymentMethod.ID INNER JOIN
Invoice ON InvoicePayment.InvoiceID = Invoice.ID
WHERE (PaymentMethod.IsCredit = 1)
AND (Invoice.Void = 0)
AND (Invoice.CustomerID = @CustomerID)

TotalPaid

SELE

Hey, long time reader first time poster. I am finally at a point in my software development to start worrying about business objects and a great business layer. My projects are getting larger and more complex by the day and i am finding that my simple DTO objects are not going to be able to cut it and they will have to just be on my dumb objects. So what i am getting at is what interfaces i should consider when i start building my new objects? I have done IEditable and really like that and am sort of curious on what other interfaces i should build into my data bound objects. (INotifyPropertyChange, ITrackingChanges, etc..). I deal mainly on smartclients with a service (WCF) right now but will be moving a bit toward web based apps since that is what clients are asking for now and still utilize my services. (Hope that extra info helps you help me) Any info appreciated peers, Jordon P.S. Also I will be creating my own typed lists to hold these objects and a business layer mapper to convert my DTOs to the smart business object. So any information on what has worked/not worked for you would be great or books that i should have on my shelf.

Agreed, You can always swap out a control but your underlying data mechanism is usually not going to be changing. Why does it even matter doesn't everyone load everything async now?

I am guessing WPF is not on your "TODO" list either since Microsoft pushes binding on that majorly.

agreed. Actually just got back from a TechEd conference where they discussed just that scenario. But they did not go over the DataBind() so not sure if they virtualize because if your list is more than what is displayed then that will be a big impact. I would suggest doing 2 bind scenarios. One large list (at least 10x the visual rows) and a small list (the number of visual rows). *Note: I am not a asp.net developer so not sure about how controls work on a asp.net page or even if there is a difference.

I can vouch for this reporting tool. It is completely customizable, actually built a TIFF reader from their printing system that comes with it. Just so i could take advantage of their PDF conversion and Emailing capabilities. It can go simple as dragging a table on and binding it to drawing everything through code. There help is great and fast, cost is negligent considering how many hours they have saved me because i was able to ask a question and get response next day. You can also purchase their source code as a option if you want to. Oh and creating a custom report (like a mailing letter) for the end user to do took me half a day to implement, customers love it when you give them power to re-size text and add logos, etc... Jordon Kraft

that's what i got, wish i did't read comments first, now i must go google it...

Oh yeah i second that!! DO it.

Baconbutty wrote:

give us all more leisure time.

Good point,I believe that that hasn't occurred yet. Maybe if everything becomes web based and we can access it from anywhere it will free up more of our time. That makes sense right?

You old guys are depressing... I "wanted" to excel to a architect status but now i think I'll just keep updating code generators with the latest technology and run that for 15 min and bill for 6 hours. Then i won't have to come onto a post about great jobs and depress the new guys with how empty their future will be. Jordon.

"Wake up" check "eat" check if (DayofWeek.IsMorning) "program" check "eat" check" if (DayOfWeek.IsAfterNoon) "program" check "eat" check "hang out with friends family, enjoy anything not technology for the next 4 hours" check "go to sleep" check if (DayOfWeek) "dream about programming" check Going through your list i almost thought i was in danger, thank god for my daily 4h break.

Well I was at a crappy no where job last year August 07. Until the company started to lose money and they felt I was a added bonus and not needed (15 min before end of day). Which was very true i was supposed to do a software program for them turned into IT work with about 2 months of software development in 2 years... Anyways they paid me to read books and learn new technologies for 2 years which was nice and they gave me a 1 month severance when I left which was nice of them. Now I work for a good up and coming software company that realizes the importance of constant growth, coding standards, documentation and that things do take time (No real stress here). Couldn't be happier, went to palm springs last year for a Microsoft conference and i live in Canada. So hang in there, maybe they will fire you form your crappy job and you can get a nice severance and a great new job. Jordon.

Couldn't agree more, all our grids load based on filtering, especially since it seems we are making more and more mobile applications where the user needs to be connected all the time. Air-Card connections aren't that steady so we need to be able to send small amounts of data at a time so the ui is responsive and not a "Not -Responding" screen. Man i hate that.