|
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 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.
|
|
|
|
|
Which is best datatype used in sqlserver2008...to store IList<string> C#.
|
|
|
|
|
|
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.
|
|
|
|
|
I'd like to be able to see if credentials (server/instance, username/password or win authentication) specified by an administrator are actually able to connect to a database at my application installation time. I'm using WISE if that matters at all. How can I go about accomplishing this? Can it be done with something like osql.exe??
|
|
|
|
|
Yes, you can test your credentials. Execute a command like select count(*) from myTable and redirect the output to a file, then parse the file for the expected results and if there is no error you have validated that your username/password combination is correct.
Do this through osql.
|
|
|
|