|
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
|
|
|
|
|
Regret for typing mistake. Both are same tbale like "TableA"
Comparing the column (ColA) in your case EDate (also id to be used. because date is dependet on id) from outer query & subquery,
Then counting the values which are more than outer query's value.
following must work. I've checked at my end.
DECLARE @nth as bigint
SET @nth = 2
SELECT *
FROM TblA AS a
WHERE @nth = (SELECT Distinct Count(*)
FROM TblA AS b
WHERE b.EDate >= a.EDate AND b.id = a.id)
Once again regret for the mistake
Regards
KP
|
|
|
|
|
These multi-date/multi-value ones can be a bit tricky.
One solution is this: (and it assumes that each employee only has a single entry for any given date)
create a view, called vwEmployeeMaxDate that contains the following:
select ID, max(EDate) as MaxDate from TABLENAME
group by ID
Secondly, to get the information you want above, use the following query:
select TABLENAME.ID, TABLENAME.Value1, TABLENAME.EDate
from TABLENAME inner join vwEmployeeMaxDate on (TABLENAME.ID = vwEmployeeMaxDate.ID) and (TABLENAME.EDate = vwEmployeeMaxDate.MaxDate)
brackets not necessary, but used for emphasis of the two parts of the join.
Hope this helps.
-------------------------------------------
Don't walk in front of me, I may not follow;
Don't walk behind me, I may not lead;
Just bugger off and leave me alone!!
|
|
|
|
|
I think ur way of concept will be help full for me...,
Thank u,
Regards,
Magi
|
|
|
|
|
Suppose if i want the a paricular employee previous date value want, how to fetch that?
Means Suppose 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
|
|
|
|
|
SELECT ID, MAX(value1) AS value1<br />
FROM tableName<br />
GROUP BY ID
I Love SQL
|
|
|
|
|
Hi blue boy,
Some times the value wil go decrement also...,
So we cant say max(value),
then it will be give wrong o/p,
Regards,
Magi
|
|
|
|
|
I am currently having problems with the SQL Server 2005 Developer.
- I have a Windows XP SP2 Japanese version.
- This machine is connected to a domain managed by Linux server.
- I login with a domain account and install.
Initially, the intallations were failing with an error message that I do not
have administrator right.
We created an account on my machine with the same user name as that used
for the domain, and gave this administrator rights.
With this the installation was completed. However, there is another problem,
I cannot connect to the SQL Server.
For the installation, I chose "Local System" with Windows Authentication,
but the connection to the database still fails with the error message that
remote connection is not supported. I do not understand why I need TCP to
accesss a local installation of the SQL Server.
However, I followed the procedure outlined in a MS KB to enable TCP. But the
connection to the database still fails.
I am using the Management Studio to connect to SQL Server. I have also tried
creating a database in VS. NET 2005 and this also fails. I have checked the
services and all are running.
What is wrong and what can I do? (it takes more than an hour to install this
stuff and it is really consuming my time). Any help?
Best regards,
Paul.
Jesus Christ is LOVE! Please tell somebody.
|
|
|
|
|