|
I need to get a value from previous row in result with any extracted row... ?
here's with an example
Table structure
empid EmpName Otherdetails
1 Robert abcd
2 Peter abcd
1 Bob abcd
Above given is history table with records added for any update or delete...so Robert was the record inserted to main table and Record was updated as bob. So we have two records in history table as above with same id and modifieddate to get order
Now here Robert updated his name as Bob....now in result set I need to get a list of employees and their previous name like..
Id NewName OldName
1 Robert Null
2 Peter Null
1 Bob Robert
Can I get this somehow? Any hint or suggestions welcome..
Keep DotNetting!!
GeekFromIndia
modified on Wednesday, September 3, 2008 11:23 AM
|
|
|
|
|
Show here your update command to see how you make that update.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
This is actually a history table...which will have all insert, update and delete operations recorded...this works through a trigger...
so for every update there will be a new row added to the table...
So now I have old name and updated name as different rows...i need to get both names together in a query...can be selected using empId and modified date...
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
You can if you have say a datemodified column
select id, newname,oldname
from emp e1
left outer join emp e2 on e2.id = e1.id
and e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.id
and e3.datemodified < e2.datemodified)
I might have a bit wrong here, as I don't have sql server on this PC so can't test, but you get the idea - find the same id where the datemodified is the latest date for this id BUT less than the current record, using an out join as presumably not every record will have been changed.
Hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Well this query makes sense...but its giving all null for oldname...i guess something small we are missing here...
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
got it...awesome...
there was a small change though...last clause should be e3.datemodified < e1.datemodified
select id, newname,oldnamefrom emp e1left outer join emp e2 on e2.id = e1.idand e2.datemodified = (select max(datemodified) from emp e3 where e3.id = e2.idand e3.datemodified < e1.datemodified)
Thanks a lot...
Keep DotNetting!!
GeekFromIndia
|
|
|
|
|
Great. Did wonder, I usually test first but couldn't this time. Still, it got you where you wanted to go. 
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I need a little help building my DTS. i told my boss id have this done by end of week and im a little stuck right now on this step. any help would be greatly appericiated. heres what i need:
UPDATE * fields in DB
WHERE Fields in Excel Sheet
Match the fields in DB.
The fields are:
UPC_Code
item_id
cost
price
Last_yr
Also would need a little help with this code to, i do have 90% of this already completed but to dumb to figure these 2 things out.
I need a SQL script that would take the Price Field in the excel sheet and multiply it by .0375 to create the cost field in the excel sheet.
Thank you for any help you may offer me.
|
|
|
|
|
SELECT AGE_CATEGORY, SUM(MALES) AS MALES, SUM(FEMALES) AS FEMALES FROM AGE_CATS GROUP BY AGE_CATEGORY
AGE_CATEGORY MALES FEMALES %
------------ ----- ------- --
0-5 200 400 60%
5-14 200 200 40%
I'm having this select statement which outputs the first three columns and wish to have the third column "textually typed by me now for this post".. How can I get the last column
Please help guys!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
modified on Wednesday, September 3, 2008 7:58 AM
|
|
|
|
|
SELECT AGE_CATEGORY, SUM(MALES) AS MALES, SUM(FEMALES) AS FEMALES
(SUM(FEMALES)/ (SUM(MALES)+SUM(FEMALES))) * 100
FROM AGE_CATS GROUP BY AGE_CATEGORY
Is that what you are looking for?
|
|
|
|
|
Thank you guys
You're amazing!
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
In a single select this will do it:
SELECT AGE_CATEGORY, SUM(MALES) AS MALES, SUM(FEMALES) AS FEMALES,
convert(float,(sum(males) + sum(females))) * 100 / (SELECT SUM(MALES) + SUM(FEMALES) FROM AGE_CATS) as PCent
FROM AGE_CATS GROUP BY AGE_CATEGORY
However it would be better to do
DECLARE @tot float
select @tot = SUM(MALES) + SUM(FEMALES) FROM AGE_CATS
SELECT AGE_CATEGORY, SUM(MALES) AS MALES, SUM(FEMALES) AS FEMALES ,
convert(float,(sum(males) + sum(females))) * 100 / @tot as Pcent
FROM AGE_CATS GROUP BY AGE_CATEGORY
hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks mate
I owe you
All generalizations are wrong, including this one!
(\ /)
(O.o)
(><)
|
|
|
|
|
No problem.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
hi i have a table that store my subject.
each subjct has a parentid that refer to one subject id in itself table.
how can i write query that return subject with number of there childs?
sepel
|
|
|
|
|
Where are stored childs of subject? In which table?
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
can you give structure of tables you are talking about and structure of output you need?
|
|
|
|
|
You might want to have a google and look at recursive common table expressions (CTE) (if you're on SQL2005).
|
|
|
|
|
OK, here is the deal. I've got a table with 2 columns: "ID"(int), "Name"(string). "ID" - is a unique key field used in relations with other tables, but the values of the "Name" should be unique as well. I need to add more data into this table, but not to create some duplicate information.
I know about INSERT DISTINCT, but I am not using an SQL command, instead I use a VS generated TableAdapter (in my case it is named "companiesTableAdapter"). Here is a small code snippet:
this.companiesTableAdapter.Insert("XYZ Company");
this code does insert the string into a table, but it doesn't care if "XYZ Company" is already there.
(the DB itself is created in Access2003, if this may be helpful)
If someone has done something similar, I would really appreciate their help.
|
|
|
|
|
First check if that name exists, in case if exists then don't insert otherwise do insert.
example:
if(select count(name) from tablename where name='value')=0 <br />
begin<br />
insert into tablename values ('idvalue','namevalue')<br />
end<br />
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you then save my time by voting my post.
|
|
|
|
|
Worked great! Thanks a lot
|
|
|
|
|
No problem.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
You can add an unique index on the name column.
|
|
|
|
|
How can I do that? (Add a unique numerical index to a column)
|
|
|
|
|
Simply really
Create unique index fred on myTable(myColumn)
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
