Which years are similar.
-
For fun and learning only.
===================
===================Which years are similar to a specific year.
For example from which years can the calendar be used for this year (2019)?
When can I use the 2019 calendar again ?If an auxilary calendar table is available this can be used.
Creative as this group is, I would like to learn from the diversity of different sollutions.
This is for fun and learning only. (The solution itself has no value).
Thanks for your time and attention,
Ben -
What do you mean "similar"? Like World events? Is 2020 similar to 2016 because they both hosted Olympics? Is 2014 similar to 1990 because Germany won the FIFA World Cup both years?
I've really no idea what you mean by "from which years can the calendar be used for this year". Could you try to re-explain your question please?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Is 2014 similar to 1990 because Germany won the FIFA World Cup both years?
Yeah that is ok, your choice.More obvious would be: Dates fall om the same weekday and that the year has the same number of days.
But as this is for fun and learning I am open to other similarities which can be resolved. And does not have to be limited the Georgian calendar.Ben
-
Gregorian calendar began in 1582. The sql date functions will return values with dates < 1582, but would probably not be meaningful.
Here's the solution I came up with.DECLARE @Date date = '1900-01-01'
IF OBJECT_ID('tempdb..#Dates','U') IS NOT NULL
DROP TABLE #Dates
CREATE TABLE #Dates
(YrBeg date
,Days int
,DoW int
)
WHILE @Date < '2100-01-01'
BEGIN
INSERT INTO #Dates
SELECT @Date AS YrBeg
,DATEDIFF(DAY,@Date,DATEADD(DAY,-1,DATEADD(YEAR,1,@Date)))+1 AS Days
,DATEPART(DW,@Date) AS DoW
SET @Date = DATEADD(YEAR,1,@Date)
END
SELECT * FROM #Dates
ORDER BY Days, Dow, YrBeg
-
Here's a code that will list 200 years starting on 1900 and group them by calendar variation. You can have 14 possible calendars, 2 for each day of the week in which one is for leap years and the other is for regular years.
WITH
E(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT TOP(200) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) - 1 n
FROM E4
)
SELECT n+1900 AS [year],
DENSE_RANK() OVER( ORDER BY
DATEPART( DW, DATEADD( YY, n, 0)),
DATEPART( DW, DATEADD( YY, n+1, -1)),
DATEPART( DY, DATEADD( YY, n+1, -1))) AS YearGroup
FROM cteTally
ORDER BY YearGroup, N; - ben.brugman - Tuesday, February 5, 2019 2:29 AM
Please post DDL and follow ANSI/ISO standards when asking for help.
-
I took a slightly different approach. While solving the original problem certainly has merit, I can also foresee people wanting to solve problems like "Show me all years that begin on a Monday" and "Show me all years that have 366 days" (which are always Leap Years), etc, etc. It also makes the information to make those decisions visible to provide the user with a case of the "nice warm fuzzies" that things are working correctly. I also did all years within the limits of what SQL Server/T-SQL recognizes as the Gregorian Calendar (1753-9999).
Here's the code (apologies for the forum software messing up the visible indentation)...
/********************************************************************************************
Purpose:
Create a list of all years in the Gregorian Calendar that SQL Server recognizes along with
the numeric day of the week (Monday = 1) the year starts on and whether or not the year is
a leap year (unlike so many other applications, T-SQL actuallydoes this correctly). The
resulting StartDate and IsLeapYear columns have been combined to easily find "similar" years
that start on the same day and have the same number of days in the year.Dependencies:
1. This code uses a "Tally" function that returns a sequence of numbers from either 0 or 1
up to and including a given number. It will work for all versions of SQL Server for 2005
and up. For earlier versions, use a "Tally" table that starts at 0 and move that
functionality from the CTE to a FROM clause.Programmer Notes:
1. If the output from this code is going to be used frequently, it could be better to store
it in a table properly indexed for the queries the table would need to support. As with
any table, Logical Reads will be higher but CPU and duration would likely be lower.2. If you never need the DayStart or IsLeapYear values, simply delete them from the final
SELECT in the code.3. Note that the datatypes for the columns have been "right-sized". If you need some other
datatype for the columns, simply change the CONVERTs. Also note that there's no byte
savings in converting IsLeapYear to a BIT data type in this case because 1 BIT data type
still requires a full byte for storage. If the code will be used from a .NET front end,
using a BIT data type may have an advantage because it can be auto-magicall interpreted
as a BOOLEAN by the front end.4. The ISNULLs simply create a Non-NULLable column if the output is used by a SELECT/INTO
to create and populate a table on the fly.Revision History:
Rev 00 - Jeff Moden - 06 Feb 2019
********************************************************************************************/
WITH cteGenStartDates AS
(--==== Create a date for the first of every year.
SELECT StartDate = DATEADD(yy,t.n,'1753')
FROM dbo.fnTally(0,DATEDIFF(yy,'1753','9999'))t
)
, cteYearTypeInfo AS
(--==== Determine the StartDate and IsLeapYear determine similar years.
-- This also "DRY"s out the calculations for the upcoming grouping.
SELECT YYYY = ISNULL(CONVERT(SMALLINT,DATEPART(YY,StartDate)),0)
,StartDay = ISNULL(CONVERT(TINYINT,DATEDIFF(DD,'1753',StartDate)%7+1),0)
,IsLeapYear = ISNULL(CONVERT(TINYINT,SIGN(DATEPART(DD,DATEADD(dd,59,StartDate))-1)),0)
FROM cteGenStartDates
)--==== Create the final output including the grouping to find similar years.
SELECT YYYY
,StartDay
,IsLeapYear
,YearTypeGroup = ISNULL(CONVERT(TINYINT,IsLeapYear*10+StartDay),0)
FROM cteYearTypeInfo
;Partial Results;:
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks all, I'll give this a go tomorrow.
I used an auxiliary calendar and picked up all combinations of days of the 28 of February and the 1st of march.
This would give the correct results for most years of the Western (Julian and Gregorian) calendar except for the years the Julian calendar was replaced by the Gregorian calendar (Location depended, where I live the Gregorian calendar was adopted on 12 January 1583).
Also the year from the calendar below would be represented correctly. But there is no reuse of that calendar as far as I know.
This is the only calendar I know of containing a 30th of February. (1712 Sweden).So thanks for your responses. I'll try them tomorrow.
Ben
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply