asked
Need help in displaying the results in a specified order..
Hi, I am working on a sql server project and i need to display the results in a ordered manner using the Date Visited column. The results must display the previous visit date and the current visitdate along with the visitid column and the customer id. For each visitid's i need the previous DateVisited for the previous visitid and the current datevisited for the current visitid in a single row. I dont want to use cursors for doing this as I will not have the access for running cursors in the database. So if this can be done by simple sql queries that will be very much help ful for me. I have attached the the images of the sample table and the output that i expect. I would be very thankful if I get the solution for this. ![alt text][1] Expected OutPut ![alt text][2] I need the output like the above screenshot only. IF cursors are used, then we can easily perform this. but as I will not have access for using cursors in the database, i dont know how to perform this. Please help me... Thanks, karthik Venkatraman [1]:
http://ask.sqlservercentral.com/storage/temp/215-Table+Example.jpg [2]:
http://ask.sqlservercentral.com/storage/temp/214-Expected+Output.jpg
i could not see the screenshots, but why do you not looking at using if, while etc rather than using cursors. i never use cursors. I use If statements, while statements ets and increment them myself in code. You just need to determine what you can use to ensure that you work with unique records. maybe a rowid can be considered. So example: You have 10 records RowId 1 to 10 DECLARE @CurrentRow int @LastRow int SELECT @LastRow = Max(RowId) @CurrentRow = Min(RowId) -- Or 1 From Your table -- or temp table WHILE @CurrentRow <= @LastRow Begin -- Your code SET @CurrentRow = @CurrentRow + 1 END I hope this helps
You may have. But the first option should be a set based approach. The set based solution **almost** always perform better than a CURSOR/LOOP solution.
I have found a great difference in performance and duration doing so with previous tasks.
It is really good for you that you are not permitted to use the cursors. But you should have provided the test data in a format which can be used to create the test data. Following is the population of test data as well as a self join solution (This may not scale well for large data) IF OBJECT_ID('tempdb..#CustomerVisits','U') IS NOT NULL DROP TABLE #CustomerVisits; GO SELECT *, DateVisited = DATEADD(dd,ROW_NUMBER() OVER (ORDER BY VisitId),'2009') INTO #CustomerVisits FROM ( SELECT TOP (100) CustomerID = 1121001, VisitId = ABS(CHECKSUM(NEWID()))%29 + 1 FROM master.sys.all_columns ac1 CROSS JOIN master.sys.all_columns ac2 ) TestData ; GO SELECT * FROM [#CustomerVisits] GO WITH CTEOrdered AS ( SELECT [CustomerID] , [VisitId] , [DateVisited] , ROW_NUMBER() OVER (ORDER BY [VisitId], [DateVisited]) Rnum FROM [#CustomerVisits] AS CV ) SELECT [C1].[CustomerID] , [C2].[VisitId] PreviousVisitId , [C1].[VisitId] CurrentVisitId , [C2].[DateVisited] PreviousDateVisited , [C1].[DateVisited] CurrentDateVisited FROM [CTEOrdered] C1 LEFT JOIN [CTEOrdered] C2 ON [C1].[Rnum] = [C2].[Rnum] + 1
Hi, Thanks for your response.. I have figured it out...
7 People are following this question.
