November 17, 2015 at 7:09 am
Hello, this is a simplified sample to demonstrate the problem I am having:
CREATE TABLE #Sample (ID INT NOT NULL, Yr INT NOT NULL, Col_A VARCHAR(10), Col_B INT, Col_C INT)
INSERT INTO #Sample values
(1, 2004, 'N/S', 10, 255),
(1, 2005, 'N/S', 10, 255),
(1, 2006, 'N/S', NULL, 1),
(1, 2007, 'N/S', 10, 255),
(1, 2008, 'N/S', 10, 255),
(1, 2009, 'N/S', 10, 255),
(1, 2010, 'N/S', 0, 255),
(1, 2011, 'N/A', 10, 255),
(1, 2012, 'N/A', 10, 255),
(1, 2013, 'N/A', 0, 0),
(1, 2014, 'Y', 101, 256),
(1, 2015, 'N/S', 1, 32),
(1, 2016, 'N/A', NULL,NULL );
SELECT CASE WHEN MIN(YR )=MAX(YR) THEN CAST(MIN(YR) AS CHAR(4)) ELSE CAST(MIN(YR ) AS CHAR(4))+'-'+RIGHT(CAST(MAX(YR) AS CHAR(4)),2) END AS FROMYEAR_TOYEAR,
ID , Col_A , Col_B , Col_C
FROM #Sample GROUP BY
ID , Col_A , Col_B , Col_C ORDER BY 1
RESULTS:
FROMYEAR_TOYEARIDCol_ACol_BCol_C
2004-091N/S 10 255
20061N/S NULL 1
20101N/S 0 255
2011-121N/A 10 255
20131N/A 0 0
2014 1Y 101 256
20151N/S 1 32
20161N/A NULL NULL
CORRECT RESULTS SHOULD BE:
FROMYEAR_TOYEARIDCol_ACol_BCol_C
2004-051N/S 10 255
20061N/S NULL 1
2007-091N/S 10 255
20101N/S 0 255
2011-121N/A 10 255
20131N/A 0 0
2014 1Y 101 256
20151N/S 1 32
20161N/A NULL NULL
Please notice that periods from 2004 to 2009 though having same data have to be broken down, as the year in between(2006) had different data. I was trying to process the records by using windowing functions to break the group with the gap in years but as of now I don't have a viable solution...It seems to me there should be a way to do it in a single select statement...
Thank you for your help in advance
P.S. - I've gotten a reply and a reference to this time of topics under "Islands and Gaps" problem. Consider this closed. thank you all for your consideration
November 17, 2015 at 10:30 am
This sounds like the same formatting issue that I proposed a solution for in the thread on store hours.
See if you can adjust that approach to your current situation.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 17, 2015 at 1:26 pm
Just if anyone wants an example that is not as complex as the one from Drew (that problem required a more complex solution than this one).
WITH CTE AS(
SELECT *, Yr - ROW_NUMBER() OVER( PARTITION BY ID, Col_A, Col_B, Col_C ORDER BY Yr) InitialYr
FROM #Sample
)
SELECT CASE WHEN MIN(YR )=MAX(YR) THEN CAST(MIN(YR) AS CHAR(4)) ELSE CAST(MIN(YR ) AS CHAR(4))+'-'+RIGHT(CAST(MAX(YR) AS CHAR(4)),2) END AS FROMYEAR_TOYEAR,
ID , Col_A , Col_B , Col_C
FROM CTE
GROUP BY InitialYr, ID , Col_A , Col_B , Col_C
ORDER BY FROMYEAR_TOYEAR;
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply