Help needed on retrieving required data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Help needed on retrieving required data

Author

Topic

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-05-02 : 02:06:08

Hi
I have data in a table (actually its an log table) like below.

ID FieldName Value UpdatedOn
1 A 10 2010-04-30 11:55:253
2 A 20 2010-04-30 11:55:253
3 A 30 2010-04-30 11:55:253
4 A 40 2010-04-30 11:55:253
5 B 1 2010-04-30 11:55:253
6 B 2 2010-04-30 11:55:253
7 B 3 2010-04-30 11:55:253
8 B 4 2010-04-30 11:55:253

Here FieldNames will be like A1, A2, A3, A4, etc (same for B also) in application. I have replaced the 1,2,3,4 to empty as all these values belongs to ‘A’.

I am getting this result set based on the updatedon date. I need to get the last entered value in each fieldname
Like below

A 40 2010-04-30 11:55:253
B 4 2010-04-30 11:55:253

The updated on date will be same for all the fieldnames because all the values has been entered at the same time. So, We can’t find last entered value in each fieldname.
I have done the sorting based on UpdatedOn and ID. The result set looks like

ID FieldName Value UpdatedOn RowNo
4 A 40 2010-04-30 11:55:253 1
3 A 30 2010-04-30 11:55:253 2
2 A 20 2010-04-30 11:55:253 3
1 A 10 2010-04-30 11:55:253 4
8 B 4 2010-04-30 11:55:253 1
7 B 3 2010-04-30 11:55:253 2
6 B 2 2010-04-30 11:55:253 3
5 B 1 2010-04-30 11:55:253 4

So, now I can get the last entered value.
But the problem is after this result if the user updates the any of the first three occurrences then that will comes as my last entered record. It will insert one record with new id as this is the log table and the log table looks like

ID FieldName Value UpdatedOn
1 A 10 2010-04-30 11:55:253
2 A 20 2010-04-30 11:55:253
3 A 30 2010-04-30 11:55:253
4 A 40 2010-04-30 11:55:253
5 B 1 2010-04-30 11:55:253
6 B 2 2010-04-30 11:55:253
7 B 3 2010-04-30 11:55:253
8 B 4 2010-04-30 11:55:253
9 A 50 2010-04-30 12:55:253
10 B 6 2010-04-30 12:55:253

Here user changed the value of A 30 to 50 and B 3 to 6.
Still I need the result set like the above as 40 is the last entered value in A and 4 is last entered value in B.

A 40 2010-04-30 11:55:253
B 4 2010-04-30 11:55:253

If the user makes the last entered value 40 as NULL then I need to get the preceding one. i.e 30.

A 30 2010-04-30 11:55:253
B 3 2010-04-30 11:55:253

Kindly help me how can I get this.

Thanks in advance

Thanks

Jack

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-02 : 05:16:27

Try this. It does assume that this "The updated on date will be same for all the fieldnames because all the values has been entered at the same time." mean that the times will be exactly the same.

SELECT ID, FieldName, Value, UpdatedOn
FROM (	SELECT *, ROW_NUMBER() OVER (PARTITION BY FieldName ORDER BY ID DESC) AS Row
		FROM tableName y
		WHERE Value IS NOT NULL
			AND UpdatedOn = (
				SELECT MIN(UpdatedOn)
				FROM tableName
					WHERE FieldName = y.FieldName)
) z
WHERE Row = 1

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-05-02 : 07:03:29

I think it won't useful for me. Because if the user enters a new value like

ID FieldName Value UpdatedOn
1 A 10 2010-04-30 11:55:253
2 A 20 2010-04-30 11:55:253
3 A 30 2010-04-30 11:55:253
4 A 40 2010-04-30 11:55:253
5 B 1 2010-04-30 11:55:253
6 B 2 2010-04-30 11:55:253
7 B 3 2010-04-30 11:55:253
8 B 4 2010-04-30 11:55:253
9 A 50 2010-04-30 13:55:253
10 B 5 2010-04-30 13:55:253

here the user added new value for A (i.e A5)(this time user is not updating the existing value) then i need to get this value as the last entered.

As per your query i think it will take takes the min(updatedon). Then i won't get this value.

Thanks

Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 09:40:13

or in that case is there any field which designates whether it was a new entry or an update?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-05-02 : 12:25:47

yeah... we have a column called 'NewColumn' which stores '1' if it is an insert (for the first time) and '0' when it's update.

Actually when we insert a value into A1 then it'll shows A2 to insert a value (cascading fields).Like this the process will goes on.

Assume that for the first time if i have inserted 2 values i.e (A1 & A2)and saved. Then NewColumn will store '1'. For the next time onwards if we add any values like A3 or A4 or etc then it will be like an update. Then the Newcolumn will stores '0'.

Hope you will understand what i am trying to explain.

Can we achieve this.

Thanks

Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-02 : 13:10:32

[code]
SELECT ID,
FieldName,
Value,
UpdatedOn
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY FieldName,UpdatedOn ORDER BY [ID] DESC) AS Seq,*
FROM Table
WHERE NewColumn = 1
)t
WHERE Seq =1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-05-02 : 13:17:56

how about if i add a new value after insert.That will be the last entered value.Then NewColumn = 0 right. I need to consider that value.

your query will not return that value right.

Thanks

Jack

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-02 : 14:01:28

quote:
Originally posted by shaik.zakeer

how about if i add a new value after insert.That will be the last entered value.Then NewColumn = 0 right.

If it's a new value, then NewColumn should equal 1.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

shaik.zakeer
Posting Yak Master

117 Posts

Posted - 2010-05-02 : 14:16:56

For the first time it will creates a xml with all the tags in the db. so this table records '1' when it is insert. Next time onwards when u do any insert of a value or updation it will updates the xml. So it will be an update for us.

Thanks

Jack

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-03 : 01:37:09

quote:
Originally posted by shaik.zakeer

For the first time it will creates a xml with all the tags in the db. so this table records '1' when it is insert. Next time onwards when u do any insert of a value or updation it will updates the xml. So it will be an update for us.

Thanks

Jack

so in that case you want again distinguish them as inserts and updates?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources