Fiscal Quarters

[philwes83170]

I need to create a view to summarize actual totals of orders by sales rep. It's got to be easy but, I'm a novice programmer so I keep getting lost.

My table looks like this:

Name Order Amount Date
Smith ABC 100 12/1/2005
Smith XYZ 200 12/15/2005
Smith QWE 500 1/15/2006
Jones LMN 150 1/5/2006

I need a view that will show the following:

Name FiscalYear FiscalQtr Total
Smith 2006 1 300
Smith 2006 2 500
Jones 2006 2 150

Our fiscal year is Oct 1 to Sept 30. I've got the totals for each rep, but I don't know how to break it out to the year and quarter.

Thanks in advance for your assistance.

 

[SQLSister]

I would do this using a union all.

Create a select that gathers the data you want for the first quasrter by using a where clause that has the dates for the first day of the quarter and the firstday of the next quarter then union all to a simliar statemnt that has the days for the next quarter and so forth through all four quarters and then finally add one for the entire year if you want that data as well. Order by Name

Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gmmastros]

This may not be perfect, but hopefully will give you a headstart.

Select Name,
       Sum(Amount),
       Year(Date),
       Case DatePart(Quarter, Date)
            When 1 Then 2
            When 2 Then 3
            When 3 Then 4
            When 4 Then 1
            End As FiscalQtr
From  Table
Group By Name, Year(Date), DatePart(Quarter, Date)

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[philwes83170]

gmmastros...

Worked perfect, thanks. Just a little question, I need to increment the year for Q1. Fiscal Qtr1 is 1, but year is still 2005 instead of 2006. I Can't get the syntax to work right in there to increment the year. Can you help with that?

 

[gmmastros]

Instead of Year(Date), try

Case When datePart(Quarter, Date) = 4 Then Year(Date) + 1 Else Year(Date) End.

I'm not completely convinced that this will be completely accurate for your quarters and fiscal year, so you should test this thoroughly before implementing.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[philwes83170]

Something in the syntax is killing me. Keep getting this error:

ACTUALCLOSE is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Here my code:

Select LastName,
Case DatePart(Quarter, ActualClose)
When 1 Then 2
When 2 Then 3
When 3 Then 4
When 4 Then 1
End As FiscalQtr,
Case
When DatePart(Quarter, ActualClose) = 4
Then Year(ActualClose) + 1
Else Year(ActualClose)
End As FiscalYear,
Sum(ActualAmount)

From HAY_Orders

Group By LastName, Year(ActualClose), DatePart(Quarter, ActualClose)

It looks like it is grouping them all correctly if I could just get the year to increment.