Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Filter report based on Quarter or in my Case Fiscal Quarter


Filter report based on Quarter or in my Case Fiscal Quarter

Author
Message
mightycaptain
mightycaptain
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 16
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.
Daniel Bowlin
Daniel Bowlin
SSCrazy
SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)SSCrazy (3K reputation)

Group: General Forum Members
Points: 2950 Visits: 2629
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.
mhoward_00 57858
mhoward_00 57858
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search