SQL Query - Historical Data

Posted on 2014-08-25
Medium Priority
Last Modified: 2014-10-27
I have a SQL table with historical data on users of a specific system. I need to report on how one field has changed over time. Here's an example of the data:

User      Type      Effective Date
User 1      A      1/1/2010
User 1      B      5/1/2010
User 1      A      7/1/2010
User 2      B      1/1/2010
User 2      A      2/1/2010

The data basically means that User 1 was type A from 1/1 - 4/30, type B from 5/1 - 6/30 and back to type A from 7/1 through today.

How can I write a query that expands this data so I can chart the change of user types over time?
Question by:bezellvi
LVL 49

Expert Comment

ID: 40284710
what chart type are you considering? and what is it's data requirements?

i.e. what is the "expected result" please - that's your job.

while you think about that could you also tell us what version of SQL Server you are using please (it could make a difference to the solution options)
LVL 53

Expert Comment

by:Vitor Montalvão
ID: 40285077
Ordering it by User and EffectiveDate but I don't know what's the output that you want. Can you post an example of the pretended output?

SELECT User, EffectiveDate, Type
FROM YourTableName
ORDER BY User, EffectiveDate

Open in new window


Author Comment

ID: 40285668
I am looking to create a stacked bar chart with the date in the x axis and the number of users in the y axis, colored by user type. I somehow need to create a query that lets me count the number of users in each category at any given date in time.
LVL 11

Accepted Solution

John_Vidmar earned 2000 total points
ID: 40286791
	,	rn = ROW_NUMBER() OVER (PARTITION BY [User] ORDER BY [Effective Date])
	FROM	SomeTable
SELECT	a.[User]
,	[EffectiveFrom]	=	a.[Effective Date]
,	[EffectiveTo]	=	ISNULL(b.[Effective Date], current_timestamp)
,	a.[Type]
JOIN	CTE	b	ON	a.[User] = b.[User]
			AND	a.rn = b.rn - 1
BY	a.[User]
,	a.[Effective Date]

Open in new window


Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question