Fiscal Year Query - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Fiscal Year Query

Author

Topic

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-28 : 02:13:32

Hello all am back with one more question:::

I have the query below which gives me data from quarter wise.
with my query i get data q1=jan-feb-mar

What should i do to get data by fiscal calender year ( ex: 01-april-2006 to 31-mar-2007)is one fiscal year,
q1 should be (apr-may-june) of every year and follows---please help

SELECT
DATENAME(Year,BookingDate) as Year,
DATENAME(QUARTER,BookingDate) as QUARTER,
COUNT(InvoiceNumber) AS [Total Bookings],
Sum(NegotiatedSellPrice) AS [Total Sale Price]

FROM tblInvoice
WHERE BookingDate >= '2006-01-01'
AND BookingDate < '2020-02-01'
AND fk_StatusID in (3, 5)

GROUP BY DATENAME( quarter,bookingDate) ,
DATENAME(Year,BookingDate),
DATEPART(QQ,BookingDate),
DATEPART(YYYY,BookingDate)

ORDER BY DATEPART(YYYY,BookingDate),
DATEPART(QQ,BookingDate)

Thanks

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-28 : 05:18:59

I got it guys:::
but how to group it quarterwise...???

SELECT

COUNT(InvoiceNumber) AS [Total Bookings],
Sum(NegotiatedSellPrice) AS [Total Sale Price],bookingdate,

CASE
WHEN MONTH(bookingdate) BETWEEN 1 AND 3 THEN convert(char(4), YEAR(bookingdate) - 1) + 'Q4'
WHEN MONTH(bookingdate) BETWEEN 4 AND 6 THEN convert(char(4), YEAR(bookingdate) - 0) + 'Q1'
WHEN MONTH(bookingdate) BETWEEN 7 AND 9 THEN convert(char(4), YEAR(bookingdate) - 0) + 'Q2'
WHEN MONTH(bookingdate) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(bookingdate) - 0) + 'Q3'
END AS Quarter
FROM
dbo.tblInvoice

where
BookingDate >= '2006-04-01'
AND BookingDate < '2020-02-01'
AND fk_StatusID in (3, 5)

GROUP BY DATENAME( quarter,bookingDate) , DATENAME(Year,BookingDate),
DATEPART(QQ,BookingDate),bookingdate
ORDER BY DATEPART(YYYY,BookingDate),DATEPART(QQ,BookingDate),bookingdate,DATENAME( quarter,bookingDate)

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-28 : 05:28:55

do you have fiscal calender table ?

KH
[spoiler]Time is always against us[/spoiler]

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-28 : 05:34:16

I don t have fiscal calender table.this part of query gets me

CASE
WHEN MONTH(bookingdate) BETWEEN 1 AND 3 THEN convert(char(4), YEAR(bookingdate) - 1) + 'Q4'
WHEN MONTH(bookingdate) BETWEEN 4 AND 6 THEN convert(char(4), YEAR(bookingdate) - 0) + 'Q1'
WHEN MONTH(bookingdate) BETWEEN 7 AND 9 THEN convert(char(4), YEAR(bookingdate) - 0) + 'Q2'
WHEN MONTH(bookingdate) BETWEEN 10 AND 12 THEN convert(char(4), YEAR(bookingdate) - 0) + 'Q3'
END AS Quarter

this part will get me coloumn with coloumn name Quarter and with rows as
2006q4,2007q1,2007q2,2007q3....but would like to group them based on "coloumn name "quarter"

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-28 : 05:41:40

Well, you should consider having one.
Depending on your requirement, it can be as simple as


declare	@fiscal table
(
	[cal_year]	int,
	[cal_month]	int,
	[fis_year]	int,
	[fis_quarter]	int
)
insert into @fiscal select 2006, 1, 2005, 4
insert into @fiscal select 2006, 2, 2005, 4
insert into @fiscal select 2006, 3, 2005, 4
insert into @fiscal select 2006, 4, 2006, 1
insert into @fiscal select 2006, 5, 2006, 1
insert into @fiscal select 2006, 6, 2006, 1
insert into @fiscal select 2006, 7, 2006, 2
insert into @fiscal select 2006, 8, 2006, 2
insert into @fiscal select 2006, 9, 2006, 2
insert into @fiscal select 2006,10, 2006, 3
insert into @fiscal select 2006,11, 2006, 3
insert into @fiscal select 2006,12, 2006, 3
insert into @fiscal select 2007, 1, 2006, 4
insert into @fiscal select 2007, 2, 2006, 4
insert into @fiscal select 2007, 3, 2006, 4
... ...

-- And then your query can be as simple as
; with cte as
(
	SELECT	datepart(year,  BookingDate) as cal_year,
		datepart(month, BookingDate) as cal_month,
		COUNT(InvoiceNumber) AS [Total Bookings],
		Sum(NegotiatedSellPrice) AS [Total Sale Price]
	FROM 	tblInvoice
	WHERE 	BookingDate >= '2006-01-01'
	AND 	BookingDate < '2020-02-01'
	AND 	fk_StatusID in (3, 5)
	GROUP BY datepart(year,  BookingDate),
		datepart(month, BookingDate)
)
select	f.fis_year, f.fis_quarter, c.[Total Bookings], c.[Total Sale Price]
from	cte c
	inner join @fiscal f	on	c.cal_year 	= f.cal_year
				and	c.cal_month 	= f.cal_month
order by f.fis_year, f.fis_quarter

KH
[spoiler]Time is always against us[/spoiler]

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-28 : 05:43:22

Thanks for u r help.. i ll do that....

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-08-28 : 05:47:20

another comments on your GROUP BY. Those in red are extra column in the GROUP BY clause. It is redundant as DATENAME(Year,BookingDate) is basically same as DATEPART(YYYY,BookingDate).

quote:


SELECT
	DATENAME(Year,BookingDate) as Year,
	DATENAME(QUARTER,BookingDate) as QUARTER,
	COUNT(InvoiceNumber) AS [Total Bookings],
	Sum(NegotiatedSellPrice) AS [Total Sale Price]
FROM 	
	tblInvoice
WHERE 	
	BookingDate >= '2006-01-01'
AND 	BookingDate < '2020-02-01'
AND 	fk_StatusID in (3, 5)
GROUP BY 
	DATENAME( quarter,bookingDate) ,
	DATENAME(Year,BookingDate),
	DATEPART(QQ,BookingDate),
	DATEPART(YYYY,BookingDate)

Also take note that datename() and datepart() actually returns different data type. for datename(year, ..) and datepart(year, ...) it may seems like returning the same result but one is a nvarchar and the other is a int.

KH
[spoiler]Time is always against us[/spoiler]

anuraag205
Yak Posting Veteran

58 Posts

Posted - 2012-08-28 : 06:00:39

Thanks for the info..

I ll use only one now.

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources