June 30, 2015 at 9:42 am
My apologies, I was not aware of the business rules previously as it had to be decided upon since we are rebuilding something that previously existed. Same reason I am using the cursor it is part of the old methodology. The years are calculated based on the fiscal year which runs from, for example, 07-01-2014 to 06-30-2015. Again my apologies as the previously provided data may not match up to that but can be used as a starting point. This was my first post and I did the best I could to present things in a way which would give a ground to work from. In future posts I will be more explicit.
June 30, 2015 at 10:06 am
OLSONEJ (6/30/2015)
My apologies, I was not aware of the business rules previously as it had to be decided upon since we are rebuilding something that previously existed. Same reason I am using the cursor it is part of the old methodology. The years are calculated based on the fiscal year which runs from, for example, 07-01-2014 to 06-30-2015. Again my apologies as the previously provided data may not match up to that but can be used as a starting point. This was my first post and I did the best I could to present things in a way which would give a ground to work from. In future posts I will be more explicit.
Okay. Now that we have the FISCAL YEAR as the basis, and going from 7/1 to 6/30, here's a try that produces a value of 3 for ID values 1 and 4, and 0 for ID value 9:
DECLARE @INPUT_DATA AS TABLE (
ID INT NOT NULL,
[Date] DATE NOT NULL
);
INSERT INTO @INPUT_DATA (ID, [Date]) VALUES
(1, '2000-05-03'),
(1, '2001-06-10'),
(1, '2002-04-02'),
(1, '2005-07-29'),
(1, '2010-12-15'),
(4, '2001-05-07'),
(4, '1999-08-01'),
(4, '2000-07-05'),
(4, '2001-08-01'),
(9, '2002-05-01'),
(9, '2000-04-02');
WITH TRANSFORMED AS (
SELECT D.ID, CASE WHEN MONTH(D.[Date]) < 7 THEN YEAR(D.[Date]) - 1 ELSE YEAR(D.[Date]) END AS FISCAL_YEAR
FROM @INPUT_DATA AS D
)
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
GROUP BY T1.ID
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 30, 2015 at 10:53 am
What if we were to add "(1, '2006-10-05')," to the data set?
That would produce the following fiscal years for ID #1...
ID FISCAL_YEAR
----------- -----------
1 1999
1 2000
1 2001
1 2005
1 2006
1 2010
In this case 1999, 2000 & 2001 are consecutive and 2005 & 2006 are consecutive... Which begs the question... What's the expected "Consecutive Count" in this scenario?
June 30, 2015 at 11:45 am
OLSONEJ (6/30/2015)
My apologies, I was not aware of the business rules previously as it had to be decided upon since we are rebuilding something that previously existed. Same reason I am using the cursor it is part of the old methodology. The years are calculated based on the fiscal year which runs from, for example, 07-01-2014 to 06-30-2015. Again my apologies as the previously provided data may not match up to that but can be used as a starting point. This was my first post and I did the best I could to present things in a way which would give a ground to work from. In future posts I will be more explicit.
That's good to know but we still have the problem of the 1 day = 1 year scenario. In this case, it would be 06-30-2015 to 07-01-2015. Just because they crossed the threshold of the fiscal year, it's still only 1 day. Are you sure that you want to give them credit for a year???
--Jeff Moden
Change is inevitable... Change for the better is not.
July 1, 2015 at 9:32 am
The solution presented works to a point. An additional point of criteria, that I was only informed of this morning, is that we only want to count as consecutive those entries that are consecutive through the current fiscal year. Meaning that if there is a gap throughout the years where they were previously consecutive and then lapsed that those previously consecutive years would not be counted.
July 1, 2015 at 11:15 am
OLSONEJ (7/1/2015)
The solution presented works to a point. An additional point of criteria, that I was only informed of this morning, is that we only want to count as consecutive those entries that are consecutive through the current fiscal year. Meaning that if there is a gap throughout the years where they were previously consecutive and then lapsed that those previously consecutive years would not be counted.
Okay, how about this query instead (it doesn't have good sample data, so no rows are returned - you can create better sample data):
DECLARE @THE_DATE AS date = GETDATE();
DECLARE @CURRENT_YEAR AS int = CASE WHEN MONTH(@THE_DATE) < 7 THEN YEAR(@THE_DATE) - 1 ELSE YEAR(@THE_DATE) END;
DECLARE @INPUT_DATA AS TABLE (
ID INT NOT NULL,
[Date] DATE NOT NULL
);
INSERT INTO @INPUT_DATA (ID, [Date]) VALUES
(1, '2000-05-03'),
(1, '2001-06-10'),
(1, '2002-04-02'),
(1, '2005-07-29'),
(1, '2010-12-15'),
(1, '2006-10-05'),
(4, '2001-05-07'),
(4, '1999-08-01'),
(4, '2000-07-05'),
(4, '2001-08-01'),
(9, '2002-05-01'),
(9, '2000-04-02');
WITH TRANSFORMED AS (
SELECT D.ID, CASE WHEN MONTH(D.[Date]) < 7 THEN YEAR(D.[Date]) - 1 ELSE YEAR(D.[Date]) END AS FISCAL_YEAR
FROM @INPUT_DATA AS D
)
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
GROUP BY T1.ID
HAVING MAX(T2.FISCAL_YEAR) = @CURRENT_YEAR
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 1, 2015 at 11:44 am
You can try this as well...
IF OBJECT_ID('tempdb..#temp') IS NOT NULL
DROP TABLE #temp;
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');
DECLARE @CerrentFiscalYear INT = CASE WHEN MONTH(CURRENT_TIMESTAMP) < 7 THEN YEAR(CURRENT_TIMESTAMP) - 1 ELSE YEAR(CURRENT_TIMESTAMP) END;
WITH DistinctFiscalYears AS (
SELECT
t.ID,
CASE WHEN MONTH(t.Date) < 7 THEN YEAR(t.Date) - 1 ELSE YEAR(t.Date) END AS FiscalYear
FROM
#temp t
GROUP BY
t.ID,
CASE WHEN MONTH(t.Date) < 7 THEN YEAR(t.Date) - 1 ELSE YEAR(t.Date) END
), FiscalYearsConsecFromCurrent AS (
SELECT
dfy1.ID,
NULLIF(MIN(COALESCE(dfy2.FiscalYear + 1, 0)) OVER (PARTITION BY dfy1.ID ORDER BY dfy1.FiscalYear desc ROWS UNBOUNDED PRECEDING), 0) AS ConsecYears
FROM
DistinctFiscalYears dfy1
LEFT JOIN DistinctFiscalYears dfy2
ON dfy1.ID = dfy2.ID
AND dfy1.FiscalYear = dfy2.FiscalYear +1
)
SELECT
fc.ID,
ISNULL(NULLIF(COUNT(fc.ConsecYears), 0) + 1, 0) AS ConsecYears
FROM
FiscalYearsConsecFromCurrent fc
GROUP BY
fc.ID
Steve - When I ran your solution against the test data I created, it count 1 too many for ID 1 due to the 1st 2 rows being concurrent...
July 1, 2015 at 12:44 pm
I am not getting any results where I expect them with your query sg. I am not sure why as I would say it is sound by looking at it. I am not at all familiar with partitioning. I don't mind using it but I would want to do more research before using it in anything.
July 1, 2015 at 12:51 pm
OLSONEJ (7/1/2015)
I am not getting any results where I expect them with your query sg. I am not sure why as I would say it is sound by looking at it. I am not at all familiar with partitioning. I don't mind using it but I would want to do more research before using it in anything.
This isn't table partitioning. It is partitioning an aggregate or window function. Very different concept. https://msdn.microsoft.com/en-us/library/ms189461.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2015 at 1:29 pm
My editor is not liking the Rows or the order by part. I am trying to run this as query.
July 1, 2015 at 1:40 pm
OLSONEJ (7/1/2015)
My editor is not liking the Rows or the order by part. I am trying to run this as query.
The query that was posted will not work on anything older than sql 2012. I am guessing that Jason did not realize this is in the 2008 forum which means that query won't work for you. 😉 If nobody else posts an update I will try to look at this in the morning.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 1, 2015 at 1:42 pm
Yep, that would explain it. I am in 08 R2. Thanks!
July 1, 2015 at 2:07 pm
Sean Lange (7/1/2015)
OLSONEJ (7/1/2015)
My editor is not liking the Rows or the order by part. I am trying to run this as query.The query that was posted will not work on anything older than sql 2012. I am guessing that Jason did not realize this is in the 2008 forum which means that query won't work for you. 😉 If nobody else posts an update I will try to look at this in the morning.
DOH!!! Yea, I knew about it being SQL 2008 (which is why I used the self join instead of a LAG function), I just brain farted on the window frame. Sorry about that.
Fortunately, "ROWS UNBOUNDED PRECEDING" is the default behavior when not specified. So simply omitting those three words should allow the code to work in 2008 R2...
(IIRC, windowed aggregates were introduced in 2005... I think...)
NULLIF(MIN(COALESCE(dfy2.FiscalYear + 1, 0)) OVER (PARTITION BY dfy1.ID ORDER BY dfy1.FiscalYear DESC), 0) AS ConsecYears
I don't have access to a copy of 2008 so I can't verify in a 2008 environment but the modified query continued to produce the expected results in 2014.
July 1, 2015 at 2:12 pm
Wouldn't you know that since the ROWS was giving me a red line I removed that part but it still did not like it 🙂
July 1, 2015 at 2:44 pm
OLSONEJ (7/1/2015)
Wouldn't you know that since the ROWS was giving me a red line I removed that part but it still did not like it 🙂
The word "ROWS" or "ROWS UNBOUNDED PRECEDING"? What does that line look like in it's current state?
Viewing 15 posts - 16 through 30 (of 46 total)
You must be logged in to reply to this topic. Login to reply