April 20, 2010 at 10:50 am
Hi, I have a date field in a table that I want to use to generate quarterly reports on. I just need to find out the earliest year that the date field holds (using MIN i guess!) and the latest year in that date field (using MAX i guess!) and generate the years in between so that I can then go on to CROSS JOIN that with quarters (i.e. Numbers 1 to 4)... That's what I've got planned anyway... there might be an easier way to do this.
I currently have this setup using a disparate "years table" and a "quarters table" which is ok for now but isn't a long-term solution for obvious reasons.
So my question is... how can I generate the 'middle' years and quarters so that only the relevant values are displayed?
April 20, 2010 at 11:34 am
Can you put together a test suite; table(s) (CREATE TABLE statement(s)), sample data (INSERT INTO statements) for the table(s) involved, and expected results that demonstrates what you are attempting to achieve?
April 20, 2010 at 11:44 am
You can use this example for how to create the missing years. If you can't get it working with this, please do as Lynn suggests, and give us table structures (CREATE TABLE statement), put some data into those tables (via INSERT statement(s)), and the expected results you want based off of that sample data.
-- See how this starts off with a table and data in it?
-- If you had provided us the data in this format,
-- it would have made things easier for all of the
-- volunteers on this site to help you out.
declare @temp table (MyDateField datetime);
insert into @temp
select '20100101' UNION ALL
select '20150101';
declare @min-2 datetime,
@max-2 datetime,
@Age int;
select @min-2 = MIN(MyDateField),
@max-2 = MAX(MyDateField)
from @temp;
select @Age = DATEDIFF(year, @min-2, @max-2);
-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)
SELECT DateField = DATEADD(year, N, @min-2)
FROM Tally
WHERE N <= @Age
UNION ALL
SELECT @min-2
order by DateField;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 20, 2010 at 12:06 pm
Personally, I've always used user-defined scalar functions to return the first day of the year or quarter for a given date.
Example:
CREATE FUNCTION fn_DATE_FirstDayOfYear
(@date DATETIME)
RETURNS DATETIME
AS
RETURN DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
END
CREATE FUNCTION fn_DATE_FirstDayOfQuarter
(@date DATETIME)
RETURNS DATETIME
AS
RETURN DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
Which allows you to:
SELECT
SUM(<SalesDollarsField>),
dbo.fn_DATE_FirstDayOfQuarter(<DateField>),
dbo.fn_DATE_FirstDayOfYear(<DateField>)
FROM
<TableName>
WHERE
<Criteria>
GROUP BY dbo.fn_DATE_FirstDayOfYear(<DateField>), dbo.fn_DATE_FirstDayOfQuarter(<DateField>)
Frankly, this never met my needs, because I needed fiscal year and fiscal quarters:
ALTER FUNCTION [dbo].[fn_DATE_FirstDayOfFYear]
(@date DATETIME, @fystart INT = 6)
RETURNS DATETIME
AS
BEGIN
RETURN CASE
WHEN DATEPART(month,(CAST(@date AS DATETIME))) < @fystart
THEN DATEADD(month,-(12-@fystart+1),DATEADD(year, DATEDIFF(yy,0,(CAST(@date AS DATETIME))),0))
ELSE DATEADD(month,@fystart-1,DATEADD(year, DATEDIFF(yy,0,(CAST(@date AS DATETIME))),0))
END
END
This is my fiscal year code. Our fiscal year starts on June 1. This code does not support fiscal years that don't begin on the first of the month.
The fiscal quarter code, however, required a lookup table by month. There may have been another way, but the lookup table was the easiest way I found. I won't go into that code, because it's kind of a mess.
That is the way I used to do things. These days, I have a time dimension table for our data warehouse that I just join against the date. The table stores lots of information about every date through an arbitrary end date that I select.
April 20, 2010 at 12:16 pm
I can do, but it is a very general question that can apply to any single date field on any database. Didn't want to over-complicate it but here goes...
CREATE TABLE [dbo].[Course_Application](
[Course_App_PK] [int] IDENTITY(1,1) NOT NULL,
[Course_App_Date] [datetime] NULL,
[Course_Choice_1] [int] NULL,
[Person_FK] [int] NULL,
[Street] [nvarchar](50) NULL,
[District] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Post_Code] [nvarchar](50) NULL,
[Tel_Day] [nvarchar](50) NULL,
[Tel_Evening] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Emergency_Contact Name] [nvarchar](50) NULL,
[Emergency_Contact_Number] [nvarchar](50) NULL,
[Friend_Partner_Name] [nvarchar](50) NULL,
[Friend_Partner_Desc] [nvarchar](50) NULL,
[Wheelchair] [bit] NULL,
[Transport_FK] [int] NULL,
[Other_Learning_Needs] [ntext] NULL,
[Six_Weeks] [bit] NULL,
[Hear_About_Course_FK] [int] NULL,
[GP_Name] [nvarchar](50) NULL,
[Age_Band_FK] [int] NULL,
[Gender_FK] [int] NULL,
[Ethnic_Origin_FK] [int] NULL,
[Keep_Details] [bit] NULL,
[Interviewed_By_FK] [bit] NULL,
[Terminated] [bit] NULL,
CONSTRAINT [aaaaaCourse_Application_PK] PRIMARY KEY NONCLUSTERED
(
[Course_App_PK] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO [Course_Application] ([Course_App_PK],[Course_App_Date],[Course_Choice_1],[Person_FK],[Street],[District],[City],[Post_Code],[Tel_Day],[Tel_Evening],[Email],[Emergency_Contact Name],[Emergency_Contact_Number],[Friend_Partner_Name],[Friend_Partner_Desc],[Wheelchair],[Transport_FK],[Other_Learning_Needs],[Six_Weeks],[Hear_About_Course_FK],[GP_Name],[Age_Band_FK],[Gender_FK],[Ethnic_Origin_FK],[Keep_Details],[Interviewed_By_FK],[Terminated])VALUES(20,'Apr 1 2005 12:00:00:000AM',7,357,'1 Some Road','Dont Know','London','W12 34A','0123 453 789',NULL,NULL,'Mother','01234567890','No',NULL,0,2,'None',1,1,'Some GP Surgery',2,2,0,1,0,1)
INSERT INTO [Course_Application] ([Course_App_PK],[Course_App_Date],[Course_Choice_1],[Person_FK],[Street],[District],[City],[Post_Code],[Tel_Day],[Tel_Evening],[Email],[Emergency_Contact Name],[Emergency_Contact_Number],[Friend_Partner_Name],[Friend_Partner_Desc],[Wheelchair],[Transport_FK],[Other_Learning_Needs],[Six_Weeks],[Hear_About_Course_FK],[GP_Name],[Age_Band_FK],[Gender_FK],[Ethnic_Origin_FK],[Keep_Details],[Interviewed_By_FK],[Terminated])VALUES(21,'Dec 10 2007 12:00:00:000AM',7,502,'2 Someplace Lane','Far Place','London','E12 34A','0123 456 789',NULL,NULL,'Husband','01234567890','No',NULL,0,1,'No',1,4,'Some GP Surgery',5,2,3,1,0,1)
INSERT INTO [Course_Application] ([Course_App_PK],[Course_App_Date],[Course_Choice_1],[Person_FK],[Street],[District],[City],[Post_Code],[Tel_Day],[Tel_Evening],[Email],[Emergency_Contact Name],[Emergency_Contact_Number],[Friend_Partner_Name],[Friend_Partner_Desc],[Wheelchair],[Transport_FK],[Other_Learning_Needs],[Six_Weeks],[Hear_About_Course_FK],[GP_Name],[Age_Band_FK],[Gender_FK],[Ethnic_Origin_FK],[Keep_Details],[Interviewed_By_FK],[Terminated])VALUES(22,'Apr 17 2008 12:00:00:000AM',7,377,'3 Someother Road',NULL,'London','N12 34A','0123 456 789',NULL,NULL,'Mum','01234567890','No',NULL,0,2,'No',1,4,'',3,1,0,1,0,1)
INSERT INTO [Course_Application] ([Course_App_PK],[Course_App_Date],[Course_Choice_1],[Person_FK],[Street],[District],[City],[Post_Code],[Tel_Day],[Tel_Evening],[Email],[Emergency_Contact Name],[Emergency_Contact_Number],[Friend_Partner_Name],[Friend_Partner_Desc],[Wheelchair],[Transport_FK],[Other_Learning_Needs],[Six_Weeks],[Hear_About_Course_FK],[GP_Name],[Age_Band_FK],[Gender_FK],[Ethnic_Origin_FK],[Keep_Details],[Interviewed_By_FK],[Terminated])VALUES(24,'May 15 2007 12:00:00:000AM',7,401,'4 Far Away Garden',NULL,'London','S12 34A','0132 456 7890',NULL,NULL,'Sister','01234567890','No',NULL,0,2,'None',1,3,'Some GP Surgery',4,1,0,1,0,1)
INSERT INTO [Course_Application] ([Course_App_PK],[Course_App_Date],[Course_Choice_1],[Person_FK],[Street],[District],[City],[Post_Code],[Tel_Day],[Tel_Evening],[Email],[Emergency_Contact Name],[Emergency_Contact_Number],[Friend_Partner_Name],[Friend_Partner_Desc],[Wheelchair],[Transport_FK],[Other_Learning_Needs],[Six_Weeks],[Hear_About_Course_FK],[GP_Name],[Age_Band_FK],[Gender_FK],[Ethnic_Origin_FK],[Keep_Details],[Interviewed_By_FK],[Terminated])VALUES(25,'Jan 29 2010 12:00:00:000AM',7,409,'5 The Street','Village','London','W1 23A','0123 4567 890',NULL,NULL,'Son','01234567890','No',NULL,0,2,'No',1,5,'Some GP Surgery',7,2,0,1,0,1)
So like the Course_App_Date in that table needs to be used to generate ALL QUARTERS FROM
2005-2 (The second Quarter of 2005)
to
2010-1 (The first quarter of 2010)
so that would be
2005-2
2005-3
2005-4
2006-1
2006-2
2006-3
2006-4
2007-1
2007-2
2007-3
2007-4
2008-1
2008-2
2008-3
2008-4
2009-1
2009-2
2009-3
2009-4
2010-1
Then various counts of whatever will go next to this (that part is already there now)
Just need the quarters to be 'self-updating based on what date values are actually in the database!
April 20, 2010 at 3:07 pm
Okay, take the code I wrote above.
Change the "year" to "qq" in the two date functions, and the select @min-2/@max to the appropriate table/column. Add some code to calc the year-qtr, and you end up with this working code:
declare @min-2 datetime,
@max-2 datetime,
@Age int;
select @min-2 = MIN([Course_App_Date]),
@max-2 = MAX([Course_App_Date])
from @Course_Application;
select @Age = DATEDIFF(qq, @min-2, @max-2);
-- See Jeff Modem's article The "Numbers" or "Tally" Table: What it is and how it replaces a loop.
-- at http://www.sqlservercentral.com/articles/T-SQL/62867/.
;WITH
Tens (N) AS (SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0),
Thousands(N) AS (SELECT 1 FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3),
Millions (N) AS (SELECT 1 FROM Thousands t1 CROSS JOIN Thousands t2),
Tally (N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)
Dates (DateField) AS (
SELECT DateField = DATEADD(qq, N, @min-2)
FROM Tally
WHERE N <= @Age
UNION ALL
SELECT @min-2 )
SELECT DateField,
YearQtr =
CONVERT(char(4), YEAR(DateField)) + '-' +
CONVERT(char(1), DatePart(qq, DateField))
FROM Dates
ORDER BY DateField;
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply