Query - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Query

Author

Topic

venkat_sajja
Starting Member

4 Posts

Posted - 2005-06-18 : 14:35:28

Table Name : Serials

Serials Int
Updated Char
UpdatedBy Varchar

-----------------------------------------------
Serials Updated UpdatedBy
-----------------------------------------------

1000 Yes Shamsh
1001 Yes Shamsh
1002 Yes Shamsh
1003 No
1004 No
1005 No
1006 No
1007 No
1008 No
1009 No
1010 No

------------------------------------------------

update Serials set Updated = 'Yes', By = 'Shamsh', where Serials in (select top 3 Serials from Serials where Updated= 'No' order by Serials)

using the above SQL statement i can update first 3 records where Updated = No. But i would like to add one record in another table where it should create a record like

From To UpdatedDate
1003 1005 6/18/2005

that means i would like get the first and last record values of Serials so that i can enter into another talble like this.

can anybody help me in this regard?

thanks in advance
venkat

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-18 : 22:54:36

quote:
Originally posted by venkat_sajja

From To UpdatedDate
1003 1005 6/18/2005

that means i would like get the first and last record values of Serials so that i can enter into another talble like this.

It looks like the first (1000) and last (1010) of Serials don't match the values in the example I've quoted.

I read your problem statement twice and can't understand what you are trying to do.

venkat_sajja
Starting Member

4 Posts

Posted - 2005-06-19 : 02:10:33

There are three fields in the table
1.Serials
2.Updated
3.UpdatedBy

i am updating this serials table using the following query

-----------------------------------------------------------------
update Serials set Updated = 'Yes', UpdatedBy = 'Shamsh' where Serials in (select top 3 Serials from Serials where Updated= 'No' order by Serials)
-----------------------------------------------------------------

using the above SQL statement i can update first 3 records where Updated = 'No'.

this works fine.... but now i would like to add one more record in another table after updating serials table using the above sql statment.

That table consists of the following fields

From
To
UpdatedDate

And if you see the sql statement again... i am getting first 3 Serials where updated='No'. And in this table i would like to get first Number and last number of the updated Serials. In this example i will get 1003 to 1005 (3 serials). how can i get these two numbers through query.

i think i am clear this time.....

SamC
White Water Yakist

3467 Posts

Posted - 2005-06-19 : 08:12:58

[code]
INSERT INTO AnotherTable (From, To, UpdateDate)
SELECT MIN(Serials), MAX(Serials), GETDATE()
FROM (
select top 3 Serials
from Serials
where Updated= 'No'
order by Serials
) X
GROUP BY Serials[/code]

venkat_sajja
Starting Member

4 Posts

Posted - 2005-06-19 : 11:59:19

I will try this... thank you

venkat_sajja
Starting Member

4 Posts

Posted - 2005-06-20 : 03:13:46

the above code worked but in a different manner.. the output was something like this...

From To UpdatedDate
1003 1003 6/20/2005
1004 1004 6/20/2005
1005 1005 6/20/2005

I have modified the code a bit and i got the exact result what i wanted ... i have removed Group By Serials

here is the code

INSERT INTO SerialUpdated
([From], [To], UpdatedDate)
SELECT MIN(Serials) AS Expr1, MAX(Serials) AS Expr2, GETDATE() AS Expr3
FROM (SELECT TOP 3 Serials
FROM Serials
WHERE Updated = 'No'
ORDER BY Serials) DERIVEDTBL

and the resuld is

From To UpdatedDate
1003 1005 6/20/2005

But the code given by SamC helped me in getting the result what i wanted...

Thank you SamC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources