|
doesn't need a loop. You could use the horrible distinct clause
select * from
(
select distinct(customerid)
from orders
where orderdate < '2004-12-01 00:00'
) j
left join
(
select distinct(customerid)
from orders
where orderdate >= '2004-12-01 00:00'
) k
on k.customerid=j.customerid
where k.customerid is null
|
|
|
|
|
SELECT Customers.CustomerID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerID
HAVING MAX(Orders.OrderDate) < '20041201' Assuming you keep your orders in an Orders table, and your customers in a Customers table, of course!
Stability. What an interesting concept. -- Chris Maunder
|
|
|
|
|
You guys are the best! thanks a lot!
But one more question, I'm using Mike's algorithm, how do you select more fields to display?
|
|
|
|
|
As long as you want to select more fields from the customers table, you should include the fields in both the select and group by clause. Like:
<br />
SELECT Customers.CustomerID, Customers.Name <br />
FROM Customers <br />
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID<br />
GROUP BY Customers.CustomerID, Customers.Name <br />
HAVING MAX(Orders.OrderDate) < '20041201'<br />
Wout Louwers
|
|
|
|
|
I did that but I want the date to display and if you group by date it shows the duplicates.
|
|
|
|
|
I 'me not sure if this will work, but you could try:
<br />
SELECT Customers.CustomerID, MAX(Orders.OrderDate)<br />
FROM Customers <br />
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID<br />
GROUP BY Customers.CustomerID<br />
HAVING MAX(Orders.OrderDate) < '20041201'<br />
Wout Louwers
|
|
|
|
|
You the man! Thanks a ton!
|
|
|
|
|
I am new to SQL and I am currently working on a creating Calendar application. I store all data in the Calendar table in my database. Essentially I am trying to get the database to email a user when a trigger date is reached.
For example, if event is on some date I want the database to email a reminder to the member of staff who manages that event is coming in x number of days in advance of the expiration date.
Please help me to do so.
Thanks Kissy
|
|
|
|
|
Hello, I am creating a database with access and I have a question involving a macro I have created. In the Begging line of the macro immediately following the Warnings on field in design view, I have a RunApp Command that executes a batch file which takes about three minutes to complete. The problem is, the rest of the queries being used in this macro depend on the new data brought in by the batch file. Can anyone suggest a way to get around this so taht the batch file can execute before I continue executing the rest of the macro? Thanks in advance for any help!
Frank Lepkowski
|
|
|
|
|
hi there,
I have a collection of record in a data table which is populated from a dataset.
I would like to replace some field values in that data table. This replacement is
temporary (I am not going to update it back to database or dataset).
Eg:
EmpID EmpName
1101 My Name
1102 Your Name
What I need to do is, I need to replace all "Emp ID" values to it's corresponding encrypted value.
Answer will be
EmpID<t> EmpName
xabc<t> My Name
ssbc<t> Your Name
Now I am iterating through individual records of my data table and updating with my required value. The iteration took little more time, when I deal with lot of records.
Is there any better way to achieve my result.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Are you using SQL server or Access or ??
can your Encryption code be ported to Transact-SQL? If so I would simply create a function that encrypts your values and run it at the server and represents it in a view.
'Cause I'm living on things that excite me, be it pastries or lobsters or love...
I'm just trying to get by, being quiet and shy, in a world full of push and shove...
Jimmy Buffett - The Wino and I know
|
|
|
|
|
Not at all possible. I can't ship the encription algoritham with T-SQL. Sybase is the database.
Sreejith Nair
[ My Articles ]
|
|
|
|
|
If you're not going to update the database with the new values, why bother putting the values in the dataset?
You could use some kind of intrinsic data object to the language you're using. Such as an array or a collection.
using System.Beer;
|
|
|
|
|
I want to use all methods which normally offered by dataset and data table. This is the main reason that, i am keeping all values in DataTable. Now do you think arraylist is good enough ?
Sreejith Nair
[ My Articles ]
|
|
|
|
|
Well you've answered your own question. If updating the dataset is too slow, then you need to do it a different way.
using System.Beer;
|
|
|
|
|
HOw we can take sql server db backup in c#.net?
I have an idea if we create a .bat file and than run it in c#.net but the problem is that i don't know what in real i have to do.
Muhammad Waqas Butt
waqasb4all@yahoo.com
|
|
|
|
|
WaqasButt wrote:
HOw we can take sql server db backup in c#.net?
The same way as any other database access using C#, by using the SqlConnection and SqlCommand objects.
You can write some C# that makes a connection to the database (so long as the user making the connection has the sysadmin, db_owner or db_backupoperator role) then you can issue the BACKUP DATABASE command with the SqlCommand.ExecuteNonQuery()
For details on the BACKUP command you may with to read the SQL Server Documentation[^]
So, your C# code will look something like this:
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand("BACKUP DATABASE...", conn);
cmd.ExecuteNonQuery();
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
Does anyone know how to restore a .bak file to a MS SQL Sever. I think the .bak file is a backup of a .mdf file but I don't have the logs or any of the associated files.
|
|
|
|
|
All Tasks -> Restore Database... -> Restore from Device (radiobutton option) -> Select Devices... -> Add (supply a filename)
After all this, you should be able to do all your "Oks" and restore as normal.
Dallas
|
|
|
|
|
I've tried that but I get some big long error about my data and log files not being found.
|
|
|
|
|
Choose the options tab and check "force restore over existing database". The3 backup filewas probably created on a different machine and the database and logfiles were located in a diufferent place (dreive/directory) on thet machine. The backuup 'remembers' the original locations, and tries to update the files in that place unless you select this option.
Why would anyone waste time arguing with an accountant about anything? Their sole function is to record what happenned, and any higher aspirations are mere delusions of grandeur. On the ladder of productive contributions they are the little rubber pads at the bottom that keep the thing from sliding out from under you. - Roger Wright
|
|
|
|
|
hi,
i wnat to know if there is a way to execute several sql statements in one transaction like sql Query Analyzer.
for example i want to have this to statements together in one sqlcommand
"delete from mytable
go
delete from mytable2"
in Query Analyzer this works but in .net it doesn't
is there any solution?
|
|
|
|
|
The "GO" is a delimiter for the Query Analyser only. You can change it to what ever you want by going to Tools-->Options... and selecting the Connection tab. There is an item called Batch Separator that you can set.
The example you gave does not put both DELETE commands in one transaction
You do not have to separate all commands into individual batches. So you can send two commands in one batch, like this
DELETE FROM mytable; DELETE FROM mytable2;
Of course, if you need to call multiple commands then the the better solution is to use stored procedures and call the stored procedure. In the query analyser:
CREATE PROCEDURE DeleteStuff
AS
BEGIN TRANSACTION
DELETE FROM mytable
DELETE FROM mytable2
COMMIT TRANSACTION
GO
Then in your .NET application:
SqlCommand cmd = new SqlCommand("DeleteStuff", myConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
Does this help?
Do you want to know more?
WDevs.com - Member's Software Directories, Blogs, FTP, Mail and Forums
|
|
|
|
|
I would like to use one connection in my windows application, and I have more than one form.
The problem is that when i drag a DataAdapter from the ToolBox to the form, it wants to create a connection with the wizard.
I don't want this to happen, and I would like some way to use the designer in order to use a DataConnection that was defined elsewhere.
I know I can create the connection to the same database in each form, but my problem with this approach is that there is no real need to create the same DataConnection again and again for no good reason.
I want only to use the designer in order to create the DataAdapter. Is there some way to do it without programming?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
