x

Displaying output based on previous column value

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][2]

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.

[2]: /storage/temp/267-desired_output.jpg
data.jpg (37.2 kB)
desired_output.jpg (71.5 kB)
more ▼

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

Karthik Venkatraman gravatar image

Karthik Venkatraman
72 8 9 10

(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

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1840
x371
x45
x28

asked: Jun 23, 2012 at 06:06 PM

Seen: 820 times

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