|
True - this is what happens when you code when tired. You end up overcomplicating things. Thanks for pointing this out to the OP.
I'd like to think that I would have spotted this, this morning.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Thank you! This worked perfectly.
|
|
|
|
|
I have 2 tables. I want to use info from the second table to update column values in the first. Consider:
T1:
id time1 time2
1 8:00 null
2 8:01 null
3 8:02 null
4 8:12 null
T2:
id time2
2 8:09
4 8:15
What I want is:
T1:
id time1 time2
1 8:00 null
2 8:01 8:09
3 8:02 null
4 8:12 8:15
Is there an sql command sequence that can achieve this? Its easy to think about it iteratively, but in the absense of explicit iterators in sql (to my understanding) I hope that there's another way.
Its clear that I can exec the join:
select T1.id, T1.time2 from T1, T2 where T1.id = T2.id
but using this as part of an update is broken (well, because I don't know how to do it):
(wrong
update T1
set time2 = ( select time2 from T1, T2 where T1.id = T2.id)
where id = ( select id from T1, T2 where T1.id = T2.id )
how can I write an <update> on T1.time2 that matches T1.id = T2.id and updates the T1.time2 null with the T2.time2 ?
|
|
|
|
|
UPDATE T1
SET time2 = T2.time2
FROM T2
WHERE T1.id = T2.id Tested with SQL Server 2000 SP4.
You basically have an implicit cross-join with the table you're updating and the tables and join statements listed in the FROM clause.
|
|
|
|
|
Hi,
I need to do a check on a date field in my table, I need to check if the entered year is in the publication date. Currently I have it like this:
SET ROWCOUNT 500;
SELECT *
FROM tblFullTextSearch
WHERE
fldPublicationDate LIKE (1995);
The reson why I used like is because the user can type any number into the date input textbok. The date in the table looks like:
1995-03-01 00:00:00.000
Please can someone help.
Regards
ma se
|
|
|
|
|
SET ROWCOUNT 500;
SELECT *
FROM tblFullTextSearch
WHERE
YEAR(fldPublicationDate) = 1995;
|
|
|
|
|
You can do that, but SQL Server cannot make use of any indexes on the fldPublicationDate column. If the year is what's wanted, you can do
SELECT *
FROM tblFullTextSearch
WHERE fldPublicationDate >= '19950101' AND fldPublicationDate < '19960101' Obviously this technique does not work if you want to find all records from March, or the first day of the month, for example.
|
|
|
|
|
Hi i need one more query...,
See i have records like this:
Id value edate
100 1000 10/13/2006
101 1300 12/21/2006
102 1250 09/19/2006
102 1765 4/28/2007
102 1845 5/4/2007
100 1500 3/21/2007
100 1750 6/1/2007
101 1200 4/21/2007
103 1000 12/12/2006
If i pass the month and year based on it my o/p want to come...,
See its diff from other queries...,
Suppose if i select month=5 and year =2007
My o/p will be:
Id value edate
100 1500 3/21/2007
101 1200 4/21/2007
102 1845 5/4/2007
103 1000 12/12/2006
Means See the above table:
for that id=100 its not taken final date record also and last entry record also...,
Based on the passing month & year,it has taken 3rd month record...,
Same time, see that id=102 taken 5th month record ,its not taken 4 th month record...,
See that id=103 dont have any other date record so its taken which one have value that one...,
Like that i need query...,
If u didnt get what i am asking,i am ready to explain more than this also...,
Advance thanks...,
-- modified at 7:16 Tuesday 31st July, 2007
Regards,
Magi
|
|
|
|
|
in a nutshell,
row with max(edate) for a specific id, less than given Month & Year
is my understanding right
Regards
KP
|
|
|
|
|
Sorry krish ur correct,
See i have modified the query...,
It has taken that equal month record also...,
and see its not taken max(edate)...,
see that id=100 have 6th month 2007 th year record also...,
But it has taken 3rd month record...,
So we cant say max(edate) also...,
Plz if possible give me the query for that...,
Advance thanks,
Regards,
Magi
|
|
|
|
|
Yes for id 100, date with Jun-2007 to be ignored becasue is more than search condition i.e. May-2007.
to be picked up is Mar-2007 with the next higher date.
comming for 102 as per your example
102 1845 5/4/2007
May-2007 data is fetched is it right. should it be Apr-2007 like
102 1765 4/28/2007
pls check is this what you are looking for ...
DECLARE @Mth AS Varchar(5)
DECLARE @Yr AS Varchar(5)
SET @Mth = '5'
SET @Yr = '2007'
SELECT * FROM TblA order by id
SELECT a.*
FROM TblA a
INNER JOIN (SELECT id, Max(Edate) AS Edate FROM TblA WHERE Edate < @mth + '/01/' + @yr GROUP BY id)b
ON a.id = b.id AND a.Edate = b.Edate
ORDER BY a.id
output what i got is
100 1500 2007-03-21 00:00:00.000
101 1200 2007-04-21 00:00:00.000
102 1765 2007-04-28 00:00:00.000
103 1000 2006-12-12 00:00:00.000
-- modified at 8:10 Tuesday 31st July, 2007
Regards
KP
|
|
|
|
|
No krish i didnt get any o/p like that..,
See i have modified my query,i need o/p for that selcted month also...,
For that id=102 it want to display 5th month record not 4th one,
That i have modified...,
Plz send query for like that...,
Hlp me plz,
Advance thanks
-- modified at 9:00 Tuesday 31st July, 2007
Regards,
Magi
|
|
|
|
|
hi,could anyone explain me on how to create columns in sql wen i enter a charcter value in text box?
let say,if i enter A, one column need to be create,if B two need to be created in existing table ....
how can i do this???
thanks for any help!!!
The Best performance for good work tomorrow is to do good work Today
|
|
|
|
|
Need to create a table (CREATE TABLE ...)
and then add columns (ALTER TABLE ...)
refer this url for usage of CREATE TABLE & ALTER TABLE
http://sqlzoo.net[^]
Regards
KP
|
|
|
|
|
hi all
I want to trace only one Database operation using SQL profiler
can any busy tell me how to use it
when i create trace it shows all the events , but I only want to check of one database
Thanks in advance
The Stifler
--
Bugs can neither be created nor be removed from software by a developer. They can only be converted from one form to another. The total number of bugs in the software always remain constant.
|
|
|
|
|
Create a new template. You'll be able to edit the filters. One of those should be database name. Theres also client name which can be handy if there are mutliple developers.
|
|
|
|
|
In the SQL Server 2000 version, I found that filtering on database name was not reliable (I can't recall whether you got all databases or no results at all - it was one of those). Instead, use the DB_ID function to find out the database's numeric ID and use that in the appropriate filter.
|
|
|
|
|
Wierd 
It's been a while since I used it with 2000 but I dont remember having any issues with it. Then again I can't remember how to spell my middle name most of the time :P
|
|
|
|
|
Hi i have one table like this format:
ID Value1 EDate
100 2000 5/21/2007
101 3200 5/15/2007
102 1700 4/11/2007
103 2800 3/23/2007
100 2500 6/12/2007
102 3500 7/10/2007
Now i want the latest record of each employee:
means my output want to be like this:
Id value1 Edate
100 2500 6/12/2007
101 3200 5/15/2007
102 3500 7/10/2007
103 2800 3/23/2007
How to write the query?,
Advance thanks,
Regards,
Magi
|
|
|
|
|
is this what you are looking for
SELECT a.*
FROM a
INNER JOIN (SELECT id, Max(edt) AS edt FROM a GROUP BY id)b
ON a.id = b.id AND a.edt = b.edt
ORDER BY a.id
Regards
KP
|
|
|
|
|
No Krish,
I didnt get what ur trying to say...,
See the above table...,
And My o/p details...,
Plz hlp me,
Regards,
Magi
|
|
|
|
|
What i understood :
need to fetch thoes rows with max(EDate) for each group of ID
solution :
1. fetch all max(EDate) group by ID (in my query i've used inline view)
2. then get corresponding rows for the ID & EDate
I've just created a table by name "a" nad inserted the rows given in your example. my query returns the same rows as listed in your sample output.
Regards
KP
|
|
|
|
|
Suppose if i want the a paricular employee previous date value want, how to fetch that?
Means second highest date value need means?,
Table have like this:
Id value1 Edate
100 2000 5/21/2007
100 2500 6/21/2007
100 3000 7/11/2007
I want o/p like:
Id value1 Edate
100 2500 6/21/2007
Advance thanks,
Regards,
Magi
|
|
|
|
|
earlier i've given following solution for getting nth max value. you can use the same.
DECLARE @nth as bigint
SET @nth = 10
SELECT *
FROM TblA AS a
WHERE @nth = (SELECT COUNT(ColA)
FROM TblB AS b
WHERE b.ColA >= a.ColA)
This will return 10th Max.
change value of the variable @nth to get the required Max.
Regards
KP
|
|
|
|
|
Really ur query confusing me...,
I dont know how to use it...,
I given only one table...,
In that how u got 2 tables?,
And whats that cola & colb?,
Really dont know...,
Sorry,Plz see my table and tell me,
Regards,
Magi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
