July 1, 2015 at 2:47 pm
When that piece is included, there is a red squiggle line under the ROWS bit. When that is taken out and the query is parsed, it is telling me that I have an issue near order.
July 1, 2015 at 2:51 pm
Jason A. Long (7/1/2015)
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?
The problem could be using SQL Server 2012 features on SQL Server 2008.
July 2, 2015 at 6:21 am
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.
Given the sample data, there are no qualifying rows. As to what results you get from my query when run against your actual data, I have no way to know. However, my latest query does not use any windowed functions, so the PARTITION BY keyword isn't there, and thus I'm not sure why you mentioned it. Maybe you're starting to lump all the responses together... (been there, done that 🙂 ).
Anyway, one other possible reason is the definition for your fiscal years. The question is, which of the following one year periods constitutes fiscal 2015?
1.) July 1st, 2014 to June 30th, 2015, or
2.) July 1st, 2015 to June 30th, 2016
My query assumes that the first of those 2 choices is reality. Let me know if it's the latter of the two and I can adjust the query and re-post.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 2, 2015 at 7:56 pm
Maybe a slightly different approach will get a few ideas thrown around.
WITH SampleData (ID, MyDate) as
(
select 1, cast('2000-05-03' as datetime) union all
select 1, '2001-06-10' union all
select 1, '2002-04-02' union all
select 1, '2005-07-29' union all
select 1, '2010-12-15' union all
select 4, '2001-05-07' union all
select 4, '1999-08-01' union all
select 4, '2000-07-05' union all
select 4, '2001-08-01' union all
select 9, '2002-05-01' union all
select 9, '2000-04-02'
)
SELECT ID, c=MAX(c)
FROM
(
SELECT ID, c=CASE WHEN COUNT(*) = 1 THEN 0 ELSE COUNT(*) END
FROM
(
SELECT ID, MyDate, g=fy-ROW_NUMBER() OVER (PARTITION BY ID ORDER BY fy)
FROM
(
SELECT *, rn=ROW_NUMBER() OVER (PARTITION BY ID, fy ORDER BY MyDate)
FROM SampleData a
CROSS APPLY
(
SELECT CASE WHEN MyDate <= LEFT(YEAR(MyDate), 4) + '-06-30'
THEN YEAR(MyDate)
ELSE YEAR(MyDate) + 1
END
) b (fy)
) a
WHERE rn=1
) a
GROUP BY ID, g
) a
GROUP BY ID
ORDER BY ID;
Edit: Note that I'm not sure I fully understand all the requirements, so it is unlikely this is a perfect solution. Just an idea generator is all.
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
July 5, 2015 at 11:27 am
Jason, not sure what you are asking. When that piece is added back I am getting a red squiggle line under the ROWS bit. UPDATE*** I removed the order by completely where this the ROWS bit and now it is pulling data but everything is coming back as 0.
July 6, 2015 at 7:51 am
Ok, started playing with this a little more over the 4th. I have come up with the following and it is getting the count of years based on a calendar year count where there is a record in the current calendar year. I am looking to get it into fiscal year running from 07/01/xxxx to 06/30/xxxx, where there is participation in this current fiscal year and then counting the consecutive fiscal years of participation. If there is a lapse in participation that will end the count. Some sample data is below along with what I have come up with so far. If additional information would help, please let me know. Based on the data presented I am expecting a count of 3 for id 1.
with Something as
(
select 1 as ID, cast('2000-05-03' as datetime) as MyDate union all
select 1, '2001-06-10' union all
select 1, '2002-04-02' union all
select 1, '2005-07-29' union all
select 1, '2010-12-15' union all
select 1, '2015-04-15' union all
select 1, '2014-06-15' union all
select 1, '2013-05-12' union all
select 4, '2001-05-07' union all
select 4, '1999-08-01' union all
select 4, '2000-07-05' union all
select 4, '2001-08-01' union all
select 9, '2002-05-01' union all
select 9, '2000-04-02'
)
With Years
AS
(select ROW_NUMBER()
OVER (partition by
year(MyDate) order by id
) as RowNumber,
id, year(MyDate) as yearpart
from Something
)
select id, sum(case
when RowNumber = 1 then 1
else 0
end) as yearcount
from Years
group by id
having max(yearpart) = 2015
July 6, 2015 at 11:34 am
OLSONEJ (7/6/2015)
Ok, started playing with this a little more over the 4th. I have come up with the following and it is getting the count of years based on a calendar year count where there is a record in the current calendar year. I am looking to get it into fiscal year running from 07/01/xxxx to 06/30/xxxx, where there is participation in this current fiscal year and then counting the consecutive fiscal years of participation. If there is a lapse in participation that will end the count. Some sample data is below along with what I have come up with so far. If additional information would help, please let me know. Based on the data presented I am expecting a count of 3 for id 1.with Something as
(
select 1 as ID, cast('2000-05-03' as datetime) as MyDate union all
select 1, '2001-06-10' union all
select 1, '2002-04-02' union all
select 1, '2005-07-29' union all
select 1, '2010-12-15' union all
select 1, '2015-04-15' union all
select 1, '2014-06-15' union all
select 1, '2013-05-12' union all
select 4, '2001-05-07' union all
select 4, '1999-08-01' union all
select 4, '2000-07-05' union all
select 4, '2001-08-01' union all
select 9, '2002-05-01' union all
select 9, '2000-04-02'
)
With Years
AS
(select ROW_NUMBER()
OVER (partition by
year(MyDate) order by id
) as RowNumber,
id, year(MyDate) as yearpart
from Something
)
select id, sum(case
when RowNumber = 1 then 1
else 0
end) as yearcount
from Years
group by id
having max(yearpart) = 2015
My query still returns 0 rows, because none of your sample data is in FISCAL YEAR 2015, which at the moment I'm defining at 7/1/2015 through 6/30/2016. If FISCAL 2015 is instead 7/1/2014 through 6/30/2015, then I need to just subtract 1 from the fiscal year I'm generating. You'll have to let me know which of those two you need to operate on. Also, you previously mentioned that you need the condition to include the current fiscal year, but this most recent post appears to suggest current calendar year, so please clarify.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2015 at 11:41 am
sg, my apologies for the confusion.
This will operate based on fiscal year 07-01-2014 to 06-30-2015. I am in need of a count of consecutive fiscal years if the person has participation in this fiscal year and has previous participation in preceding fiscal years up until a lapse or the beginning of their participation. The query I included was for calendar year as my thought was to start basic and expand as I went along since I have never had recourse to do a count like this before.
July 6, 2015 at 1:41 pm
OLSONEJ (7/6/2015)
sg, my apologies for the confusion.This will operate based on fiscal year 07-01-2014 to 06-30-2015. I am in need of a count of consecutive fiscal years if the person has participation in this fiscal year and has previous participation in preceding fiscal years up until a lapse or the beginning of their participation. The query I included was for calendar year as my thought was to start basic and expand as I went along since I have never had recourse to do a count like this before.
Okay, I'm assuming your stated range is FISCAL 2015. This is a gap problem, and my previous query would not account for gaps. Also, FISCAL 2015 ended just short of a week ago, so I adjusted the date for the overall query to go back into June so that I wouldn't need to adjust the sample data. Please test this one and let me know:
EDIT: Updated the code to handle the possiblility that there are 0 records without an adjacency, and added the sample data for ID value 5 to test it.
DECLARE @THE_DATE AS date = DATEADD(dd, -10, GETDATE());
DECLARE @CURRENT_YEAR AS int = CASE WHEN MONTH(@THE_DATE) > 6 THEN YEAR(@THE_DATE) + 1 ELSE YEAR(@THE_DATE) END;
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-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 *
FROM @TRANSFORMED
ORDER BY ID, FISCAL_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.RN >
ISNULL(
(SELECT MAX(T3.RN)
FROM @TRANSFORMED AS T3
WHERE T3.ID = T1.ID
AND T3.FISCAL_YEAR NOT IN
(
SELECT T4.FISCAL_YEAR
FROM @TRANSFORMED AS T4
INNER JOIN @TRANSFORMED AS T5
ON T4.ID = T5.ID
AND T5.FISCAL_YEAR IN (T4.FISCAL_YEAR - 1, T4.FISCAL_YEAR + 1)
)
), 0)
GROUP BY T1.ID
HAVING MAX(T2.FISCAL_YEAR) = @CURRENT_YEAR
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2015 at 3:19 pm
Please see my updated code above, as I realized there was a condition that could break it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 6, 2015 at 6:12 pm
I will need to hit some more ids against it but this appears to hold up to my small amount of unit checks. I will be able to bump it more tomorrow and will go from there. Thanks for the help on this one! Definitely appreciated.
July 9, 2015 at 12:48 pm
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.
July 9, 2015 at 1:23 pm
Coming back to see what's happening and I find myself lost. What exactly are you trying to return? The maximum number of consecutive years? The number consecutive years (three islands with gaps between them for example)?
July 9, 2015 at 1:27 pm
The count of years in the most recent of the 3 islands based on my most recent sample data example.
July 9, 2015 at 3:16 pm
OLSONEJ (7/9/2015)
The count of years in the most recent of the 3 islands based on my most recent sample data example.
In this case, wouldn't an island of one year count if it is the most recent island for a specific ID?
Viewing 15 posts - 31 through 45 (of 46 total)
You must be logged in to reply to this topic. Login to reply