July 9, 2015 at 3:29 pm
So, something like this?
CREATE TABLE #temp (
ID INT NOT NULL,
[Date] DATE NOT NULL
);
INSERT #temp (ID, [Date]) VALUES
(1, '2000-05-03'),
(1, '2001-06-10'),
(1, '2014-04-02'),
(1, '2014-07-29'),
(1, '2015-07-15'),
(4, '2001-05-07'),
(4, '2013-08-01'),
(4, '2014-02-05'),
(4, '2015-07-05'),
(4, '2014-08-01'),
(9, '2002-05-01'),
(9, '2000-04-02');
INSERT INTO #temp (ID, [Date]) VALUES
(10, '2000-03-31'),
(10, '2000-04-24'),
(10, '2001-02-10'),
(10, '2001-03-06'),
(10, '2001-03-09'),
(10, '2010-05-11'),
(10, '2011-03-25'),
(10, '2012-11-25'),
(10, '2013-06-18'),
(10, '2014-02-16'),
(10, '2015-05-17');
--
Query actually starts here
--
with FiscalData as (
select distinct
ID,
year(dateadd(month,6,[Date])) FiscalYear,
rn = dense_rank() over (partition by ID order by year(dateadd(month,6,[Date]))),
grp = year(dateadd(month,6,[Date])) - dense_rank() over (partition by ID order by year(dateadd(month,6,[Date])))
from
#temp
), IslandData as (
select distinct
ID,
ConsecutiveYears = count(FiscalYear) over (partition by ID, grp),
Rnk = dense_rank() over (partition by ID order by grp desc)
from
FiscalData
)
select
ID,
ConsecutiveYears
from
IslandData
where
Rnk = 1;
July 9, 2015 at 7:35 pm
OLSONEJ (7/9/2015)
This seem to be pulling beyond lapse periods. When I have the following, I am expecting a count of 3 not 7, which is what I am receiving.INSERT INTO @INPUT_DATA (ID, [Date]) VALUES
(1, '2000-03-31'),
(1, '2000-04-24'),
(1, '2001-02-10'),
(1, '2001-03-06'),
(1, '2001-03-09'),
(1, '2010-05-11'),
(1, '2011-03-25'),
(1, '2012-11-25'),
(1, '2013-06-18'),
(1, '2014-02-16'),
(1, '2015-05-17')
the 2011 to 2012 jump would be a lapse, as March of 2011 would not be consecutive to November of 2012. The next fiscal year after March 2011 would be 07/2011 to 6/2012. Then a new one would be 07/2012 to 06/2013.
Okay, I see where the problem was. I thought I had a lock on seeing where a gap was, but alas, it was faulty. I had to come up with a better method, so I decided to look at ALL YEARS in the span of all the data, and take the maximum year that doesn't appear in the data, and require all selected years to be larger than that value, or greater than the minimum year minus 1 if there are no gaps.
Here's the updated code:
DECLARE @THE_DATE AS date = DATEADD(dd, -30, GETDATE());
DECLARE @CURRENT_YEAR AS int = CASE WHEN MONTH(@THE_DATE) > 6 THEN YEAR(@THE_DATE) + 1 ELSE YEAR(@THE_DATE) END;
DECLARE @MIN_YEAR AS int, @MAX_YEAR AS int;
SELECT @CURRENT_YEAR AS CURRENT_YEAR
DECLARE @INPUT_DATA AS TABLE (
ID INT NOT NULL,
[Date] DATE NOT NULL
);
INSERT INTO @INPUT_DATA (ID, [Date]) VALUES
(1, '2000-03-31'),
(1, '2000-04-24'),
(1, '2001-02-10'),
(1, '2001-03-06'),
(1, '2001-03-09'),
(1, '2010-05-11'),
(1, '2011-03-25'),
(1, '2012-11-25'),
(1, '2013-06-18'),
(1, '2014-02-16'),
(1, '2015-05-17'),
/*(1, '2000-05-03'),
(1, '2001-06-10'),
(1, '2002-04-02'),
(1, '2005-07-29'),
(1, '2010-12-15'),
(1, '2015-04-15'),
(1, '2014-06-15'),
(1, '2013-05-12'),*/
(4, '2001-05-07'),
(4, '1999-08-01'),
(4, '2000-07-05'),
(4, '2001-08-01'),
(5, '2013-06-01'),
(5, '2014-06-01'),
(5, '2015-06-01'),
(9, '2002-05-01'),
(9, '2000-04-02');
DECLARE @TRANSFORMED AS TABLE (
ID INT,
FISCAL_YEAR INT,
RN INT
);
INSERT INTO @TRANSFORMED (ID, FISCAL_YEAR, RN)
SELECT D.ID, CASE WHEN MONTH(D.[Date]) > 6 THEN YEAR(D.[Date]) + 1 ELSE YEAR(D.[Date]) END AS FISCAL_YEAR,
DENSE_RANK() OVER(PARTITION BY D.ID ORDER BY CASE WHEN MONTH(D.[Date]) > 6 THEN YEAR(D.[Date]) + 1 ELSE YEAR(D.[Date]) END) AS RN
FROM @INPUT_DATA AS D;
SELECT @MIN_YEAR = MIN(FISCAL_YEAR), @MAX_YEAR = MAX(FISCAL_YEAR)
FROM @TRANSFORMED;
SELECT *
FROM @TRANSFORMED
ORDER BY ID, FISCAL_YEAR;
WITH YEARS AS (
SELECT @MIN_YEAR AS FISCAL_YEAR
UNION ALL
SELECT FISCAL_YEAR + 1
FROM YEARS
WHERE FISCAL_YEAR + 1 <= @MAX_YEAR
)
SELECT T1.ID, ISNULL(NULLIF(COUNT(DISTINCT T2.FISCAL_YEAR), 0) + 1, 0) AS CONSECUTIVE_YEARS
FROM @TRANSFORMED AS T1
LEFT OUTER JOIN @TRANSFORMED AS T2
ON T1.ID = T2.ID
AND T1.FISCAL_YEAR = T2.FISCAL_YEAR - 1
AND T1.FISCAL_YEAR >
ISNULL(
(
SELECT MAX(Y.FISCAL_YEAR)
FROM YEARS AS Y
LEFT OUTER JOIN @TRANSFORMED AS T3
ON Y.FISCAL_YEAR = T3.FISCAL_YEAR
AND T3.ID = T1.ID
WHERE T3.ID IS NULL
),
(
SELECT MIN(T4.FISCAL_YEAR)
FROM @TRANSFORMED AS T4
WHERE T4.ID = T1.ID
) - 1)
GROUP BY T1.ID
HAVING MAX(T2.FISCAL_YEAR) = @CURRENT_YEAR;
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 46 through 46 (of 46 total)
You must be logged in to reply to this topic. Login to reply