February 16, 2012 at 7:54 am
Hello,
I am working with cars. I have 3 columns from a raw import. FirstYear, LastYear, Make, and Model.
Records look like:
1950 1951 VW Beetle
1950 1960 VW Beetle
1955 1975 VW Beetle
1955 1955 VW Beetle
1998 2000 VW Beetle
I need to get the MIN FirstYear and MAX LastYear and insert into another table so I get a Year,Make and Model for each year. I can do that no problem but the issue I have is when it skips a year (or years).
I shouldn't (in the example above) have any Year,Make,Models from 1975 - 1998
so i would have
1950 vw beetle
1951 vw beetle
etc to
1975 vw beetle
1998 vw beetle
When I do my loop, I am adding false records between 1975 and 1998.
Any thoughts?
Thank you!
Tony
February 16, 2012 at 8:09 am
Look at this "Spackle" article by Jeff Moden, I think it might do what you want to do.
February 16, 2012 at 8:17 am
BEGIN TRAN
--Sample data
CREATE TABLE #yourTable (FirstYear INT, LastYear INT, Make CHAR(2), Model CHAR(6))
INSERT INTO #yourTable
SELECT FirstYear, LastYear, Make, Model
FROM (VALUES(1950, 1951, 'VW', 'Beetle'),(1950, 1960, 'VW', 'Beetle'),
(1955, 1975, 'VW', 'Beetle'),(1955, 1955, 'VW', 'Beetle'),
(1998, 2000, 'VW', 'Beetle'))a(FirstYear, LastYear, Make, Model)
--Actual solution
;WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
t4(N) AS (SELECT 1 FROM t3 x, t3 y),
Tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM t4 x, t4 y)
SELECT Years, Make, Model
FROM #yourTable
CROSS APPLY (SELECT FirstYear + N
FROM Tally
WHERE LastYear >= FirstYear + N) tally(Years)
GROUP BY Years, Make, Model
ORDER BY Years;
ROLLBACK
Returns: -
Years Make Model
-------------------- ---- ------
1950 VW Beetle
1951 VW Beetle
1952 VW Beetle
1953 VW Beetle
1954 VW Beetle
1955 VW Beetle
1956 VW Beetle
1957 VW Beetle
1958 VW Beetle
1959 VW Beetle
1960 VW Beetle
1961 VW Beetle
1962 VW Beetle
1963 VW Beetle
1964 VW Beetle
1965 VW Beetle
1966 VW Beetle
1967 VW Beetle
1968 VW Beetle
1969 VW Beetle
1970 VW Beetle
1971 VW Beetle
1972 VW Beetle
1973 VW Beetle
1974 VW Beetle
1975 VW Beetle
1998 VW Beetle
1999 VW Beetle
2000 VW Beetle
February 16, 2012 at 8:45 am
Cadavre,
You ARE a GENIUS! That is exactly what I am looking for.
I can follow it a little but would you be kind enough to explain what is happening in the WITH clause (up to the select).
Thank you!!!!!!!!
February 16, 2012 at 8:47 am
tcarcieri (2/16/2012)
Cadavre,You ARE a GENIUS! That is exactly what I am looking for.
I can follow it a little but would you be kind enough to explain what is happening in the WITH clause (up to the select).
Thank you!!!!!!!!
Check out this article http://www.sqlservercentral.com/articles/T-SQL/62867/ then this http://www.sqlservercentral.com/articles/Tally+Table/72993/. It will explain everything 🙂
Jared
CE - Microsoft
February 16, 2012 at 8:50 am
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),
t2(N) AS (SELECT 1 FROM t1 x, t1 y),
t3(N) AS (SELECT 1 FROM t2 x, t2 y),
t4(N) AS (SELECT 1 FROM t3 x, t3 y),
Tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM t4 x, t4 y)
The common table expression above just creates a 0 based tally table (a table of numbers from 0 to 65,536 which I figured would give you enough years). You could achieve the same by removing the whole of the CTE and instead CROSS APPLY to a physical table.
A warning though, I'm not sure how it'll perform against bigger data-sets as it is creating lots of duplicate data that gets filtered out by the GROUP BY clause at the end. Probably a better way of doing it, but this should get you started.
February 16, 2012 at 9:42 am
Thank you for the clarification.
So if I remove the t4(N) line, would I reduce the number of years by a factor of 10? Thereby limiting to just 6K years? I only need like 200 years.
Thank you,
Tony
February 16, 2012 at 9:48 am
Ahh nevermind...if I remove the last line, i would get 257 years. I think that is sufficient.
February 16, 2012 at 10:27 am
tcarcieri (2/16/2012)
Thank you for the clarification.So if I remove the t4(N) line, would I reduce the number of years by a factor of 10? Thereby limiting to just 6K years? I only need like 200 years.
Thank you,
Tony
Apologies.
WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1), --0 to 4
t2(N) AS (SELECT 1 FROM t1 x, t1 y), --0 to 16
t3(N) AS (SELECT 1 FROM t2 x, t2 y), --0 to 256
t4(N) AS (SELECT 1 FROM t3 x, t3 y), --0 to 65536
Tally(N) AS (SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM t1 x, t1 y)
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply