|
Hi Magi
Try:
SELECT EM.EmpId, TC.CardDate, SD.Whrs,
MHD.Appwhours as AppHours, MHD.AppOtHours as AppOtHours
FROM dbo.tblEmployeeMaster EM
INNER JOIN dbo.tblTimeCardmain TC
ON EM.EmpId = TC.EmpId
INNER JOIN dbo.tblSalaryDetails SD
ON EM.EmpId= SD.EmpId
AND SD.EffectiveDate = (
SELECT Min(A.EffectiveDate)
FROM dbo.tblSalaryDetails A
WHERE A.EmpId = EM.EmpId
AND A.EffectiveDate <= TC.CardDate)
LEFT OUTER JOIN dbo.tblMonthHourdetails MHD
ON TC.EmpId = MHD.EmpId
AND TC.CardDate = MHD.Sdate
WHERE TC.CardDate = '8/1/2007'
AND TC.app1 = 1
AND TC.projectid = 100
ORDER BY EM.EmpIdIt uses a correlated sub-query to find the effective salary record (assuming your date column is named "EffectiveDate").
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
Hi,
Please send me how to get last 10 records in a table( There is no index on table)
|
|
|
|
|
select top 10 * from tablename order by [id] desc
I Love SQL
|
|
|
|
|
Hi I mean to say newly inserted records.
i.e Suppose a table have 1000 records.I need to retrieve 991 to 1000 records.
|
|
|
|
|
If you have column named ID which is autonumber then my query which i posted before works
I Love SQL
|
|
|
|
|
Hello all
I am writing the query to select values between two dates. My query is
Select * from Table1 where date_sent between '25-Jul-07 12:00:00' and '01-aug-07 12:00:00' this is not retrieving me any data as there are some entries between those dates in the table.
i had tried using >=25-Jul-07 and <=02-aug-07 ' but this is fetching me 25th and 26th july data which is not supposed to be happened.
did i miss something which is very important in the query.
thanks in advance.
Bharath.S Ron
|
|
|
|
|
Select * from Table1 where date_sent >= convert(varchar, (convert(datetime,'25-Jul-07 12:00:00')),102) and date_sent <= convert(varchar, (convert(datetime,'01-aug-07 12:00:00')),102)<br />
<br />
I Love SQL
|
|
|
|
|
Hi all
i am new to sql,s o plz could any body help me to to write the query to display the duplicate record in sql server.
|
|
|
|
|
If you want to display how many times is displayed col1 the use query down below
select col1,count(col1) from TableName group by Col1
Hope this helps you
I Love SQL
|
|
|
|
|
Hi,
I have two tables in which i could not find any unique relationship. Can anyone help help me, how to view metadata of oracle table. can metadata help in solving table dependency problems.......
Regards
Manish
|
|
|
|
|
This is the last piece of a very long puzzle. I have searched and searched for the answer to this problem, but I have not uncovered anything that shows me how to do this.
I need to run the below sql inside of a stored procedure. There can only ever be one AccountID that has the Col2 value of 1. How do I get the AccountID into a parameter that I can use in serveral other statements being run inside of the same stored paramater.
Select AccountID FROM Table where Col2 = 1;
|
|
|
|
|
declare @accountId int
select @accountID = AccountId from table where col2 = 1
You may want to put a 'top 1' in there, and an if exists (select blah, to make sure that there is at least one, and that you only grab one. I know you're saying it will always work, but better to be sure.
Christian Graus - Microsoft MVP - C++
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
I am having a difficult time importing a file to either MS Access or SQL Server 2000. The following is a sample of the file I am trying to import. I am using a SQL script that can be use with the DTS wizard.
Thank you for your assistance.
Sample 1
Patient Name Patient SS# DOS CPT Code Charge Amount Diagnosis 1 Diagnosis 2 Diagnosis 3 Diagnosis 4
Sarnio, Jim R 111-11-1111 Aug 7 2003 99242 154.00 717.7
Aug 7 2003 73564 139.00 717.7
Aaron, Peter E 222-22-2222 Feb 14 2006 99274 220.00 722.0
Feb 14 2006 95903 226.04 722.0
Feb 14 2006 95904 185.36 722.0
Feb 14 2006 72050 190.00 722.0
Smith, John A 333-33-3333 Oct 3 2006 99205 224.00 735.0 735.4
Oct 3 2006 73630 117.00 735.0 735.4
Oct 3 2006 73630 117.00 735.0 735.4
Oct 18 2006 99215 163.00 735.0 735.4
Arode, Tammie E 455-44-1111 Jun 30 2003 99203 112.00 724.2 722.10
Jul 23 2003 99212 52.00 724.2
Sep 27 2004 72110 197.00 737.30 722.10 722.52 724.2
Sep 27 2004 99214 104.00 737.30 722.10 722.52 724.2
Jack Fleet
|
|
|
|
|
What is happening exactly? "difficulty importing" doesn't help us much!;P
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
I should have been more specific. What I need is the first line of each record to be associated to the detail lines below it. What I am getting is a straight import with many blank fields and no way to related the details to the person. Here is an example of my desired result from an import.
Aaron, Peter E 222-22-2222 Feb 14 2006 99274 220.00 722.0
Aaron, Peter E 222-22-2222 Feb 14 2006 95903 226.04 722.0
Aaron, Peter E 222-22-2222 Feb 14 2006 95904 185.36 722.0
Aaron, Peter E 222-22-2222 Feb 14 2006 72050 190.00 722.0
Thanks for the reply.
Jack Fleet
|
|
|
|
|
You need to think of some way of distinguishing the patient records from the diagnosis records programatically. Then you could write a routine that would find a patient record, load it, identify any associated diagnosis records and load them as well before moving on to the next patient record.
Paul
|
|
|
|
|
Paul,
Thanks. I wrote a routine that adds a recordid to each line, incrementing only when the next line the inscarrier is not null. Once this was accomplished it was pretty easy a query to separate and rename the fields and another to put is all back together nice and neat.
Thanks again,
Jack Fleet
Jack Fleet
|
|
|
|
|
Hey all,
I have posted this queston some time ago and so far have had no luck getting any further, it is v ery important i get this finished for a project, so fingers crossed someone will have an idea.
I writing an c# windows application that uses odbc to connect to a my sql database. i am able to successfully connect to the database and copy the contents of a table into a datagrid. i am trying to then get the table to update when i modify this table. This code returns no errors but does not update the table, I believe the problem is with the .Update() call.
<br />
DataSET.AcceptChanges();
DataAdapter.TableMappings.Add("Test", "TableData");
DataAdapter.Update(DataSET, "Test");
DataAdapter.TableMappings.RemoveAt("Test");
I have been trying for almost a week but jsut cannot get the table to update. Any suggestions would be much appreciated.
Below is the full code to a small application i have created to test this procedure. it simply connects, downloads a table named 'Test' to a datagrid and has a update button which when pressed whould update the table on the mysql database.
Many Many Many Many Many Thanks for any help or solution or similar code that works correctly!
Alex
using System;<br />
using System.Collections.Generic;<br />
using System.ComponentModel;<br />
using System.Data;<br />
using System.Drawing;<br />
using System.Text;<br />
using System.Windows.Forms;<br />
using System.Data.Odbc;<br />
<br />
<br />
<br />
namespace CannotUpdateTables<br />
{<br />
<br />
<br />
public partial class Form1 : Form<br />
{<br />
<br />
private System.Data.Odbc.OdbcConnection OdbcCon;<br />
<br />
private System.Data.Odbc.OdbcDataAdapter DataAdapter;<br />
private DataSet DataSET;<br />
<br />
private string ConStr;<br />
<br />
<br />
public Form1()<br />
{<br />
InitializeComponent();<br />
}<br />
<br />
private void Form1_Load(object sender, EventArgs e)<br />
{<br />
<br />
<br />
ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + txtIP.Text + "ORT=" + txtPort.Text + ";DATABASE=" + txtDatabase.Text + ";UID=" + txtUsername.Text + "WD=" + txtPassword.Text + ";OPTION=3";<br />
<br />
OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);<br />
<br />
try<br />
{<br />
txtLog.AppendText("Openning connection...\r\n");<br />
if (OdbcCon.State == ConnectionState.Closed)<br />
{<br />
OdbcCon.Open();<br />
}<br />
txtLog.AppendText("Connection opened\r\n");<br />
<br />
<br />
<br />
DataAdapter = new System.Data.Odbc.OdbcDataAdapter("SELECT * FROM Test", OdbcCon);<br />
<br />
<br />
DataSET = new DataSet("TableData");
<br />
<br />
DataAdapter.Fill(DataSET);
<br />
<br />
dataGrid.DataSource = DataSET.DefaultViewManager;<br />
<br />
<br />
}<br />
<br />
<br />
catch (System.Data.Odbc.OdbcException Ex)<br />
{<br />
txtLog.AppendText(Ex.Message + "\r\n");<br />
MessageBox.Show("Could not access the database and Display Table.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);<br />
}<br />
<br />
<br />
<br />
<br />
}<br />
<br />
private void btnUpdate_Click(object sender, EventArgs e)<br />
{<br />
try<br />
{<br />
<br />
<br />
DataSET.AcceptChanges();<br />
DataAdapter.TableMappings.Add("Test", "TableData");<br />
DataAdapter.Update(DataSET, "Test");<br />
DataAdapter.TableMappings.RemoveAt("Test");<br />
<br />
txtLog.AppendText("Table Test successfully Updated\r\n");<br />
}<br />
catch<br />
{<br />
txtLog.AppendText("Table Unable to Update\r\n");<br />
}<br />
}<br />
<br />
}<br />
}
If you read this far, again, many thanks!
-- modified at 13:20 Thursday 2nd August, 2007
|
|
|
|
|
--------------------------------------------------------------------------------
Hi
I have been having horrible problems tryin to get connecter.net mysql driver to work on different machine, as my laptop has broken down
I decided to try and use myodbc to discover it connected to my database but to only dissapoint me as it doesn't support stored procedures yet
i have now managed to get hold of bytefx.mysqlclient driver but this still doesn't work I get the error message
"client doesn't support authentication protocol requested by server;consider upgrading mysql client"
so i am guessing this doesn't support mysql 5.0 the most up to date server available fromy mysql
can anyone provide me with an drivers which will allow me to run asp.net to mysql, which will support stored procedures.
I don't understand why the connector won't work
I get the message
"unable to connect to any specified mysql host"
any help greatly appreciated
cheers
boyinde
|
|
|
|
|
I need to make a procedure that return free numbers from a secuential table. Could somebody help me?.
for ex: 1, 2, 3, 4, 5, 7, 8, 9, 10
Its mast be return 6. Thank
|
|
|
|
|
If you have another table that has all of the numbers you could do a left join. So if table1 is missing numbers and table2 has all the numbers you could do:
select t2.id from table2 t2
left join table1 t1 on t2.id = t1.id
where t2.id is null
This would return all the missing numbers.
Ben
|
|
|
|
|
Interesting Problem. Here is a solution to find the missing holes in your data. It will even find a leading hole if you know the expected start number. In this example the start number is set to ONE (t.[MyID] != 1 & the ISNULL result set to 0). This works with SQL Server 2000, there is more than likely an easier solution with SQL Server 2005.
Replace [MyId] with your id column name. Replace [MyTable] with your table name.
SELECT
ISNULL((SELECT MAX([MyID]) FROM [MyTable] WHERE [MyID] < t.[MyID]),0) + 1 AS FirstMissingNumber,
t.[MyID] - 1 AS LastMissingNumber
FROM
[MyTable] t
WHERE
(t.[MyID] - 1) NOT IN (SELECT [MyID] FROM [MyTable]) AND
t.[MyID] != 1
|
|
|
|
|
Thank you. You make magic with this code. I'm trying to understand it. Its works fantastic!!.
|
|
|
|
|
You are welcome.
Finding the 'last' missing number was pretty easy. The query just subtracts one from every ID and sees if it is already there and that it isn't the first assigned id.
SELECT
[MyID] - 1 AS MissingNumber
FROM
[MyTable]
WHERE
[MyID] - 1) NOT IN (SELECT [MyID] FROM [MyTable]) AND
[MyID] != 1
Finding the first missing number uses a correlated subquery. If you look at that query by itself it is pretty clear. I added the ISNULL because the first record will have no preceeding records and therefore, return NULL.
|
|
|
|
|
I agree with ArielR. Very nice. I'd have said it wasn't possible if I hadn't seen your solution.
Correct me if I'm wrong, but doesn't your solution only return the first and last numbers from a missing block...
1,2,3,9,10
It won't return all the missing numbers: 4 thru 8, will it? Just 4 and 8; right?
I also found it difficult to see how it was working, so I dumbed it down for my own understanding:
SELECT (T.ID - 1) AS MissingID
FROM tblMyTable AS T
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable);
Of course, this will pick up numbers that don't belong in the table, like 0 (zero), so I added a predicate similar to yours:
SELECT (T.ID - 1) AS MissingID
FROM tblMyTable AS T
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable)
AND T.ID > @startnum;
Of course this ONLY picks up numbers of the form (ID-1) where ID IS in the table, and ID-1 is not. It won't pick up others numbers from a block of missing numbers.
Now a concern:
I *THINK* that each element from the (T.ID - 1) set will be subject to a linear scan of the entire (SELECT ID FROM tblMyTable) result set (well, it should stop as soon as a hit occurs). This will be an O(N^2) operation. I also *THINK* that if we just sorted the exclusion set:
WHERE (T.ID - 1) NOT IN (SELECT ID FROM tblMyTable ORDER BY ID)
the DBMS should recognize the order, and do a binary search instead of a linear scan, reducing the overall select to O(N*ln(N)) (of course the ordering operation itself may be an O(N*ln(N)) operation). This may seem insignificant, but if you're searching a million record table, N^2 could be huge! In this case, N*ln(N) could be 50,000 times faster than N^2.
Then I began wondering if it could be reduced it to an O(N) operation just like a person would code it manually in a procedural language if they knew the two sets were ordered. Perhaps a LEFT JOIN like kubben suggested would do it:
SELECT Missing.ID FROM
(SELECT (ID - 1) AS ID
FROM tblMyTable
WHERE ID > @startnum
ORDER BY ID) AS Missing
LEFT JOIN
(SELECT ID
FROM tblMyTable
ORDER BY ID) AS Includes ON Missing.ID = Includes.ID
WHERE Includes.ID IS NULL;
Maybe someone who knows more about the guts of a DBMS (like Mike Dimmick) could set me straight here. I'd have liked to run some speed tests with huge tables, but just don't have the resources available to me.
David
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
