将两个查询的结果进行除法运算，得到第三个查询中的结果

我有两个SQL查询要划分。每个查询本身都是有效的，我想将它们的结果划分为第三个变量，但我不确定如何进行。

此查询计算已取消会员的数量

(select count(*) as No_of_Member_Cancelled, M.HomeBranch,M.LocationName from 
AX.Memberships  M
where M.ActiveEnd between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 1, 
0) and DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,-1)
group by M.HomeBranch,M.LocationName) as g1

此查询计算活动成员的数量

(select count(*)as No_of_Live_Member , M.HomeBranch,M.LocationName
from AX.Memberships M 
where M.ActiveStart between DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) - 
1, 0) and DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) ,-1)
group by M.HomeBranch,M.LocationName) as g2

我们得到了类似的结果

如果x=g1/g2

x，LocationName，HomeBranch在一个表中

最好将其更改为临时表，然后可以这样做：

select No_of_Member_Cancelled / No_of_Live_Member as x, HomeBranch, LocationName
from G1
join G2 on g1.HomeBranch = g2.HomeBranch and g1.LocationName = g2.LocationName
group by HomeBranch, LocationName

试试这个。。。

SELECT CAST (g1.no_of_member_cancelled AS DECIMAL(10, 2)) / 
CAST (g2.no_of_live_member AS DECIMAL(10, 2)) AS x, 
g1.homebranch, 
g1.locationname 
FROM   (SELECT Count(*) AS No_of_Member_Cancelled, 
M.homebranch, 
M.locationname 
FROM   ax.memberships M 
WHERE  M.activeend BETWEEN Dateadd(month, Datediff(month, 0, Getdate()) 
- 1, 0) 
AND 
Dateadd(month, Datediff(month, 0, 
Getdate()) 
, -1) 
GROUP  BY M.homebranch, 
M.locationname) AS g1 
INNER JOIN (SELECT Count(*)AS No_of_Live_Member, 
M.homebranch, 
M.locationname 
FROM   ax.memberships M 
WHERE  M.activestart BETWEEN Dateadd(month, Datediff(month, 0 
, 
Getdate()) - 1, 
0) AND 
Dateadd(month, Datediff(month, 
0, Getdate( 
)), -1) 
GROUP  BY M.homebranch, 
M.locationname) AS g2 
ON g1.homebranch = g2.homebranch 
AND g1.locationname = g2.locationname

考虑到潜在值的大小，您需要将已取消和活动成员的数量转换为适合您情况的数据类型。你可能应该采取预防措施，这样你就不会得到零分误差。

SELECT distinct CAST (g1.no_of_member_cancelled AS DECIMAL(10, 2)) / 
CAST (g2.no_of_live_member AS DECIMAL(10, 2)) AS x, 
g1.homebranch, 
g1.locationname,
g1.month,
g1.year 
FROM  (SELECT Count(*) AS No_of_Member_Cancelled, 
M.homebranch, 
M.locationname,
month(M.ActiveStart) month,
YEAR(M.ActiveStart) year
FROM   ax.memberships M 
WHERE  M.activeend BETWEEN Dateadd(year, Datediff(year, 0, Getdate()) - 2, 0)  
AND 
Dateadd(year, Datediff(year, 0, Getdate()), -1)
GROUP  BY M.homebranch, 
M.locationname,month(M.ActiveStart),
year(M.ActiveStart)) AS g1 
INNER JOIN (SELECT Count(*)AS No_of_Live_Member, 
M.homebranch, 
M.locationname,
month(M.ActiveStart) month,
YEAR(M.ActiveStart) year
FROM   ax.memberships M 
WHERE  M.activestart BETWEEN Dateadd(year, Datediff(year, 0, Getdate()) - 2, 0) 
AND 
Dateadd(year, Datediff(year, 0, Getdate()), -1)
GROUP  BY M.homebranch, 
M.locationname,
month(M.ActiveStart),
year(M.ActiveStart)
) AS g2 
ON g1.homebranch = g2.homebranch 
AND g1.locationname = g2.locationname
AND g1.month = g2.month
AND g1.year = g2.year
Order by g1.year,g1.month

你能检查一下我的查询是否正确吗。因为我想从过去的2年

相关内容

最新更新

热门标签：