|
|
how to use lock concept in sql server 2005?
pls send some examples urgent......
|
|
|
|
|
Google "sql server 2005 locking"
Regards
David R
|
|
|
|
|
I want to Delete Duplicate records From a table except the Max(ID)Using Full Text Query.
and
Duplicate records are just certain field
not an entire row in a table.
and
I have millions of records in my table.........
|
|
|
|
|
Muhammad Fahim Baloch wrote: I want to Delete Duplicate records From a table except the Max(ID)
You should be more specific how you define duplicate. Obviously you wnat to save the latest, but what's duplicate for you in this case.
Muhammad Fahim Baloch wrote: Using Full Text Query
How does this relate to duplicates?
Muhammad Fahim Baloch wrote: Duplicate records are just certain field
Which fields? It would be easiest if you provide a dessciption of the table and tell that based on what columns you want to observe the duplicates.
Muhammad Fahim Baloch wrote: I have millions of records in my table
And how does this relate? Concerned about the speed perhaps?
|
|
|
|
|
I have a table, which has two rows
1.EmailID
2.EmailAddress
I have Duplicate Email Addresses and i want to Delete Duplicate Email Addresses from my table except the max(emailId)
and I have millions of Duplicate EmailAddresses in my table
Plz ans me as soon as possible....
Thanks!
|
|
|
|
|
Muhammad Fahim Baloch wrote: I have Duplicate Email Addresses and i want to Delete Duplicate Email Addresses from my table except the max(emailId)
You could try something like:
delete from YourTable
where exists (select 1
from YourTable alias1
where alias1.emailaddress = YourTable.emailaddress
and alias1.emailid > YourTable.emailid)
Remember to use begin transaction so that you can rollback if the result isn't what you wanted.
Muhammad Fahim Baloch wrote: and I have millions of Duplicate EmailAddresses in my table
This means that unless you have proper indexing this will take a while.
|
|
|
|
|
Hi
Thnks a lot..............................
Thanku agian.............................
You are doing greate job
Thanks agian,,,,,,,
By
|
|
|
|
|
hi
can anybody tell me about the normalization
thanks
Hemant
By:
Hemant Thaker
|
|
|
|
|
A normalized database (which is a good idea generally) typically means that the minimum amount of data is stored (no repetition) in a relational manner. Take a look here for more info...
http://en.wikipedia.org/wiki/Database_normalization[^]
Regards,
Rob Philpott.
|
|
|
|
|
Hi
I need to put 'abs' for students absent in a review
for exemple of the material which the code is 121 .
I have the following query:
SELECT id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE [121] END AS m1,
[122] AS '2', [123] AS '3'
FROM (SELECT id_field, id_student, NOTE, CODE FROM TEST)
p PIVOT (sum(NOTE) FOR id_field IN ([121], [122], [123])) AS pvt
ORDER BY id_student
I have the error here CASE WHEN (CODE) = 1 THEN 'Abs' because [121]is the type float and 'abs' is a text how to solve this?
Thanks!!
|
|
|
|
|
Change your select to make 121 a char
SELECT id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE '121' END AS m1
or
SELECT id_student, CASE WHEN (CODE) = 1 THEN 'Abs' ELSE convert(varchar,121) END AS m1
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thank you very much Ashfield.
but the problem is in 'abs' text in float champ 
|
|
|
|
|
I know, you want the literal value 'abs' or 121, so if you force your value 121 to become a char type, either by convert (or cast) or just by enclosing it in quotes then sql server will treat BOTH values as char types and will be quite happy (as far as I know).
Try it and see, if it still doesn't work post your new code and the error message again.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Thanks Ashfield!merci beaucoup
i have anouther problem with this code ,I posted a new message.
Thanks 
|
|
|
|
|
Hi all,
I have to work on Indexes and triggers in sql server 2000 can any body suggest me any article or resourse where i can learn these things.
Thanks in advance
Best regards
Rameez
|
|
|
|
|
|
I insert and update in an excel database but what is the coding to prevent the duplicating entries in the database. Plz tell in c# language. thanx in advance.
|
|
|
|
|
I have a table of employee attendent like that:
EmpID-----Date----------------Time------------------Status (Field Name)
1-------01/01/2009------01/01/2009 8:00 am--------I
1-------01/01/2009------01/01/2009 12:00 am-------O
1-------01/01/2009------01/01/2009 1:00 pm--------I
1-------01/01/2009------01/01/2009 5:00 pm--------O
2-------01/01/2009------01/01/2009 7:50 am--------I
2-------01/01/2009------01/01/2009 12:00 am-------O
But i want to make a query that take data from table above to be like that:
EmpID-----Date------------In------------Out----------In-------------Out (Fiel Name)
1-------01/01/2009------8:00 am-----12:00 am----1:00 pm------5:00 pm
2-------01/01/2009------7:50 am-----12:00 am----Null----------Null
Can we do that ?
Thank for your kindly to help me...
Best regard,
Sovann
VB.Net
modified on Sunday, February 15, 2009 9:18 PM
|
|
|
|
|
Something like this should get you close
<br />
select c.empid, c.dt as in1, <br />
<br />
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'<br />
and not exists (select 1 from clock as c2 where c2.empid = c1.empid and c2.status = 'O' and c2.dt < c1.dt)) as out1,<br />
<br />
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'<br />
and c1.dt > c.dt) as in2,<br />
<br />
<br />
<br />
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'<br />
and c1.dt > (select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'<br />
and c1.dt > c.dt)) as out2<br />
<br />
from clock as c<br />
where c.status = 'I'<br />
and not exists (select 1 from clock as c1 where c1.empid = c.empid and c1.status = 'I' and c1.dt < c.dt)
___________________________________________
.\\axxx
(That's an 'M')
|
|
|
|
|
Thanks for your kindly to help me.
but what you wrote is in query right ?
It did not process.
but if there are more employee, Does it work ?
My idea if we select and use for loop, what can we do ?
VB.Net
|
|
|
|
|
Sovann wrote: Thanks for your kindly to help me.
No worries
Sovann wrote: but what you wrote is in query right ?
Yes - just plain SQL - so you can put it in a stored proc or whatever
Sovann wrote: It did not process
? in what way. I created a dummy table and entered data similar to your example, ran the query against it and got your desired results.
The column and table names were different to yours, but the query certainly worked fine...
Sovann wrote: but if there are more employee, Does it work ?
I think it should work as long as there is always an In and Out (not sure what it would do if there was only an In time and no Out - or if there were more than two of each. It doesn't matter how many employees there are.
Sovann wrote: My idea if we select and use for loop, what can we do ?
If you WANT to write loops etc. then sure, you could do that. You could create a temporary table, populate it using a number of queries in a loop, or whatever you want to do. Depends whether your aim is to solve the problem or to write some sql using a for loop?
___________________________________________
.\\axxx
(That's an 'M')
|
|
|
|
|
I want to write some sql using for loop. Because i do not know that about number of employee. It will increase when we add new employee info.
VB.Net
|
|
|
|
|
Sovann wrote: I want to write some sql using for loop.
If that is what you want to do (i.e. you want to write loop rather than you want to achive the results most efficiently) then take a look here[^] or an example -but read the highlighted bit abut this being inefficient!
Sovann wrote: Because i do not know that about number of employee. It will increase when we add new employee info.
The SQL I wrote will handle any number of employees..
select c.empid, c.dt as in1,
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'
and not exists (select 1 from clock as c2 where c2.empid = c1.empid and c2.status = 'O' and c2.dt < c1.dt)) as out1,
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'
and c1.dt > c.dt) as in2,
(select c1.dt from clock as c1 where c1.empid = c.empid and status = 'O'
and c1.dt > (select c1.dt from clock as c1 where c1.empid = c.empid and status = 'I'
and c1.dt > c.dt)) as out2
from clock as c
where c.status = 'I'
and not exists (select 1 from clock as c1 where c1.empid = c.empid and c1.status = 'I' and c1.dt < c.dt)
the bit not crossed out selects all employees records with an 'In' status where there is not an earlier I status record - i.e. it selects the first In record for the employee. Obviously, if you need to look at multiple days, you will need to change this slightly so that it tests the date and time separately (I wasn't sure from your question whether these were stored in separate columns or not)
So - the SQL will work as it stands with any number of employees, for a single date. Simply expand it a bit to allow multiple dates and it will give you what you want.
OK - I couldn't resist, so I expanded it.
SELECT EmpId, Dt AS in1,
(SELECT Dt
FROM clock AS c1
WHERE (EmpId = c.EmpId) AND (status = 'O') AND (NOT EXISTS
(SELECT 1 AS Expr1
FROM clock AS c2
WHERE (EmpId = c1.EmpId) AND (status = 'O') AND (Dt < c1.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c1.Dt AS Float))))) AND
(FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))) AS out1,
(SELECT Dt
FROM clock AS c1
WHERE (EmpId = c.EmpId) AND (status = 'I') AND (Dt > c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))) AS in2,
(SELECT Dt
FROM clock AS c1
WHERE (EmpId = c.EmpId) AND (status = 'O') AND (Dt >
(SELECT Dt
FROM clock AS c1
WHERE (EmpId = c.EmpId) AND (status = 'I') AND (Dt > c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float))))) AND
(FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))) AS out2
FROM clock AS c
WHERE (status = 'I') AND (NOT EXISTS
(SELECT 1 AS Expr1
FROM clock AS c1
WHERE (EmpId = c.EmpId) AND (status = 'I') AND (Dt < c.Dt) AND (FLOOR(CAST(Dt AS Float)) = FLOOR(CAST(c.Dt AS Float)))))
That should work, I think, for all dates for any number of employees, where there is at least in 'I' record for any one employee on a date (i.e. if the employee doesn't turn up at all on one day there will be no output for that employee.
___________________________________________
.\\axxx
(That's an 'M')
|
|
|
|
|
I wrote similar like what you wrote but it did not work.
Sorry i do not understand SQL you wrote. i think Dt is Date field right ? what about Time field ? I do not know which one is Date and one is Time...
Sorry, If it should work and you show me the result to sure that what you write is what i want...
I am very glad and to say thanks you so much for your help me...
It had help me some knowledge about SQL...
I will try to find it more and try to change other way to get the result what i want.
If you have any some about it, please share to me.
Thanks you again.
Best regard,
Sovann
VB.Net
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
