Historical record state based on start/end date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Historical record state based on start/end date

Author

Topic

Mickel
Starting Member

2 Posts

Posted - 2011-08-18 : 13:46:07

I have been challenged with the following problem. I am looking for a way to get the historical record state per period using the start date and end date of that record.

Example:
Product A is valid from 15-jan-2011 till 14-jul-2011
Product B is valid from 31-mar-2011 till 4-nov-2011
Product C is valid from 4-jun-2011 till 5-dec-2011
The table would have the fields Product, start date, end date

I would like to create a query that would list the total number of valid records per period (month, quarter, year) in which the user is able to enter the reporting period. Thus:

User input: 1-mar-2011 till 31-aug-2011, monthly would produce:

Mar 2011: 2 --> product A and B
Apr 2011: 2 --> product A and B
May 2011: 2 --> product A and B
Jun 2011: 3 --> product A, B and C
Jul 2011: 3 --> product A, B and C
Aug 2011: 2 --> product B and C

New user input: 1-jan-2011 till 31-dec-2011, quarterly would produce:

Q1 2011: 2 --> product A and B
Q2 2011: 3 --> product A, B and C
Q3 2011: 3 --> product A, B and C
Q4 2011: 2 --> product B and C

I only need the period and a count of the number of valid records of that period, correctly sorted by period (for example by month). I do not the list of products. If helpful I have a table of dates that could be joined (date, month number, month name, quarter, year)

Which SQL wizard is able to help me achieving this goal?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 14:03:15

you need a calendar table like below

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

and use it like below for monthly


SELECT MONTH(MonthDate),YEAR(MonthYear),PCnt
FROM
(
SELECT DISTINCT DATEADD(mm,DATEDIFF(mm,0,Date)+1,0)-1 AS MonthDate
FROM dbo.CalendarTable(@startdate,@enddate,0) 
)f
CROSS APPLY (SELECT COUNT(Product) AS Pcnt
             FROM Table
             WHERE f.MonthDate BETWEEN StartDate AND EndDate)p

for quarterly replace mm with qq for date manipulation functions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Mickel
Starting Member

2 Posts

Posted - 2011-08-19 : 21:14:11

Thank you for your help. It needed a little tweaking, but with your help I got the job done. Well done! :)

___________________________
http://www.e-controller.eu

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-20 : 01:19:59

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources