.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
Steve Jackman
Imran Ghani
Santhakumar Munuswamy

Home >> Forum >> Sql Server >> Post New QuestionBookmark and Share Subscribe to Forum

how to get the sql output

Posted By: Santosh     Posted Date: January 12, 2013    Points:5   Category :Sql Server
Hi
could you please help me to get the output without using loop...

Input :

Srno Department CreatedDate UpdateDate
1 HR 07/12/2012 10:30 07/12/2012 10:30
2 HR 07/12/2012 10:41 07/12/2012 10:41
3 HR 08/12/2012 07:08 08/12/2012 07:08
4 Development 07/12/2012 10:30 07/12/2012 10:30
5 Development 07/12/2012 11:33 07/12/2012 11:33
6 Testing 07/12/2012 17:18 07/12/2012 17:18
7 Testing 07/12/2012 10:31 07/12/2012 10:31
8 Testing 07/12/2012 11:38 07/12/2012 11:38

Output : shoube be like below without using any loop becaz it's taking more time to loop through..
Current row createddate should be updated as next row updatedate..

Srno Department CreatedDate UpdateDate
1 HR 07/12/2012 10:30 Null
2 HR 07/12/2012 10:41 07/12/2012 10:30
3 HR 08/12/2012 07:08 07/12/2012 10:41
4 Development 07/12/2012 10:30 null
5 Development 07/12/2012 11:33 07/12/2012 10:30
6 Testing 07/12/2012 17:18 Null
7 Testing 07/12/2012 10:31 07/12/2012 17:18
8 Testing 07/12/2012 11:38 07/12/2012 10:31

Thanks in adv.
santosh

Responses
Author: Petr Matejovsky             
Posted Date: January 18, 2013     Points: 20   

Hi,

without TempTable or CTE this is not possible

Srno must be row nuber without space.



MS SQL 2005:

// create table

CREATE TABLE [dbo].[DataTable](
[Srno] [int] NULL,
[Dep] [varchar](50) NULL,
[CreateDate] [varchar](50) NULL,
[UpdateDate] [varchar](50) NULL
)


// select

WITH CTE_table ( Srno, Row, Dep, CreateDate, UpdateDate )
AS
(
select Srno, ROW_NUMBER() OVER (PARTITION BY Dep ORDER BY Srno ) as Row, Dep,CreateDate,UpdateDate
from dbo.DataTable
)

select c.* , (select x.updatedate from CTE_table x where x.Dep = c.Dep and x.Row = c.Row - 1 ) as NewUpdateDate
from CTE_table C


output:


Srno Row Dep CreateDate UpdateDate NewUpdateDate

1 1 HR 07/12/2012 10:30 07/12/2012 10:30 NULL
2 2 HR 07/12/2012 10:41 07/12/2012 10:41 07/12/2012 10:30
3 3 HR 08/12/2012 07:08 08/12/2012 07:08 07/12/2012 10:41
4 1 DEV 07/12/2012 10:30 07/12/2012 10:30 NULL
5 2 DEV 07/12/2012 11:33 07/12/2012 11:33 07/12/2012 10:30
6 1 TEST 07/12/2012 17:18 07/12/2012 17:18 NULL
7 2 TEST 07/12/2012 10:31 07/12/2012 10:31 07/12/2012 17:18
8 3 TEST 07/12/2012 11:38 07/12/2012 11:38 07/12/2012 10:31



Petr Matejovsky, Pemasoft


Author: Santosh             
Posted Date: January 21, 2013     Points: 20   

Thanks Petr Matejovsky , it's really awesome..


even i have also got one solution ..this also working..


UPDATE datatable SET updatedate = NULL WHERE SRNO = 1

UPDATE T2 SET T2.updatedate = NULL FROM datatable AS T1 INNER JOIN datatable AS T2
ON T2.SRNO = T1.SRNO+1 AND T2.Dep <> T1.Dep;

UPDATE T2 SET T2.updateDate = T1.CreateDate FROM datatable AS T1 INNER JOIN datatable AS T2
ON T2.SRNO = T1.SRNO+1 AND T2.Dep =T1.Dep;



once again thanks for your help..



Regards
Santosh




Post Reply

You must Sign In To post reply
 
 
Find more Forum Questions on C#, ASP.Net, Vb.Net, SQL Server and more Here
Quick Links For Forum Categories:
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  OOPs  SilverlightIISJQuery
JavaScript/VBScriptBiztalkWPFPatten/PracticesWCFOthers
www.DotNetSpark.comUnAnsweredAll

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend