Posted: Mon Dec 03, 2007 5:50 pm Post subject: Query to pull data between two fiscal periods
Hi,
Please help in correcting the below query.
The below query is to pull the data between two fiscal months of same or different year.
Code:
SELECT
A,
b,
,fiscal_year
,fiscal_month
,SUM(z)
FROM TABLE1
WHERE ( fiscal_year = 2007 AND fiscal_month >= 11)
AND ( fiscal_year = 2008 AND fiscal_month <= 1)
GROUP BY A, B,fiscal_year, fiscal_month
ORDER BY A, B, fiscal_year, fiscal_month
WITH UR
In Table1, Fiscal year and fisc month will be like this
Code:
Fiscal Year Fiscal month
2007 9
2007 10
2007 11
2007 12
2008 1
2008 2
2008 3
2008 4
2008 5
2008 6
Now I need to pull data between 2007,11 and 2008,1 (Fisc yr , Fisc month).
Sometimes I need to pull data from the same year. For that below condition works correctly.
(Fiscal month between x and y
AND Fiscal year BETWEEN x and y)
Joined: 26 Nov 2002 Posts: 12378 Topics: 75 Location: San Jose
Posted: Mon Dec 03, 2007 7:08 pm Post subject:
SMS,
Try this untested sql
Code:
SELECT A
,B
,fiscal_year
,fiscal_month
,SUM(z)
FROM TABLE
WHERE (fiscal_year,fiscal_month) BETWEEN 200711 AND 200801
GROUP BY A
,B
,fiscal_year
,fiscal_month
WITH UR
;
Are fiscal year and fiscal month numeric or character? I like Kolusu's idea, but I think if they are character, you would need a leading zero on the fiscal months that are less than 10, and then you would need to concatenate the two columns. If they are number, you could combine them with simple math of 100*fiscal_year+fiscal month. _________________ Alan Voss
Joined: 02 May 2007 Posts: 202 Topics: 0 Location: Viginia, USA
Posted: Tue Dec 04, 2007 10:48 am Post subject: Re: Query to pull data between two fiscal periods
SMS wrote:
Hi,
Please help in correcting the below query.
The below query is to pull the data between two fiscal months of same or different year.
Code:
SELECT
A,
b,
,fiscal_year
,fiscal_month
,SUM(z)
FROM TABLE1
WHERE ( fiscal_year = 2007 AND fiscal_month >= 11)
AND ( fiscal_year = 2008 AND fiscal_month <= 1)
GROUP BY A, B,fiscal_year, fiscal_month
ORDER BY A, B, fiscal_year, fiscal_month
WITH UR
In Table1, Fiscal year and fisc month will be like this
Code:
Fiscal Year Fiscal month
2007 9
2007 10
2007 11
2007 12
2008 1
2008 2
2008 3
2008 4
2008 5
2008 6
Now I need to pull data between 2007,11 and 2008,1 (Fisc yr , Fisc month).
Sometimes I need to pull data from the same year. For that below condition works correctly.
(Fiscal month between x and y
AND Fiscal year BETWEEN x and y)
I appreciate any suggestions on this.
Thanks,
SMS
I don't see anything wrong with what you have for the first query but for the second you don't need a between for the fiscal_year just a equal with a between for the fiscal_month (if you want less than a full year).
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
