如何将Excel公式转换为SQL查询
如何将Excel公式转换为SQL查询
提问于 2016-11-22 13:18:00
我使用下面的SQL查询从sql提取了一些数据到Excel，然后在excel中添加了两个列，它们各自的公式如下所示进行分析。现在，我尝试将这些excel公式添加到sql查询本身中，作为我遇到问题的2列，请您建议我如何将上述两个公式添加到sql查询中。
在此之前，非常感谢您。
- >2个月公式= IF(LastAccessDate>TODAY()-(365/6)，""，“大于2个月”)
- 持续时间检查公式=IF(LastAccessDate-FirstAccessedDate=0，“从不登录”，LastAccessDate-FirstAccessedDate)
Sql查询：
SELECT s.DomainName as UserId
,s.fullname as FullName
,MIN(DATEADD(HH,DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn)) [FirstAccessAt]
,MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())),A.CreatedOn)) [LastAccessAt]
--Tried on my own
--,DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))AS [Month]
--,(MAX(DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))> -6, GETDATE()) [OlderThan6Months]
FROM archive a
INNER JOIN Systemuser s
ON s.systemuserid = a.objectid
WHERE a.action = 54
and a.CreatedOn between '2015-05-22 00:00:00.000' and '2016-11-23 00:00:00.000'
GROUP BY s.FullName,s.DomainName --DATEPART(MM,DATEADD(HH,(DATEDIFF(HH,GetUTCDate(),GetDate())), A.CreatedOn))
ORDER BY [LastAccessAt] descStack Overflow用户
回答已采纳
发布于 2016-11-22 14:47:44
试试这个：
DECLARE @FirstAccessDate DATETIME;
DECLARE @LastAccessDate DATETIME;
DECLARE @Today DATETIME;
SET @FirstAccessDate = '20160920';
SET @LastAccessDate = '20160922';
SET @Today = '20161122';
SELECT CASE WHEN DATEADD(MONTH, 2, @LastAccessDate) >= @Today THEN NULL
ELSE 'Older than 2 months'
END AS IsOlderThanTwoMonths,
CASE WHEN @FirstAccessDate = @LastAccessDate THEN 'Never Logged On'
ELSE CAST(DATEDIFF(DAY, @FirstAccessDate, @LastAccessDate) AS VARCHAR(20))
END AS Duration;
SET @LastAccessDate = '20160921';
SET @FirstAccessDate = '20160921';
SELECT CASE WHEN DATEADD(MONTH, 2, @LastAccessDate) >= @Today THEN NULL
ELSE 'Older than 2 months'
END AS IsOlderThanTwoMonths ,
CASE WHEN @FirstAccessDate = @LastAccessDate THEN 'Never Logged On'
ELSE CAST(DATEDIFF(DAY, @FirstAccessDate, @LastAccessDate) AS VARCHAR(20))
END AS Duration; 因此，您的查询应该如下所示：
SELECT
UserId,
FullName,
FirstAccessAt,
LastAccessAt,
CASE WHEN DATEADD(MONTH, 2, LastAccessAt) >= @Today THEN NULL
ELSE 'Older than 2 months'
END AS IsOlderThanTwoMonths,
CASE WHEN FirstAccessAt = LastAccessAt THEN 'Never Logged On'
ELSE CAST(DATEDIFF(DAY, FirstAccessAt, LastAccessAt) AS VARCHAR(20))
END AS Duration
FROM (
SELECT
s.DomainName as UserId,
s.fullname as FullName,
MIN(A.CreatedOn) AS FirstAccessAt,
MAX(A.CreatedOn) AS LastAccessAt
FROM archive a
INNER JOIN Systemuser s
ON s.systemuserid = a.objectid
WHERE
a.action = 54
and a.CreatedOn between '2015-05-22 00:00:00.000' and '2016-11-23 00:00:00.000'
GROUP BY
s.FullName, s.DomainName
) t
ORDER BY LastAccessAt DESC页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/40742904
复制相关文章
相似问题
腾讯云开发者
