Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Filter report based on Quarter or in my Case Fiscal Quarter Expand / Collapse
Author
Message
Posted Tuesday, April 12, 2011 1:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, August 27, 2012 7:32 PM
Points: 1, Visits: 12
I've used these forums on many occasions to solve the problems that have vexed me and decided to post a solution I came up with today that I was having trouble finding and had to invent.
I have a report with several parameters. Fiscal Year, Department,Fiscal quarter, just to name a couple, several are Multi-Select. I like to set all my reports with default parameters, and with dates, I love for them to be dynamic, for instances so I don't have to republish reports every year, when I supply default parameters for the Fiscal Year I use this expression =iif(month(now)>=7,year(now)+1,Year(Now)). My company's Fiscal Year Starts in July.

Now for Quarter, it was a little more interesting. It is also a Multi-Select, but I wanted to return the current Fiscal Quarter by Default instead of all. Also in the Stored Procedure, @fquarter is varchar, so the data type for the parameter is String. My Fiscal Year starts in July, so conversion was a little easier since the Fiscal Quarters lined up with the Calendar Quarters. So Instead of selecting all quarters, this returns the Current Fiscal Quarter as a default value.
=iif(Cstr(Datepart("q",Today())>=3),CStr(Datepart("q",Today())-2),Cstr(Datepart("q",Today())+2))
If you wanted to follow the Calendar Year, you could drop the iif and use the first part of the expression. =Cstr(Datepart("q",Today())

I hope this saves someone some head banging. If anyone has alternate ways of handling this I'd love to know, doesn't seem to be a lot out there I can find.
Post #1092426
Posted Tuesday, April 12, 2011 2:28 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 7:35 AM
Points: 2,819, Visits: 2,563
A common technique in the data warehousing world is to create a dates or calendar table that list all the possible permutations of a date. ie.:
date
calendar year
fiscal year
calendar quarter
fiscal quarter
calendar month
fiscal month
day of calendar month
day of fiscal month
day of calendar year
day of fiscal year

etc. Then query the table with a where = Getdate() and it returns all permutations of a date. This approach also makes date range queries easy, instead of WHERE DATE BETWEEN 1/1/11 AND 3/31/11
you could use WHERE FiscalQuarter = 201101.

Post #1092440
Posted Tuesday, September 17, 2013 1:27 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, October 18, 2013 2:32 PM
Points: 1, Visits: 10
Daniel,

I found your post about dealing with a calendar table and I have done that exact thing and I have run into a lil issue and was wondering if you had a solution to it. Below is my query for my calendar table...

CREATE TABLE dbo.State_Calendar
(
dt SMALLDATETIME NOT NULL
PRIMARY KEY CLUSTERED,

isWeekday BIT,
isHoliday BIT,
Y SMALLINT,
FY SMALLINT,
Q TINYINT,
M TINYINT,
D TINYINT,
DW TINYINT,
monthname VARCHAR(9),
dayname VARCHAR(9),
W TINYINT
)
GO

---populate the table with data from jan 2000 to dec 31,2029

SET NOCOUNT ON
DECLARE @dt SMALLDATETIME
SET @dt = '20000101'
WHILE @dt < '20300101'
BEGIN
INSERT dbo.State_Calendar(dt) SELECT @dt
SET @dt = @dt + 1
END

--now populate all the other columns in the table

UPDATE dbo.State_Calendar SET

isWeekday = CASE
WHEN DATEPART(DW, dt) IN (1,7)
THEN 0
ELSE 1 END,

isHoliday = 0,

Y = YEAR(dt),



/*
-- if our fiscal year
-- starts on Sept 1st:
*/

FY = CASE
WHEN MONTH(dt) < 9
THEN YEAR(dt)-1
ELSE YEAR(dt) END,

--STATE Quarters are different
/*• 1st quarter: 1 October 2012 – 31 December 2012
• 2nd quarter: 1 January 2013 – 31 March 2013
• 3rd quarter: 1 April 2013 – 30 June 2013
• 4th quarter: 1 July 2013 – 30 September 2013
*/


Q = CASE
WHEN MONTH(dt) >=10 THEN 1
WHEN MONTH(dt) <=3 THEN 2
WHEN MONTH(dt) BETWEEN 4 AND 6 THEN 3
ELSE 4 END,

M = MONTH(dt),

D = DAY(dt),

DW = DATEPART(DW, dt),

monthname = DATENAME(MONTH, dt),

dayname = DATENAME(DW, dt),

W = DATEPART(WK, dt)


The issue I am having is that I want to pull the quarter from the current date but want it do follow my fiscal year. See below for example..

select *
from State_calendar
where
[highlight=#ffff11]Q=DATENAME(quarter,getdate()) and[/highlight]
Y=YEAR(GETDATE())

Problem is the quarter in sql says 9-17-2013 is in the 3 quarter but in my fiscal year it is in the 4th quarter, So I am getting date range
04-01-2013 to 06-30-2013

--STATE Quarters are different
/*• 1st quarter: 1 October 2012 – 31 December 2012
• 2nd quarter: 1 January 2013 – 31 March 2013
• 3rd quarter: 1 April 2013 – 30 June 2013
• 4th quarter: 1 July 2013 – 30 September 2013
*/


Q = CASE
WHEN MONTH(dt) >=10 THEN 1
WHEN MONTH(dt) <=3 THEN 2
WHEN MONTH(dt) BETWEEN 4 AND 6 THEN 3
ELSE 4 END,


How might I get around this? The reason I am doing this is to eliminate parameters from a stored procedure where this report will run on the last night of the quarter and build the report for the entire quarter.

Thanks for your time,

Melissa
Post #1495650
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse