Date by monthly, quarterly, yearly

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date by monthly, quarterly, yearly

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-13 : 14:12:28

Lets say I have a date range of 1/1/2009 to 4/20/2010

And I have a selection criteria of Monthly, Quarterly and Yearly in the drop down.

If Monthly is selected then I should have 16 diff rows , 12 rows for the months in 2009 and 4 rows for the months in 2010.
If Quarterly is selected then I should have 6 rows, 4 quarters in 2009 and 2 rows in 2010.
If Yearly is selected then I should have 2 rows one year for 2009 and one for 2010.

How would you do this? I could define another datetime var and using a CASE stmt with a condition that the datetime field is between start and end date?

Based on the date range selected , output would appear as for example By Quarter

Q1
Q2

By Monthly
Jan - Dec 2009
Jan - April 2010

DECLARE
@StartDate datetime, --= '20100101'
@EndDate datetime, --= '20100201'
@DateBreakout datetime
--month, quarter, year (month 12, quarter 4 and year entire date range)
SET @StartDate = '20090101'
SET @EndDate = '20090523'
SET @DateBreakout = '20090409'

SELECT right('0' + convert(varchar(2), datepart(mm, @DateBreakout)), 2),
convert(varchar(4), datepart(yy, @DateBreakout)),
'Q' + convert(varchar(2), datepart(qq, @DateBreakout)) --Added for grouping in report
where @DateBreakout BETWEEN @StartDate AND @EndDate

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-13 : 15:59:10

I found these examples. but please let me know if there are other ways to go about doing this.

DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '20070429',
@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.
-- The outer select formats it. Once cached, it's incredibly fast.
;WITH
cteTally AS
(--==== Returns a value of 1 to the number of days of all months in date range
SELECT TOP (DATEDIFF(dd,
DATEADD(mm,DATEDIFF(mm,0,@DateStart),0), --First of start month
DATEADD(mm,DATEDIFF(mm,0,@DateEnd)+1,0))) --First of month after end month
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT N,
DateStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart),0)+t.N-1,
NextStart = DATEADD(mm,DATEDIFF(mm,0,@DateStart),0)+t.N
FROM cteTally t

--=======================================================================================
-- Find all dates between the start and end dates (inclusive)
--=======================================================================================
--===== Here are the two parameters you wanted
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '20070429',
@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.
-- The outer select formats it. Once cached, it's incredibly fast.
;WITH
cteTally AS
(--==== Returns a value of 1 to the number of days in date range
SELECT TOP (DATEDIFF(dd,
DATEADD(dd,DATEDIFF(dd,0,@DateStart),0), --Whole day start of range
DATEADD(dd,DATEDIFF(dd,0,@DateEnd)+1,0))) --Whole day end of range
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT N,
DateStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N-1,
NextStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N
FROM cteTally t

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-14 : 00:15:25

you can use same method and then group based on relevant period (quarter,month etc) and take distinct value to get output you want

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-14 : 09:00:35

I tried executing the below but it complained of the below error

Msg 208, Level 16, State 1, Line 8
Invalid object name 'Master.sys.All_Columns'.

If I select the Monthly option and if my start and end date parameters are for example

1/2005 and 10/2007 then I would like to see data for
Jan-Dec 2005, Jan-Dec 2006 and Jan - Oct 2007.

Another example is if my start date is 1/2005 and end date is 1/2005 then Monthly option should give me data for Jan 1st to jan 31st 2005.

I am unable to see what the below CTE does as I am not sure how to get rid of the error

--=======================================================================================
-- Find all dates between the start and end dates (inclusive)
--=======================================================================================
--===== Here are the two parameters you wanted
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '20070429',
@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.
-- The outer select formats it. Once cached, it's incredibly fast.
;WITH
cteTally AS
(--==== Returns a value of 1 to the number of days in date range
SELECT TOP (DATEDIFF(dd,
DATEADD(dd,DATEDIFF(dd,0,@DateStart),0), --Whole day start of range
DATEADD(dd,DATEDIFF(dd,0,@DateEnd)+1,0))) --Whole day end of range
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM Master.sys.All_Columns t1
CROSS JOIN Master.sys.All_Columns t2
)
SELECT N,
DateStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N-1,
NextStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N
FROM cteTally t

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-14 : 09:10:54

I got it to execute. It did not like the uppe case in All amd Master so I used all and master instead. Now if I need to view by month or quarter, you suggest grouping by month or quarter based on period?

DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SELECT @DateStart = '20070429',
@DateEnd = '20081201'

--===== Find the dates using a Tally table as a counter.
-- The outer select formats it. Once cached, it's incredibly fast.
;WITH
cteTally AS
(--==== Returns a value of 1 to the number of days in date range
SELECT TOP (DATEDIFF(dd,
DATEADD(dd,DATEDIFF(dd,0,@DateStart),0), --Whole day start of range
DATEADD(dd,DATEDIFF(dd,0,@DateEnd)+1,0))) --Whole day end of range
N = ROW_NUMBER() OVER (ORDER BY t1.Object_ID)
FROM master.sys.all_columns t1
CROSS JOIN master.sys.all_columns t2
)
SELECT N,
DateStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N-1,
NextStart = DATEADD(dd,DATEDIFF(dd,0,@DateStart),0)+t.N
FROM cteTally t

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-14 : 09:29:36

One more question. If my date range is in the format mm/yyyy and is declared as varchar(12), then I get a conversion from datetime to character error when I use the same CTE.

I tried using the convert function but still gett the error.

Is there a way I can use the below date format and varchar type to get the output for month/quarter/year based on the same range.

DECLARE
@StartMonth varchar(12),
@StartYear varchar(12),
@EndMonth varchar(12),
@EndYear varchar(12),
@StartDate varchar(12),
@EndDate varchar(12)
SET @StartMonth = '1'
SET @StartYear = '2005'
SET @EndMonth = '1'
SET @EndYear = '2005'

SET @StartDate = @StartMonth + '/' + @StartYear
SET @EndDate = @EndMonth + '/' + @EndYear

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources