Click here to Skip to main content
15,905,781 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
HELLO FRIENDS,
I WAS TRYING FOLLOWING QUERY
SELECT OrderID,V.ForcSummCalDesc,    
Isnull(Q1,0) Q1,    
Isnull(Q2,0) Q2 ,    
Isnull(Q3,0) Q3,    
Isnull(Q4,0) Q4, ISNULL(YTD ,0) YTD
FROM FWB_VWForcSummCalc v    
LEFT OUTER JOIN
(
	SELECT [ForcSummCalDesc], SUM(Q1) [Q1],SUM(Q2) [Q2],SUM(Q3) [Q3],SUM(Q4) [Q4] FROM
	(
		SELECT 
		CASE WHEN CONVERT(INT,MONTH(ClosedDate)) IN (7,8,9) THEN
		SUM(ORDERVALUE * dbo.FWB_ufn_GetConversionRate(GETOPP.Currency,@Currency,@ReviewMonth)) END Q1,
		CASE WHEN CONVERT(INT,MONTH(ClosedDate)) IN (10,11,12) THEN
		SUM(ORDERVALUE * dbo.FWB_ufn_GetConversionRate(GETOPP.Currency,@Currency,@ReviewMonth)) END Q2,
		CASE WHEN CONVERT(INT,MONTH(ClosedDate)) IN (1,2,3) THEN
		SUM(ORDERVALUE * dbo.FWB_ufn_GetConversionRate(GETOPP.Currency,@Currency,@ReviewMonth)) END Q3,
		CASE WHEN CONVERT(INT,MONTH(ClosedDate)) IN (4,5,6) THEN
		SUM(ORDERVALUE * dbo.FWB_ufn_GetConversionRate(GETOPP.Currency,@Currency,@ReviewMonth)) END Q4,
		CASE   
		WHEN ProbabilityPercent = 100 THEN '100% & Contracts'
		WHEN ProbabilityPercent = 90 THEN '90%'
		WHEN ProbabilityPercent IN (60,75) THEN '60% - 90%'
		ELSE 'Below 60%'
		END [ForcSummCalDesc]
		FROM FWB_Opportunities OPP
		INNER JOIN dbo.FWB_ufn_GetOpportunity(@empid) GETOPP ON OPP.OpportunityId = GETOPP.OpportunityId 
		AND GETOPP.REVIEWMONTH = @ReviewMonth
		WHERE OPP.ReviewMonth = @ReviewMonth AND (ClosedDate Between @startDate AND @endDate) 
		AND [Include] = 1 
		AND GETOPP.AccountID=ISNULL(@accountid,GETOPP.AccountID) 
		AND GETOPP.VCID=ISNULL(@vcid,GETOPP.VCID) 
		AND GETOPP.GeoID= ISNULL(@geoid,GETOPP.GeoID)  
		GROUP BY ProbabilityPercent,ClosedDate
	) A
	GROUP BY [ForcSummCalDesc]
)B
ON V.[ForcSummCalDesc] = B.ForcSummCalDesc


I ALSO WANT ANOTHER FEILD CALLED 'YTD' IN FINAL OUTPUT WHICH TAKES THE FOLLOWING FORM
YTD=Q1+Q2+Q3+Q4

ANY IDEA HOW I CAN DO THIS

I TRIED FOLLOWING
SELECT OrderID,V.ForcSummCalDesc,    
Isnull(Q1,0) Q1,    
Isnull(Q2,0) Q2 ,    
Isnull(Q3,0) Q3,    
Isnull(Q4,0) Q4 
FROM FWB_VWForcSummCalc v    
LEFT OUTER JOIN
(
    SELECT [ForcSummCalDesc],Q1,Q2,Q3,Q4,SUM(Q1+Q2+Q3+Q4) YTD FROM
    (
	SELECT [ForcSummCalDesc], SUM(Q1) [Q1],SUM(Q2) [Q2],SUM(Q3) [Q3],SUM(Q4) [Q4] FROM
	(
         ....
        )A
	GROUP BY [ForcSummCalDesc]
    )C GROUP BY [ForcSummCalDesc],Q1,Q2,Q3,Q4
)B
ON V.[ForcSummCalDesc] = B.ForcSummCalDesc


IT GIVES ME NULL VALUE IN YTD COLUMN

ANY IDEA, HOW CAN I DO THIS?

THANKS IN ADVANCE
Posted
Updated 9-May-12 23:00pm
v2
Comments
Sandeep Mewara 10-May-12 5:47am    
You are pretty old member who has posted quite a lot of questions. Yet, you post your sentences in CAPS! :doh:

CAPS means shouting and is rude while asking for help.
dhage.prashant01 15-May-12 7:05am    
Oo sorry for that =)
Have habit of writing sql queries in caps
Hence I will do take care of it.
Nice to know, someone knows me..!! =)
Sandeep Mewara 15-May-12 11:39am    
Ok.

Try using for example COALESCE (or ISNULL) in your calculation. Something like:
SQL
...
SELECT [ForcSummCalDesc],Q1,Q2,Q3,Q4,
       SUM(COALESCE( Q1, 0) 
           + COALESCE( Q2, 0)
           + COALESCE( Q3, 0)
           + COALESCE( Q4, 0) ) YTD FROM
...
 
Share this answer
 
Comments
Maciej Los 10-May-12 16:44pm    
Good answer, my 5! ;)
Wendelius 10-May-12 16:49pm    
Thanks :)
Sandeep Mewara 10-May-12 23:06pm    
My 5!
Wendelius 11-May-12 0:54am    
Thanks :)
Another solution is:
Replace MONEY with destination type of data.
SQL
SELECT [ForcSummCalDesc],Q1,Q2,Q3,Q4, CONVERT(MONEY, ISNULL(Q1,0)+ ISNULL(Q2,0) + ISNULL(Q3,0) + ISNULL(Q4,0)) YTD FROM
 
Share this answer
 
Comments
Wendelius 10-May-12 16:49pm    
Depending on the data, that should also work. 5
Maciej Los 10-May-12 17:07pm    
Thank you ;)
Sandeep Mewara 10-May-12 23:06pm    
My 5!
Maciej Los 11-May-12 1:25am    
Thank you ;)
Maciej Los 11-May-12 1:25am    
----- deleted ----

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900