|
hvgyufg28fh38tyr78hf wrote: Could Some one tell me , is this results are right for this search?
I'm not sure, but the documentation says that punctuation is ignored;
Punctuation is ignored. I guess that you're searching for two separate terms in your query. Look through your resultset; there's 4 results that got either term.
The source for this answer is here[^], under the heading "simple_term".
Merry Christmas 
|
|
|
|
|
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
SELECT (sum(CustomerAccountPayment.Amount) + Sum(FloatingAmount)) as TotalPaid
FROM CustomerAccountPayment
WHERE CustomerID = @CustomerID
AND Void = 0
Now the problem is that I need to make a customer sheet for October 2009. StartDate = 20091001, EndDate = 20091031.
How do I exclude InvoicePayments (these are really charges on invocies..bad name) that are covered under a customer payment made after October 2009 but include the invoice charges for october only? Meaning there is a relation between a invoicepayment and a customerpayment made in november (there could even be a voided one CustomerAccountPayment.Void = 'True')
How do I get the balance previous to October 2009?
I just can't seem to get the proper results with all my test queries (Which are numerous..) I am hoping to get a new start point for these queries from the community... I think i am missing a technique i am jsut not aware of.
Thanks,
Jordon.
|
|
|
|
|
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 @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,
|
|
|
|
|
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 that don't have a customer payment for the invoice charge, that is the easy one.
The second query gets all the invoices that have a partial payment or an overpayment (could happen with old system, not with mine...but yeah they exist).
My head hurts I don't know how to filter this beast for an end date. I guess i need to run the query on customer payment/customerpaymentdetails that are already filtered by the end date. And then run this query against that, accept i don't know how, i think that is the issue, my lack of sql experience.
Jordon.
|
|
|
|
|
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 0
when 'False' then ROUND(CustomerAccountPaymentDetail.Amount, 2)
end) <> InvoicePayment.Amount)
|
|
|
|
|
Hello everyone,
I am planning on developing an accounting application, and while planning what language to use etc... I came started to think about databases, and my question is here. I need a database that can be distributed with a setup file but is still powerful enough to handle a large amount of data and multiple users accessing the database at the same time but also offers good security which is also important, so can anyone recommend me a good database? and if it is open source its much better. I was maybe thinking of using MS SQLSERVER EXPRESS 2005.
Thank you very much everyone really appreciate any help.
Regards,
Christian Pace
|
|
|
|
|
There are limitations on SQL Server Express. Check it out to see if your "large amount of data" will exceed the limits. You can consider MySQL. I have been using MySQL in my projects for a few years. I am happy with it. It's reliable, powerful, and easy to use.
|
|
|
|
|
loyal ginger wrote: There are limitations on SQL Server Express.
Too true. Check others before Express. I can only go by my experience and so have no answer as to which to pick but do not pick Access. I have nightmares over trying fix a migration from Access that did things like make zip code a float.
|
|
|
|
|
Yes but can i somehow incorporate MYSQL into my installation?
|
|
|
|
|
You may want to build into your design the flexibility of supporting multiple databases, like Oracle, MS-SQL, MySQL, etc. This way let your customer determine which database he/she will deploy your application. Do this by building a Data Access Layer that implements all of the interaction with the database in one location.
Check this article out
Implementing a Provider Independent Data Access Layer in .NET[^]
|
|
|
|
|
Yes this is really a good idea but i want to ask you one thing. This way you are "saying" that has knowledge in IT and knows how deploy a database and maintain it etc. What I would like to implement in my application is a database that is installed automatically in the system when the user installs the application so the user doesn't need to have any IT know how.
Do you understand what I am saying? and thank you for your post anyways I agree with it and its a really good idea
|
|
|
|
|
Hi All,
I am trying to do some query in my sqlite database. I wanted to get average of some number, but could not do it.
if you please can, write a sql statement that get average of some number.
"SELECT AVE(w)FROM x WHERE y = z ";
It should return the ave of all w.
thanks in advance
|
|
|
|
|
AVE is wrong. You should use AVG.
|
|
|
|
|
Hi dxlee, it is really a silly mistake. I meant to do avg not ave and I could not figure it out what was wrong.
thanks a lot.
|
|
|
|
|
I am glad I could help. I like sqlite. My electronic document manager uses sqlite and it is much faster than my previous version (which used Microsoft Access).
|
|
|
|
|
Hi everyone,
this question may seem dumb, so sorry beforehand - I'm just starting to learn SQL 
I want users to be have the address book (set of name/e-mail pairs) in my web application. To do this, I've created a db with 2 tables:
CREATE TABLE `users`
(
`id` integer (12) NOT NULL AUTO_INCREMENT,
`name` varchar (50) UNIQUE NOT NULL,
`email` varchar (100) NOT NULL,
`password` varchar (50) NOT NULL,
PRIMARY KEY (`id`)
)
CHARACTER SET utf8 COLLATE utf8_general_ci;
CREATE TABLE `addresses`
(
`name` varchar (50) NOT NULL,
`email` varchar (100) NOT NULL,
`userid` integer (12) NOT NULL
)
CHARACTER SET utf8 COLLATE utf8_general_ci;
First question: is this a good db structure?
Second question: what SQL query (join?) I must perform for user with name "name" to retrieve all his address records?
Thank you in advance.
Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).
|
|
|
|
|
Dmitry Khudorozhkov wrote: First question: is this a good db structure?
No, you need a primary key on the address table AddressID INT Auto increment. You also need a foreign key between User and address. This means you need to put the userID (currently the ID field on the user table) on the address table.
Then to get the address for a user something like
Select *
From Users
inner join Addresses on Addresses.UserID = User.ID
where Users.ID = @IDno
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks a lot Mycroft.
Listen up! "Teamwork" means staying out of my way! (Seifer, Final Fantasy 8).
|
|
|
|
|
What is a good query to execute to see if the account you are logged in as has sysadmin rights?
modified on Tuesday, December 22, 2009 1:48 PM
|
|
|
|
|
Are you talking about South Africa or South America?
CQ de W5ALT
Walt Fair, Jr., P. E.
Comport Computing
Specializing in Technical Engineering Software
|
|
|
|
|
Modified original message.
|
|
|
|
|
Try dropping the databases. If you succeed it means that you have more permission then you need.
|
|
|
|
|
|
Found this handy function IS_SRVROLEMEMBER ( 'role' [ , 'login' ] ). It will return either or a 1 or 0.
ex. SELECT IS_SRVROLEMEMBER( 'sysadmin', 'user_name' )
Problem solved.
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
