|
Well, I’ve posted my question on two message boards and received several suggestions. First of all Thanks for all your feedback and help!!
Below are the two approaches that I’ve received and I’ve tweaked them to get the result set I was looking for. Table one includes everyone on our payroll including terminated employees. Table two is our health insurance table which includes retirees. The emp number will match especially with the retirees. The problem also comes into play when you have retirees come back to work on a contractual basis. Somedays I wish they would just go golfing. 
Hopefully my explanation above helps in understanding the data I’m trying to select. Both of the selects below work where we include all the active folks on our payroll (table1), plus all the active health insurance participants (table2). Both code snipplets also weed out the duplicate Retirees and only list them once.
I now have the question; Is one section of code using a stored procedure is more efficient than the other? I’ve tested both selects in my VB.NET project and the second code snipplet is about 50 milliseconds faster. My record count is 1578. Even if the second code snipplet is faster, is it more efficient to use the join? Nobody will really notice 50 milliseconds, so I want to go with the code that will take the least amount of resources from SQL. I used Query analyzer to perform an Estimated Execution Plan and didn’t notice any real differences. Of course I’m new to this, and not sure what I’m looking at.
Any suggestions or feedback is appreciated!! Again, thanks for the great feedback you’ve already given!! 
SELECT [table1].LastName, [table1].FirstName, [table1].EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT [table2].LastName, [table2].FirstName, [table2].SocSecNo AS EmpID
FROM table2
LEFT JOIN [table1] ON [table2].SocSecNo = [table1].EmpNo
WHERE [table1].EmpNo IS NULL
OR
([table1].EmpNo IS NOT NULL
AND
[table1].EmpTermDate IS NOT NULL)
ORDER BY LastName ASC, FirstName ASC, EmpID ASC
SELECT LastName,FirstName,EmpNo AS EmpID
FROM table1
WHERE EmpTermDate IS NULL
UNION
SELECT LastName, FirstName, SocSecNo AS EmpID
FROM table2
WHERE SocSecNo NOT IN (SELECT EmpNo FROM table1 WHERE EmpTermDate IS NULL)
ORDER BY LastName ASC,FirstName ASC, EmpID ASC
Lost in the vast sea of .NETNET
|
|
|
|
|
The second statement is definitely faster as you have a shorter WHERE condition compared to the first one.
You should first choose whichever is logically correct, and if both are correct, then choose the simplest one.
BTW, from what I see your first statement can be replaced with the second, as it does the same thing anyway.
First statement: select table1 excluding retiree UNION select table2 where records not in table1 or records are retiree.
Second statement: select table1 excluding retiree UNION select table2 where records not in table1 (which includes retiree).
Hope that helps,
Edbert
|
|
|
|
|
I'm going to go with the second statement. It is logically correct and I believe more readable and it is faster.
I can't take out the first part of the select. Table 1 is our payroll table which has 4158 records with around 1400 active employees. Table 2 contains our retirees, COBRA participants and 2 agencies who have health insurance, but are not on our payroll. Table 2 has 606 records. 9 of the records on table 2 are retirees who have come back as active contractual employees. If I just use the second portion of the select statment, the result list is 597 which contains all the health insurance records with the exception of the 9 duplicates. The first portion of the select pulls back all the active employees including the 9 retirees who should be golfing, but their working contractually.
With everyone's feedback, I believe I have a good piece of code for this lookup. I took a SQL course about a year and a half ago, and now I'm finally getting into my first ADO project. Thanks for the feedback! I really appreciate everyone's help. 
Lost in the vast sea of .NET
<a href="http://www.komputing.com/Pricelist.html">Visit my website at www.komputing.com</a>
|
|
|
|
|
Hi All
I have already checked the answers in discussions to find a solution for my problem but I could not..
I am using Trancation Replication for my customer. There is one server (Publish) in a city and 5 more Subscription in different citys. We are only replicating one Table, that is called "Adresse"
So far I got 2 kind of problems.
1.One of them subscriptions being "inactive".
2.the other one is that some of the subscriptions have connection problem with Publisher DB.
For the Problem 1, if there is no transaction occurs within "14 days(default value)" between publishing server and subscription servers, becoming Subscriptions inactive. I changed this value as "never expired" but I am not sure it is a good idea. As I read, Distribution DB is growing. But only one Table is it a problem?
The 2. Problem that I yesterday had is connection Problem. 3 of subscriptions was working fine but 2 of them were not available.
The error messages are at replication monitor like this for these 2 server.
"The agent is suspect.No Response within last 10 minutes"
I know that is only a information messages.
But in subcription side, the error was;
"The Process could not connect to Distributor 'Server1' "
The customers Network is really bad and they are living disconnections very often.
Then, in replication monitor screen, I made right click to subcriptions which are not running and clicked "Start synchronizing".
After that, the transactions are made and the situations of these 2 subscriptions became normal and active.
Regarding this problem, if there is a network problem in Subcriptions, how can replication (synchronizing) start automatically, after the network is up again.
Because, its not good, everytime connecting to Customer server and start synchronizing. Could it be made with and stored procedures or with a Job, script??
Thanks alot in advance...
Tolga
Tolga YARAMIS
|
|
|
|
|
I'm updating a MSAcess app to run on SQL with a .NET frontend. Part of the app needs to create a temporary table and export its contents to DBF file. I want the same functionality in my new app, without the user having to go anywhere near Enterprise Manager.
In Access I used a pre made header file (blank dbf file with column names and no data basically) which I linked to a temp table and populated that with an insert query - this filled the dbf file for me. All I needed to do was delete the temp table and hand the DBF file to the end user.
Any pointers where to start please ....
Tks
Dave
Tks
Dave
|
|
|
|
|
To insert two table with the same fields into one table using stored procedure.
table A with field E and R
table B also have the same fields with table A
result:
table C with the records from table A and B. Thanks
<italic>Work hard and a bit of luck is the key to success. You don`t need to be genius, to be rich.
|
|
|
|
|
Never mind I figured it out already by using Insert into, But now I'm having String or binary data would be truncated. error .
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
string or binary data would be truncated means that the length of the string you are trying to insert is > then the length of the column.
For example,
Maybe the column length is 10 and you are trying to insert a 12 char string.
"People who never make mistakes, never do anything."
|
|
|
|
|
|
create or replace view YOUR_VIEW as
select SOME_COLUMN_LIST
from SOME_TABLE_LIST
where CONDITIONS
;
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
create viewname (Syntex of the SQL)
eg create viewname (Select * from emp)
|
|
|
|
|
how to save pic in your database
|
|
|
|
|
Here ya go. FYI, it's more efficient to save a pic in a file directory rather then a database.
Dim bufferLen As Integer
Dim fs As System.IO.FileStream
Dim br As System.IO.BinaryReader
Dim buffer() As Byte
bufferLen = 10000000
fs = New System.IO.FileStream(fileName, FileMode.Open, FileAccess.Read)
br = New System.IO.BinaryReader(fs)
buffer = br.ReadBytes(bufferLen)
br.Close()
fs.Close()
Dim sConn As New String(strConnection)
Dim Con As New SqlConnection(sConn)
Dim sqlcmd As SqlCommand
sqlcmd = New SqlCommand("INSERT INTO Picture (Picture) VALUES (@Picture)", Con)
Dim dataParm1 As SqlParameter
dataParm1 = sqlcmd.Parameters.Add("@Picture", System.Data.SqlDbType.Image, buffer.Length)
dataParm1.Value = buffer
Con.Open
sqlcmd.ExecuteNonQuery()
Con.Close()
"People who never make mistakes, never do anything."
|
|
|
|
|
Hi,
I am binding the database column with dropdownlist box.It is working fine.
But i have to set particular value as my first value in the dropdownlist box.
for example, the coulmn values are
Windows 2003
Unix
Solaris
Linux
Windows 2000
Windows XP.
I want to show Linux as my first value in the dropdownlist box, then other values.
Linuxt may be 5 th record or 24 th record or anywhere in the table.
I have used simple query now. "SELECT CategoryName From Category".
Can any one help me to write a query.
Thanks & Appriciate You.
|
|
|
|
|
Since you require a sort order and that sort order is not the lexical order of the text, you can add a column to the table called, .. Sort_Order. Load that column with unique numeric values and then your query becomes "Select CategoryName from Category order by Sort_Order".
Chris Meech
I am Canadian. [heard in a local bar]
Gently arching his fishing rod back he moves the tip forward in a gentle arch releasing the line.... kersplunk [Doug Goulden]
Nice sig! [Tim Deveaux on Matt Newman's sig with a quote from me]
|
|
|
|
|
Hi,
Thanks for your reply,
My table has only two column one is automatic(identity) column and other is CategoryName.
i can't add any more column this is the problem, so how can i do this.
Thanks
|
|
|
|
|
Perhaps another way would be to not bind a datatable to the control, but to loop manually around the datatable adding the data items to the control. Put an IF clause in to look out for the item that you want and insert this into the first position of the control.
Shawn
|
|
|
|
|
I have a recordset and want to push this one into a database. Is there any way to do it without a recordset loop ?
|
|
|
|
|
You can look into the BCP command in SQL Server. If you are using VB I think you can do a rs.Save to save it to a text file. It has been a while since I used VB so can't remember if you can set the delimited for the file or not. If you are using .Net, not sure. I do know you can save it to an xml file using the dataset object. I haven't notice the capability to save to disc in any other format though, but it might be there.
Also, triggers don't fire when you use BCP.
Michael
I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)
|
|
|
|
|
Why should i use databinding in my forms while coding with business objects is easier for me?
|
|
|
|
|
|
Please tell me!
How can i get first 5 records in SELECT query? (I'm using SQL server 2000)
Thanks
|
|
|
|
|
|
Hi,
Please suggest if there is any difference between using keywords JOIN and INNER JOIN? Is there any perfomance advantange in using one of them ?
Ashu Sharma
Web Analyst
|
|
|
|
|
JOIN == INNER JOIN. INNER JOIN is implied when using JOIN. No performance difference.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
