SQL 2005 Express - Complex Query Help - Date Range
-
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
-
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
here if you need the data between start date and end date then just pass the 2 dates (start and end). for example in your case 20091001 to 20091031. if you wish the data for current month then using getdate() fucntion get the current month and date and treat as startdate and end enddate. for example SELECT DATEPART(d,getdate()) SELECT DATEPART(m,getdate()) you will get the current month and date. treat as your start date and end date. in your query then put the logic just like 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) and DateCreated beetween @Startdate and @Enddate hope this is fine for you.
Regards Keyur Satyadev
-
here if you need the data between start date and end date then just pass the 2 dates (start and end). for example in your case 20091001 to 20091031. if you wish the data for current month then using getdate() fucntion get the current month and date and treat as startdate and end enddate. for example SELECT DATEPART(d,getdate()) SELECT DATEPART(m,getdate()) you will get the current month and date. treat as your start date and end date. in your query then put the logic just like 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) and DateCreated beetween @Startdate and @Enddate hope this is fine for you.
Regards Keyur Satyadev
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 @EndDateSo 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,
-
here if you need the data between start date and end date then just pass the 2 dates (start and end). for example in your case 20091001 to 20091031. if you wish the data for current month then using getdate() fucntion get the current month and date and treat as startdate and end enddate. for example SELECT DATEPART(d,getdate()) SELECT DATEPART(m,getdate()) you will get the current month and date. treat as your start date and end date. in your query then put the logic just like 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) and DateCreated beetween @Startdate and @Enddate hope this is fine for you.
Regards Keyur Satyadev
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.NameThis returns a list of all invoice payments that have amounts due and thier amount that is due. The first query gets all invoice charges
-
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.NameThis returns a list of all invoice payments that have amounts due and thier amount that is due. The first query gets all invoice charges
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