October 10, 2005 at 3:37 am
I need to group a date range and list that date range as FY2002, QTR1,QTR2,QTR3 QTR4 for muti years.
In the database mssql 2000 there is a date fields and there is also as year field, month field, and day field. The CFO whats to see a report on the reporting server that will list some company data like this, if date bewteen 10/01/2002 and 09/30/2003 then FY2003. I also would like to group by qtr, so if date bewteen 10/01/2002 and 12/31/2002 the QTR1. I am not sure how to start. I have been reading the SQL books online but have not found a way to accomplish this. Any help? Thanks in advance
Walter
October 10, 2005 at 5:44 am
How bout CASE WHEN FieldDt BETWEEN '2002-10-01' AND '2003-009-30' THEN 'FY2003'
May need to look at creating a xref table to house Month / Year in it along with Quarter and FY
i.e.
ID Month Year Quarter FiscalYear
1 10 2002 QTR1 FY2003
You could then link this in and GROUP BY using this table
Just a thought
Good Hunting!
AJ Ahrens
webmaster@kritter.net
October 10, 2005 at 7:17 am
How about this for a start
SELECT [Year],[Qtr],SUM([value]) AS [value]
FROM (SELECT [Year],DATEPART(quarter,[Date])+1 AS [Qtr],[value]
FROM
WHERE [Month] < 10
UNION
SELECT [Year]+1,1 as [Qtr],[value]
WHERE [Month] >= 10
FROM
) x
GROUP BY [Year],[Qtr]
ORDER BY [Year],[Qtr]
Far away is close at hand in the images of elsewhere.
Anon.
October 10, 2005 at 12:18 pm
I will give those a shot and see what happens thanks
October 18, 2005 at 7:45 am
I created a stored procedure that uses the Quarter field. I do create it based on the Month field. You may be able to adapt to your needs. The brackets indicate of course that this is not a table field but a field created on the fly.
[QUARTER] = CASE WHEN mpm.MPM_SALESMONTH IN (1, 2, 3) THEN 'Q1'
WHEN mpm.MPM_SALESMONTH IN (4, 5, 6) THEN 'Q2'
WHEN mpm.MPM_SALESMONTH IN (7, 8, 9) THEN 'Q3'
WHEN mpm.MPM_SALESMONTH IN (10, 11, 12) THEN 'Q4'
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply