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