April 20, 2012 at 6:33 am
Hi all,
Wondering if anyone can help me. I'm trying to count active records in the time frame between two date ranges.
For example, my date range could be 01/01/2011 to 20/01/2012. Within this period a record could be active between 01/02/2011 and 20/07/2011 and again from 15/10/2011 to 18/01/2012.
What I'm trying to display would be somthing like this.
Date Count
Jan-11 0
Feb-11 1
Mar-11 1
Apr-11 1
May-11 1
Jun-11 1
Jul-11 0
Aug-11 0
Sep-11 0
Oct-11 1
Nov-11 1
Dec-11 1
Jan-12 1
I've tried creating a tally table that gets the dates between my date range but can't seem to get any further marry up and count the number of records that active over a period of time. This example I've given above is just for one record to keep things simple.
Any pointers or advice would be gratefully received
April 20, 2012 at 6:48 am
To help those who want to help you with a tested solution, please post table definition, sample data and required results when using the sample data.
You can do this easily and rapidly, by clicking on the first link in my signature block. The article contains sample T-SQL to make the task easy to do in a very short time.
April 23, 2012 at 1:50 am
Possibly something like this may work for you:
DECLARE @t TABLE ([From] DATETIME, [To] DATETIME)
DECLARE @FromDate DATETIME
,@ToDate DATETIME
,@months INT
SELECT @FromDate = '2011-01-01', @ToDate = '2012-01-20'
SELECT @Months = 1 + DATEDIFF(month, DATEADD(month, DATEDIFF(month, 0, @FromDate), 0)
,DATEADD(month, DATEDIFF(month, 0, @ToDate), 0))
INSERT INTO @t
SELECT '2011-02-01','2011-07-20'
UNION ALL SELECT '2011-10-15','2012-01-18'
;WITH Tally (n) AS (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8
UNION ALL SELECT 9 UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL SELECT 12
UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL SELECT 15 UNION ALL SELECT 16)
,AllMonths ([Month], [Count]) AS (
SELECT DATEADD(month, n+DATEDIFF(month, 0, [From])-1, 0), 1
FROM @t
CROSS APPLY (
SELECT n
FROM Tally
WHERE n BETWEEN 1 AND 1 +
DATEDIFF(month, DATEADD(month, DATEDIFF(month, 0, [From]), 0),
DATEADD(month, DATEDIFF(month, 0, [To]), 0))) x)
SELECT STUFF(SUBSTRING(CONVERT(VARCHAR(14), [Month], 113), 3, 9),5,3,'-') As [Month]
,SUM([Count]) AS [Count]
FROM (
SELECT [Month], [Count]
FROM AllMonths
UNION ALL
SELECT DATEADD(month, n-1, DATEADD(month, DATEDIFF(month, 0, @FromDate), 0)), 0
FROM Tally
WHERE n BETWEEN 1 AND @months
) x
GROUP BY [Month]
Note that you may wish to use another tally table that returns more n's.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
April 24, 2012 at 4:57 am
That looks like the kind of thing I'm looking for, many thanks!
April 26, 2012 at 4:42 am
That script is working great but just have a couple of questions.
If a recorded is removed and then added again against a specific person within the same month, for example Jul-11. The script currently would count this twice. How would I partition the count by a PersonID so where this situation occurs it would count once for Jul-11.
Many thanks for the script Dawin C, just need a little more help.
April 26, 2012 at 5:13 am
If by removed you mean deleted, I don't see how that could happen.
Perhaps you could post some SQL to illustrate your case. I work much better when I'm seeing inputs and outputs.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply