asked on

SQL View to include values in Annual Quarters

I have a table called Opportunity with columns that return data as follows:

Oppo_OpportunityID Oppo_userID Oppo_Description Oppo_Total Oppo_CloseBy
101 1 3 x Widget X 1500 9/28/2011 12:00:00 AM
102 1 2 x Widget Y 1000 11/27/2011 12:00:00 AM
103 2 4 x Widget X 2000 10/20/2011 12:00:00 AM

oppo_opportunityid = int
oppo_userid = int
oppo_description = nvarchar(50)
oppo_total = numeric(24, 6)
oppo_closeby = datetime

I need to write a SQL view that returns the data to include Quarters/Year (datepart qq)
e.g.

Oppo_OpportunityID Oppo_userID Oppo_Description Oppo_Total * QQYY
101 1 3 x Widget X 1500 Q311
102 1 2 x Widget Y 1000 Q411
103 2 4 x Widget X 2000 Q411

Any help would be appreciated

Lee

Include this:

'Q' + cast(datepart(q, oppo_CloseBy) as nvarchar(1)) + right(cast(year(oppo_CloseBy) as nvarchar(4)), 2) as QQYY

ASKER CERTIFIED SOLUTION

Lee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MimUK

ASKER

Great Thanks Lee