I am working on a college project and I need to display the output based on a specific pattern. I have a set of customers with a unique customer id. Every customer has a specific days of visit ranging from 4 till 37. each visit will be for a single day. On each visit we will provide a dose and its type to the customer.
My problem is I need to display the list of a single subject for each visit along with his previous visit, dose and its type.
The raw data output will be as shown below.
I need to display the data in the order as shown below.
You can see that for the customer 2031005 has the days visit from 4 till 37. When I am showing the details of visit 4, i need to show it along with the details of his previous visit. when its for 32, i need to show the details of visit 4 along with visit 32. when its for 33, i need to show details of 32 along with it and so on...
I cannot use cursors.. So please can anyone help in this by providing a sample working query.
Thanks in advance.: /storage/temp/267-desired_output.jpg
This is from the top of my head. Untested. If it even works, there are probably more efficient ways of doing this. Using SQL Server 2012 you have functions LEAD and LAG to look forward and backwards in a result set. With SQL Server 2008 I think you're stuck with CTEs and row_number for sequencing data.
and current.rownum = previous.rownum + 1
answered Jun 23, 2012 at 11:39 PM