SQL Query - Historical Data

Posted on 2014-08-25
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 47

    Expert Comment

    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 44

    Expert Comment

    by:Vitor Montalvão
    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

    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

    (	SELECT	*
    	,	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]
    FROM	CTE	a
    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

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    Join & Write a Comment

    Suggested Solutions

    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    In this article I will describe the Backup & Restore 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.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now