Displaying output based on previous column value

Question

Hi,

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.

alt text

I need to display the data in the order as shown below.

alt text

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.

Answer 1

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.

with cte1 as (
select CustomerID, day_id, doseage, type, row_number() over (partition by customerID order by day_id) as rownum FROM Some_Table
)
select current.CustomerID, current.day_id as CurrentDayID, current.Doseage as CurrentDoseage, current.type as CurrentDoseType, Previous.Day_id as PreviousDay_ID, previous.Doseage as PreviousDoseage, previous.Type as PreviousDoseType
FROM cte1 as current
left join cte1 as previous
ON current.customer_id=previous.customer_id
and current.rownum = previous.rownum + 1

Displaying output based on previous column value

Follow this question

Related Questions