Displaying output based on previous column value


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.

data.jpg (37.2 kB)
desired_output.jpg (71.5 kB)
more ▼

asked Jun 23, 2012 at 06:06 PM in Default

avatar image

Karthik Venkatraman
72 8 9 12

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Jun 23, 2012 at 11:39 PM

avatar image

Magnus Ahlkvist
22.5k 20 43 43

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jun 23, 2012 at 06:06 PM

Seen: 1217 times

Last Updated: Jun 23, 2012 at 11:39 PM

Copyright 2018 Redgate Software. Privacy Policy