需要在SQL中进行分组登录 [英] Need grouping login in SQL

问题描述

你好朋友，
我正在尝试查询

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

我还希望在采用以下格式的最终​​输出中再次调用"YTD"
YTD=Q1+Q2+Q3+Q4

任何想法我该怎么做

我尝试了以下

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

它在年初至今给我NULL值

任何想法，我该怎么办?

感谢ADVANCE

IT GIVES ME NULL VALUE IN YTD COLUMN

ANY IDEA, HOW CAN I DO THIS?

THANKS IN ADVANCE

推荐答案

在计算中尝试使用例如COALESCE(或ISNULL).像这样的东西:

Try using for example COALESCE (or ISNULL) in your calculation. Something like:

...
SELECT [ForcSummCalDesc],Q1,Q2,Q3,Q4,
       SUM(COALESCE( Q1, 0) 
           + COALESCE( Q2, 0)
           + COALESCE( Q3, 0)
           + COALESCE( Q4, 0) ) YTD FROM
...

另一个解决方案是:
将MONEY替换为数据的目标类型.

Another solution is:
Replace MONEY with destination type of data.

SELECT [ForcSummCalDesc],Q1,Q2,Q3,Q4, CONVERT(MONEY, ISNULL(Q1,0)+ ISNULL(Q2,0) + ISNULL(Q3,0) + ISNULL(Q4,0)) YTD FROM

这篇关于需要在SQL中进行分组登录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！